SQL query to get the oldest date

advertisements

I'm new to SQL and have a large database that contains IDs and Service Dates and I need to write a query to give me the first date each ID had a service.

I tried:

SELECT dbo.table.ID, dbo.otherTable.ServiceDate AS EasliestDate
FROM dbo.table INNER JOIN dbo.table.ID = dbo.otherTable.ID

But the output is every service for every ID, which has too many results to sort through. I want the output to only show the ID and the oldest service date. Any advice is appreciated.

EDIT: To be more precise, the output I am looking for is the ID and service date if the oldest service date is during the year that I specify. I.E. if ID = 1 has a service in 2015 and 2016 and I am searching for IDs in 2016 then ID = 1 should not appear in the results because there was an earlier service in 2015.

EDIT: Thanks everyone who helped with this! The answer I accepted did exactly what I asked. Major kudos to Patty though who who elaborated on how to further filter the outcome by year.


Use GROUP BY and MIN to get the first date for each ID:

SELECT  dbo.table.ID,
        MIN(dbo.otherTable.ServiceDate) AS EasliestDate
FROM    dbo.table
        INNER JOIN otherTable
            ON dbo.table.ID = dbo.otherTable.ID
GROUP BY dbo.table.ID;


ADDENDUM

In reference to a question in the comments:

how would I also restrict it to show only those who had a service in a specific year?

It would depend on your exact requirements, consider the following set:

ID      ServiceDate
--------------------
1       2014-05-01
1       2015-08-01
1       2016-07-07
2       2015-08-19

You would only want to include ID = 1 if the year you specified was 2016, but assuming you still wanted to return the first date of 2014-05-01 then you would need to add a having clause with a case statement to get this.

DECLARE @Year INT = 2016;

DECLARE @YearStart DATE = DATEADD(YEAR, @Year - 1900, '19000101'),
        @YearEnd DATE = DATEADD(YEAR, @Year - 1900 + 1, '19000101');

SELECT @YearStart, @YearEnd

SELECT  t.ID,
        MIN(o.ServiceDate) AS EasliestDate
FROM    dbo.table AS t
        INNER JOIN otherTable AS o
            ON o.ID = r.ID
GROUP BY t.ID
HAVING COUNT(CASE WHEN o.ServiceDate >= @YearStart
                    AND o.ServiceDate < @YearEnd THEN 1 END) > 0;

If you only want the earliest date in 2016 the a where clause would suffice

DECLARE @Year INT = 2016;

DECLARE @YearStart DATE = DATEADD(YEAR, @Year - 1900, '19000101'),
        @YearEnd DATE = DATEADD(YEAR, @Year - 1900 + 1, '19000101');

SELECT @YearStart, @YearEnd

SELECT  t.ID,
        MIN(o.ServiceDate) AS EasliestDate
FROM    dbo.table AS t
        INNER JOIN otherTable AS o
            ON o.ID = r.ID
WHERE   o.ServiceDate >= @YearStart
AND     o.ServiceDate < @YearEnd
GROUP BY t.ID;

It is worth noting there is a very good reason I have chosen to calculate the start of the year, and the start of the next year and used

WHERE   o.ServiceDate >= @YearStart
AND     o.ServiceDate < @YearEnd

Instead of just

WHERE   DATEPART(YEAR, o.ServiceDate) = 2016;

In the former, an index on ServiceDate can be used whereas in the latter, the DATEPART calculation must be done on every record and this can cause significant performace issues.

ADDENDUM 2

To do the following:

The exact thing I want then would be IDs who's earliest service is in the year I specify.

Then you would need a having clause, just a different one to the one I posted before:

DECLARE @Year INT = 2016;

DECLARE @YearStart DATE = DATEADD(YEAR, @Year - 1900, '19000101'),
        @YearEnd DATE = DATEADD(YEAR, @Year - 1900 + 1, '19000101');

SELECT @YearStart, @YearEnd

SELECT  t.ID,
        MIN(o.ServiceDate) AS EasliestDate
FROM    dbo.table AS t
        INNER JOIN otherTable AS o
            ON o.ID = r.ID
GROUP BY t.ID
HAVING  MIN(o.ServiceDate) >= @YearStart
AND     MIN(o.ServiceDate) < @YearEnd;

ADDENDUM 3

CREATE VIEW dbo.YourView
AS
    SELECT  dbo.table.ID,
            MIN(dbo.otherTable.ServiceDate) AS EasliestDate
    FROM    dbo.table
            INNER JOIN otherTable
                ON dbo.table.ID = dbo.otherTable.ID
    GROUP BY dbo.table.ID;

Then you can apply your criteria to the view:

SELECT  *
FROM    dbo.YourView
WHERE   EasliestDate >= '2015-01-01'
AND     EasliestDate < '2016-01-01';