Solved

SQL Server Partioning or Ranking ?

Posted on 2015-01-09
18
78 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 33

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 33

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 33

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 33

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql user function 7 32
Help with SQL - TOP 10 by date and by group 13 34
SQL Statement to Update Email Domain 2 21
Trying to identify overlapping date ranges 5 15
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

920 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

12 Experts available now in Live!

Get 1:1 Help Now