Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 90
  • Last Modified:

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
0
gvamsimba
Asked:
gvamsimba
  • 6
  • 6
  • 4
  • +2
3 Solutions
 
Phillip BurtonCommented:
Which version of SQL Server?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please take into account this article:
http://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
0
 
Phillip BurtonCommented:
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?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
ste5anSenior DeveloperCommented:
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;

Open in new window

0
 
gvamsimbaAuthor Commented:
Hi Philip, my version is sql server 2008 r2
0
 
Phillip BurtonCommented:
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?
0
 
ste5anSenior DeveloperCommented:
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 99

Open in new window

?
0
 
gvamsimbaAuthor Commented:
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
0
 
gvamsimbaAuthor Commented:
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.
0
 
ste5anSenior DeveloperCommented:
Kidding me? Just use the second one..
0
 
Phillip BurtonCommented:
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 appid

Open in new window

0
 
Phillip BurtonCommented:
It's SOO much easier in SQL Server 2012 :-)
0
 
gvamsimbaAuthor Commented:
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
0
 
gvamsimbaAuthor Commented:
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.
0
 
Phillip BurtonCommented:
MY code gives:

111      2014-12-27      2014-11-27      30
0
 
ste5anSenior DeveloperCommented:
Just filter for the last row:

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
             ),
        OrderedApp
          AS ( SELECT   O.appid ,
                        O.startdate ,
                        O.statusid ,
                        ROW_NUMBER() OVER ( PARTITION BY O.appid ORDER BY O.startdate DESC ) 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
    WHERE   L.RN = 1
    ORDER BY L.appid ,
            L.startdate;

Open in new window

0
 
PortletPaulCommented:
Using the same sample data as Phillip Burton, this result:
| APPID |        PREV_START |         STARTDATE | DAYDIFFERENCE | PREV_STATUS | STATUSID |
|-------|-------------------|-------------------|---------------|-------------|----------|
|   111 | November, 27 2014 | December, 27 2014 |            30 |           8 |        9 |

Open in new window


is produced by this query:
WITH CTE
AS (
      SELECT
            S.appid
          , S.startdate
          , S.statusid
          , ROW_NUMBER() OVER (PARTITION BY S.appid ORDER BY S.startdate DESC) AS RN
      FROM test S
)
SELECT
      appid
    , prev_start
    , startdate
    , DATEDIFF(DAY, prev_start, startdate) daydifference
    , prev_status
    , statusid
FROM CTE A
      CROSS APPLY (
            SELECT TOP (1)
                  startdate PREV_START
                , statusid PREV_STATUS
            FROM CTE B
            WHERE A.appid = B.appid
            AND A.statusid <> B.statusid
            ORDER BY B.startdate DESC
      ) CA
WHERE A.RN = 1
;

Open in new window


This solution may be seen working at: http://sqlfiddle.com/#!3/1caffa/

(+ you may also compare execution plans )

sqlfiddle details:
[SQL Fiddle][1]

**MS SQL Server 2008 Schema Setup**:

    
    
    CREATE TABLE test 
    	([appid] int, [statusid] int, [startdate] datetime)
    ;
    	
    INSERT INTO test 
    	([appid], [statusid], [startdate])
    VALUES
    	(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') 
    ;

**Query 1**:

    WITH CTE
    AS (
          SELECT
                S.appid
              , S.startdate
              , S.statusid
              , ROW_NUMBER() OVER (PARTITION BY S.appid ORDER BY S.startdate DESC) AS RN
          FROM test S
    )
    SELECT
          appid
        , prev_start
        , startdate
        , DATEDIFF(DAY, prev_start, startdate) daydifference
        , prev_status
        , statusid
    FROM CTE A
          CROSS APPLY (
                SELECT TOP (1)
                      startdate PREV_START
                    , statusid PREV_STATUS
                FROM CTE B
                WHERE A.appid = B.appid
                AND A.statusid <> B.statusid
                ORDER BY B.startdate DESC
          ) CA
    WHERE A.RN = 1
    

**[Results][2]**:
    
    | APPID |                      PREV_START |                       STARTDATE | DAYDIFFERENCE | PREV_STATUS | STATUSID |
    |-------|---------------------------------|---------------------------------|---------------|-------------|----------|
    |   111 | November, 27 2014 00:00:00+0000 | December, 27 2014 00:00:00+0000 |            30 |           8 |        9 |


**Query 2**:

    WITH MYTABLE
    AS (
          SELECT
                appid
              , statusid
              , MIN(startdate) AS MINDATE
              , MAX(startdate) AS MAXDATE
              , CASE statusID
                      WHEN (
                                  SELECT
                                        MAX(statusid)
                                  FROM test AS U
                                  WHERE T.appid = U.appid
                            ) THEN 1
                      ELSE 0
                END AS MAXSTATUSID
          FROM test 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
    appid
    

**[Results][3]**:
    
    | APPID |         FIRSTDATEOFLATESTSTATUS |         MAXDATEOFPREVIOUSSTATUS | DAYDIFFERENCE |
    |-------|---------------------------------|---------------------------------|---------------|
    |   111 | December, 27 2014 00:00:00+0000 | November, 27 2014 00:00:00+0000 |            30 |



  [1]: http://sqlfiddle.com/#!3/1caffa/1

  [2]: http://sqlfiddle.com/#!3/1caffa/1/0

  [3]: http://sqlfiddle.com/#!3/1caffa/1/1

Open in new window

0
 
gvamsimbaAuthor Commented:
ste5an solution was easy to understand and gave me the result.
Many thanks to others also.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 6
  • 6
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now