-1

I have a table in SQL where one column contains lots of IDS some of which are duplicate, a second column with lots of dates all different

I want to create a table where i have unique list of IDS down the left hand side of the table, a column called number of days and then in the table the number of days between the last date and the second to last date that are in the table.

Ingram
  • 654
  • 2
  • 7
  • 29
  • 5
    Can you provide a table structure and perhaps some sample data? What have you tried so far? – kchason Oct 27 '17 at 17:08
  • 3
    It would be great if you could post your table data along with the desired output. Visual representation goes a long way to help figure out the problem – boop_the_snoot Oct 27 '17 at 17:08
  • 1
    Learn how to ask question. https://stackoverflow.com/help/how-to-ask – Eric Oct 27 '17 at 17:11
  • I have tried following this example so far but i havent been able to work out how to find the difference between the dates, and then on top of that i havent been able to work out how to do this for each ID. I will post a table in a mo https://stackoverflow.com/questions/634568/how-to-get-difference-between-two-rows-for-a-column-field – Ingram Oct 27 '17 at 17:12

2 Answers2

2

Using Row_Number() in concert with a conditional aggregation.

Example

Declare @YourTable Table ([ID] varchar(50),[DateCol] date)
Insert Into @YourTable Values 
 (1,'2017-05-01')
,(1,'2017-05-05')
,(1,'2017-05-10')  -- 2nd 
,(1,'2017-05-31')  -- Lst  21 days
,(2,'2017-05-01')
,(2,'2017-06-05')
,(2,'2017-06-10')  -- 2nd
,(2,'2017-07-31')  -- Lst 51 days

Select ID
      ,Days = DateDiff(DAY,max(case when RN=2 then DateCol end),max(case when RN=1 then DateCol end))
 From (
        Select *
              ,RN = Row_Number() over (Partition By ID Order by DateCol Desc) 
         from @YourTable
      ) A 
 Group By ID

Returns

ID  Days
1   21
2   51
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • I'm trying to interpret OP's request as well. _"...the number of days between the last date and the second to last date that are in the table."_ For your example ID1 should be 21 days? – Zorkolot Oct 27 '17 at 17:42
  • I have tried applying it to the table as John's example of the table is correct but i am sttruggling here is my query SELECT TOP 100000 [YIT_ID] ,Days = DateDiff(DAY,min(Reading_Date),max(case when RN=2 then Reading_Date end)) from [YIT].[dbo].[YIT_Readings] Select * ,RN = Row_Number() over (Partition By ID Order by DateCol Desc) from [YIT].[dbo].[YIT_Readings] ) A --Group By ID – Ingram Oct 27 '17 at 17:46
  • @MrAssistance See updated answer, Zorkolot pointed out that I missed last and 2nd last. – John Cappelletti Oct 27 '17 at 17:47
1

Here I take the MAX(DateCol) that is less than the subqueried MAX(DateCol) with the same ID- this is used in the DateDiff. 

SELECT dT.ID
      ,(SELECT MAX(DateCol)              
          FROM @YourTable Y
         WHERE Y.DateCol < dT.MaxDateCol --Date less than MAX
           AND Y.ID = dT.ID
       ) AS [Date less than MAX]   --added by request
      ,dT.MaxDateCol AS [Max Date] --added by request
      ,DATEDIFF(DAY, (SELECT MAX(DateCol)              
                        FROM @YourTable Y
                       WHERE Y.DateCol < dT.MaxDateCol --Date less than MAX
                         AND Y.ID = dT.ID
                     ) 
                   , dT.MaxDateCol
               ) AS [Days Difference] --datediff between max and date less than MAX
  FROM (    
        SELECT ID
              ,MAX([DateCol]) [MaxDateCol] --MAX, removes duplicate ID's.
          FROM @YourTable
        GROUP BY ID
       ) AS dT
Zorkolot
  • 1,899
  • 1
  • 11
  • 8
  • @ Zorkolot thank you for the help on this, understanding derived tables is a little above me. What if i wanted to display the 2 dates which the differences are being calculated from how would i display each of these in two separate columns? I have tried playing around with the code above but not to much luck :( – Ingram Oct 28 '17 at 17:18
  • See the above changes. I've annotated the lines with `--added by request`. Notice the datediff takes these 2 dates as written. – Zorkolot Oct 28 '17 at 17:32
  • No problem. I'm glad I could help you! – Zorkolot Oct 28 '17 at 17:39
  • @ Zorkolot i do have a question WHERE DateReading < dT.MaxDateCol --Date less than MAX, how does SQL no its not every date before the max date and just the one previous? – Ingram Oct 28 '17 at 17:49
  • The bottom subquery `(...) AS dT` executes first. Returned from this is the [MaxDateCol] and ID. I reference this using the "dT" alias in the receiving query: `dT.ID, dT.MaxDateCol` (see the reference? dT... the value comes from the bottom subquery). Notice I do yet another subquery- `SELECT MAX(DateCol) FROM YourTable Y WHERE Y.DateCol < dt.MaxDateCol`. The less than sign (<) is the important part. I query the original table for the MAX date less than dT.MaxDateCol- that would be 2nd highest. – Zorkolot Oct 28 '17 at 17:59
  • @ Zorkolot oh yes i see the bottom sub query where the max is obtained first. If i had a 1000 different readings less than the max date the < would only bring the date less than this, out of interest what would i then do if i wanted 2 dates previous to the maximum? No need to make this as amendment to the completed solution above its just out of curiosity – Ingram Oct 28 '17 at 18:14
  • I would probably approach it with a different algorithm. It would use a partition-by to add a rownumber. The rownumber would be in the order of Xth-highest. I don't have access to SQL Server so I can't test this atm and am writing off the top of my head... but it would be similar to: 'SELECT ID ,DateCol ,ROW_NUMBER() OVER(PARTITION BY id ORDER BY DateCol DESC) AS RowNum FROM @YourTable ORDER BY ID ASC , DateCol DESC' - you could then subquery this to see where rownumber = 3, for example. – Zorkolot Oct 28 '17 at 18:31
  • @ Zorkolot it was purely out of curiosity so i dont require you to do anything. This has really benefited me so thank you for all your help. – Ingram Oct 28 '17 at 18:36