SQL Server Partioning or Ranking ?
Hi, i have a table called test and below is my sample data for an application id.
so basically I want get the count in Days since the last status has changed based on the start date per each application. So in this example, it has to be the difference in days between the first row and the fourth row because eventough the second row is showing as the last changed date after 27th october 2014, it still got the same status as 7 so the status has not really changes so the query should look at the next latest date when the status was changed which is 4th record .
Can anyone please give me that tsql code please ?
appid statusid startdate
111 7 2014-10-27 00:00:00
111 7 2014-09-19 00:00:00
111 3 2014-03-18 00:00:00
111 8 2014-06-27 00:00:00
Many Thanks
so basically I want get the count in Days since the last status has changed based on the start date per each application. So in this example, it has to be the difference in days between the first row and the fourth row because eventough the second row is showing as the last changed date after 27th october 2014, it still got the same status as 7 so the status has not really changes so the query should look at the next latest date when the status was changed which is 4th record .
Can anyone please give me that tsql code please ?
appid statusid startdate
111 7 2014-10-27 00:00:00
111 7 2014-09-19 00:00:00
111 3 2014-03-18 00:00:00
111 8 2014-06-27 00:00:00
Many Thanks
Which version of SQL Server?
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
please take into account this article:
https://www.experts-exchange.com/Database/MS_Access/A_1555-Analytical-SQL-Where-do-you-rank.html
now, the issue with sql server is that it does not have a "LEAD" or "LAG" function like Oracle does.
hence, you will need to self-join the table on a "row number" basis, joining on +- 1 on that value.
hope this helps as rough input
https://www.experts-exchange.com/Database/MS_Access/A_1555-Analytical-SQL-Where-do-you-rank.html
now, the issue with sql server is that it does not have a "LEAD" or "LAG" function like Oracle does.
hence, you will need to self-join the table on a "row number" basis, joining on +- 1 on that value.
hope this helps as rough input
SQL Server 2012 onwards does have a LEAD/LAG function. See http://msdn.microsoft.com/en-us/library/hh231256.aspx for details.
So, which version of SQL Server?
So, which version of SQL Server?
When we order by date, e.g.
DECLARE @Sample TABLE
(
appid INT ,
statusid INT ,
startdate DATE
);
INSERT INTO @Sample
VALUES ( 111, 7, '20141027' ),
( 111, 7, '20140919' ),
( 111, 3, '20140318' ),
( 111, 8, '20140627' ),
( 111, 9, GETDATE() ),
( 123, 1, '20150101' ),
( 123, 1, '20150102' ),
( 123, 2, '20150103' );
WITH OrderedStatus
AS ( SELECT S.appid ,
S.startdate ,
S.statusid ,
ROW_NUMBER() OVER ( PARTITION BY S.appid, S.statusid ORDER BY S.startdate ) AS RN
FROM @Sample S
)
SELECT O.appid ,
O.startdate ,
O.statusid ,
LAG(O.startdate, 1, NULL) OVER ( PARTITION BY O.appid ORDER BY O.startdate ) AS DateLast ,
DATEDIFF(DAY, LAG(O.startdate, 1, NULL) OVER ( PARTITION BY O.appid ORDER BY O.startdate ), O.startdate) AS DaysSinceDateLast
FROM OrderedStatus O
WHERE O.RN = 1
ORDER BY O.appid ,
O.startdate;
WITH OrderedStatus
AS ( SELECT S.appid ,
S.startdate ,
S.statusid ,
ROW_NUMBER() OVER ( PARTITION BY S.appid, S.statusid ORDER BY S.startdate ) AS RN
FROM @Sample S
),
OrderedApp
AS ( SELECT O.appid ,
O.startdate ,
O.statusid ,
ROW_NUMBER() OVER ( PARTITION BY O.appid ORDER BY O.startdate ) AS RN
FROM OrderedStatus O
WHERE O.RN = 1
)
SELECT L.appid ,
L.startdate ,
L.statusid ,
R.startdate AS DateLast ,
DATEDIFF(DAY, R.startdate, L.startdate) AS DaysSinceDateLast
FROM OrderedApp L
LEFT JOIN OrderedApp R ON R.appid = L.appid
AND R.RN = L.RN - 1
ORDER BY L.appid ,
L.startdate;ASKER
Hi Philip, my version is sql server 2008 r2
What if the data was this?
appid statusid startdate
111 7 2014-10-27 00:00:00
111 7 2014-09-19 00:00:00
111 3 2014-03-18 00:00:00
111 8 2014-11-27 00:00:00
111 8 2014-06-27 00:00:00
111 9 2014-12-27 00:00:00
What information would you want?
appid statusid startdate
111 7 2014-10-27 00:00:00
111 7 2014-09-19 00:00:00
111 3 2014-03-18 00:00:00
111 8 2014-11-27 00:00:00
111 8 2014-06-27 00:00:00
111 9 2014-12-27 00:00:00
What information would you want?
Maybe
appid startdate statusid DateLast DaysSinceDateLast
----------- ---------- ----------- ---------- -----------------
111 2014-03-18 3 NULL NULL
111 2014-06-27 8 2014-03-18 101
111 2014-09-19 7 2014-06-27 84
111 2014-12-27 9 2014-09-19 99ASKER
Hi Philip,
in your data, since the below line has the most recent date, then I want the difference of days of 27th December 2014 and the next previous status change which is on 27the Nov 2014
111 9 2014-12-27 00:00:00
111 8 2014-11-27 00:00:00
in your data, since the below line has the most recent date, then I want the difference of days of 27th December 2014 and the next previous status change which is on 27the Nov 2014
111 9 2014-12-27 00:00:00
111 8 2014-11-27 00:00:00
ASKER
Hi ste5phan, I cannot use your query as I am using sql server 2008...
Msg 195, Level 15, State 10, Line 28
'LAG' is not a recognized built-in function name.
Msg 195, Level 15, State 10, Line 28
'LAG' is not a recognized built-in function name.
Kidding me? Just use the second one..
How about this:
DECLARE @Sample TABLE
(
appid INT ,
statusid INT ,
startdate DATE
);
INSERT INTO @Sample
VALUES ( 111, 7, '20141027' ),
( 111, 7, '20140919' ),
( 111, 3, '20140318' ),
( 111, 8, '20140627' ),
( 111, 8, '20141127' ),
( 111, 9, '20141227' );
with myTable as
(Select appid, statusid, min(startdate) as MinDate, max(startdate) as MaxDate,
case statusID
when (select max(statusid) from @Sample as U where T.appid = U.appid) then 1 else 0 end as MaxStatusID
from @Sample as T
group by appid, statusid)
select appid, (select min(MinDate) from myTable as V where MaxStatusID = 1 and U.appid = V.appid) as FirstDateOfLatestStatus,
(select max(MaxDate) from myTable as V where MaxStatusID = 0 and U.appid = V.appid) as MaxDateOfPreviousStatus,
DATEDIFF(day,(select max(MaxDate) from myTable as V where MaxStatusID = 0 and U.appid = V.appid),(select min(MinDate) from myTable as V where MaxStatusID = 1 and U.appid = V.appid)) as DayDifference
from myTable as U
group by appidSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sorry ste5phan, just saw your second bit of the code.
I only want to see one record in the output which is the number of days difference between the most recent status and one before that. we dont need to bother about the previous history. can you please update your query to show this ?
Many Thanks
I only want to see one record in the output which is the number of days difference between the most recent status and one before that. we dont need to bother about the previous history. can you please update your query to show this ?
Many Thanks
ASKER
I only want to see one record in the output Per each application. which is the number of days difference between the most recent status and one before that. we dont need to bother about the previous history.
sorry, I should have mentioned this originally.
sorry, I should have mentioned this originally.
MY code gives:
111 2014-12-27 2014-11-27 30
111 2014-12-27 2014-11-27 30
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ste5an solution was easy to understand and gave me the result.
Many thanks to others also.
Many thanks to others also.