CSjunkie CSjunkie - 6 months ago 13
SQL Question

SQL Query to get oldest date

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.

Answer

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;
Comments