Solved

SQL Server Partioning or Ranking ?

Posted on 2015-01-09
18
84 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 4
  • +2
18 Comments
 
LVL 24

Expert Comment

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

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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 34

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 34

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
 
LVL 34

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 34

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 49

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

632 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