Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server Partioning or Ranking ?

Posted on 2015-01-09
18
Medium Priority
?
88 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 35

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 35

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 35

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 200 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 35

Accepted Solution

by:
ste5an earned 1600 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 200 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

722 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