Solved

SQL Server Partioning or Ranking ?

Posted on 2015-01-09
18
77 Views
Last Modified: 2015-01-12
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
Comment
Question by:gvamsimba
  • 6
  • 6
  • 4
  • +2
18 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40540060
Which version of SQL Server?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40540072
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40540081
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
 
LVL 32

Expert Comment

by:ste5an
ID: 40540099
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
 

Author Comment

by:gvamsimba
ID: 40540140
Hi Philip, my version is sql server 2008 r2
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40540151
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
 
LVL 32

Expert Comment

by:ste5an
ID: 40540182
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
 

Author Comment

by:gvamsimba
ID: 40540234
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
 

Author Comment

by:gvamsimba
ID: 40540238
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 32

Expert Comment

by:ste5an
ID: 40540252
Kidding me? Just use the second one..
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40540254
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
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 50 total points
ID: 40540258
It's SOO much easier in SQL Server 2012 :-)
0
 

Author Comment

by:gvamsimba
ID: 40540319
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
 

Author Comment

by:gvamsimba
ID: 40540329
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40540335
MY code gives:

111      2014-12-27      2014-11-27      30
0
 
LVL 32

Accepted Solution

by:
ste5an earned 400 total points
ID: 40540409
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
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 50 total points
ID: 40541602
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
 

Author Closing Comment

by:gvamsimba
ID: 40544142
ste5an solution was easy to understand and gave me the result.
Many thanks to others also.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now