CODEWITHSUNDEEP
sqlsql-server
user2031234
user2031234

Reputation: 13

How to calculate the date difference between rows with special first row rule

I have multiple applications each with unique application ID's, Each application has logs which are stored in a table. I want to know how to calculate the date difference eg:

    AppID Start           Loggedon   change 
    A1    08/07/2010      08/09/2010 Xchange
    A1    08/07/2010      08/20/2010 Ychange
    A1    08/07/2010      08/30/2010 Zchange
    A2    07/07/2010      07/13/2010 Ychange
    A3    09/07/2010      09/09/2010 Xchange

So I want the values 

Difference
2   (Difference between the application start date and 1st loggedon date)
11  (difference between 08/20 and 08/09, the prior row because AppID stayed the same)
10  (difference between 08/30 and 08/20, the prior row because AppID stayed the same)
6   (Difference between the application start date and 1st loggedon date)
2   (Difference between the application start date and 1st loggedon date)

Hope I am clear. How can I achieve this, I tried Ranking and Row_Number. But I might be wrong somewhere. I am using SQL Server and cannot use LAG()

Upvotes: 1

Views: 191

Answers (2)

Hogan
Hogan

Reputation: 70513

Ok this will work. Enjoy.

Now tested! Thanks sgeddes -- http://sqlfiddle.com/#!3/e4d28/19

WITH tableNumbered AS
(
   SELECT *,
      ROW_NUMBER() OVER (PARTITION BY AppID ORDER BY AppID, Start , Loggedon ) AS row
   FROM Apps
)
SELECT t.*,
  CASE WHEN t2.row IS NULL THEN DATEDIFF(day,t.Start,t.LoggedOn)
       ELSE DATEDIFF(day,t2.LoggedOn,t.Loggedon)
  END as diff
FROM tableNumbered t
LEFT JOIN tableNumbered t2 ON t.AppID = t2.AppID AND t2.row+1 = t.row

I still think you should do it in the UI.

Upvotes: 1

sgeddes
sgeddes

Reputation: 62831

@Hogan has the correct approach, but for some reason, I can't get it to work completely. Here is a slight variation that seems to produce the correct result -- however, please accept @Hogan's answer as he beat me to it :)

WITH cte AS (
  SELECT AppId, Start, LoggedOn, 
  ROW_NUMBER() OVER (ORDER BY AppId) rn
FROM Apps
  ) 
SELECT c.AppId,
  CASE 
    WHEN c2.RN IS NULL
    THEN DATEDIFF(day,c.start,c.Loggedon)
    ELSE
         DATEDIFF(day,c2.Loggedon,c.Loggedon)
    END as TimeWanted
FROM cte c
   LEFT JOIN cte c2 on c.AppId = c2.AppId
      AND c.rn = c2.rn + 1

And here is the Fiddle.

Good luck.

Upvotes: 1

Related Questions