Search code examples
sqlsql-server-2008datediffrow-number

SQL Sum Datediff different rows conditionally

I am not a DBA, I know enough to get myself in trouble is all so bear with me here. I am trying to find the date difference from a set of data but only sum conditional differences between records based on a status change. The issue I am having is not counting days based on the rules I need to have in place.

Using SQL 2008 R2, with these rules:

  • Records where oldValue to newValue is (NULL to 'anything') will always = MIN;
  • Add day count only when oldValue not equal to Close or Deferred;
  • DO NOT add day counts between changes where previous known newValue equals current record oldValue and is equal to Closed or Deferred

Using:

declare @t table
(
    tranID int,
    orderNum varchar(20),
    oldValue varchar(2000),
    newValue varchar(2000),
tranTime datetime
)

insert into @t values(140,3, NULL, 'Closed', '2013-01-05 12:00:00.000')
insert into @t values(160,4, NULL, 'Defered', '2013-01-07 18:00:00.000')
insert into @t values(101,5, NULL, 'New', '2013-01-01 10:01:00.000')
insert into @t values(111,5, 'New', 'Closed', '2013-01-02 10:00:00.000')
insert into @t values(102,6, NULL, 'New', '2013-01-01 10:02:00.000')
insert into @t values(112,6, 'Open', 'Deferred', '2013-01-02 10:10:00.000')
insert into @t values(132,6, 'Deferred', 'Closed', '2013-01-04 11:00:00.000')
insert into @t values(103,7, NULL, 'New', '2013-01-01 10:03:00.000')
insert into @t values(123,7, 'Ready', 'Closed', '2013-01-03 11:30:00.000')
insert into @t values(133,7, 'Closed', 'Open', '2013-01-04 11:11:00.000')
insert into @t values(143,7, 'Passed', 'Closed', '2013-01-05 12:15:00.000')
insert into @t values(104,8, NULL, 'New', '2013-01-01 10:04:00.000')
insert into @t values(114,8, 'Open', 'Closed', '2013-01-02 10:20:00.000')
insert into @t values(134,8, 'Closed', 'Open', '2013-01-04 11:22:00.000')
insert into @t values(144,8, 'Failed', 'Deferred', '2013-01-05 12:30:00.000')
insert into @t values(154,8, 'Deferred', 'Closed', '2013-01-06 17:00:00.000')

I am expecting to see an output something like:

orderNum | resolveDays
----------------------
    3    |      0
    4    |      0
    5    |      1
    6    |      1
    7    |      3
    8    |      2

We have a set of orders that have transactions that increase in ID based on date changed. The tranID sorted will in turn sort the dates. To see the data grouped easily on the rules we need to order by orderNum then tranID and you can see the orderNum lined up nicely with its matching changes in order of occurrence. I have a query that will give me the date diff for the min and max dates but this wont adhere to the rules where I am not adding days while and order is closed or deferred. 

Select orderNum
      ,MIN(tranTime)as Opened
      ,MAX(tranTime) as LastClose
      ,DATEDIFF(DAY,MIN(tranTime),MAX(tranTime)) as resolveDays
      ,Count(tranTime) as QtyChanged
from @t 
group by orderNum
order by orderNum

I have tried to sum over the orderNum with a case switch but could not get the counts to show correctly which led me to trying to use RANK or ROW_NUMBER OVER the orderNum to do a self join on the previous record and check the case condition of the oldValue and newValue but I am missing something to connect the pieces. I have been looking at islands and gap solutions as well but I am not able to connect those dots to get my expected outcome. What else can I try here?

Solution

  • Try following query

    DECLARE @t TABLE
(
    tranID INT,
    orderNum VARCHAR(20),
    oldValue VARCHAR(2000),
    newValue VARCHAR(2000),
    tranTime DATETIME
)

INSERT INTO @t VALUES(140,3, NULL, 'Closed', '2013-01-05 12:00:00.000')
INSERT INTO @t VALUES(160,4, NULL, 'Defered', '2013-01-07 18:00:00.000')
INSERT INTO @t VALUES(101,5, NULL, 'New', '2013-01-01 10:01:00.000')
INSERT INTO @t VALUES(111,5, 'New', 'Closed', '2013-01-02 10:00:00.000')
INSERT INTO @t VALUES(102,6, NULL, 'New', '2013-01-01 10:02:00.000')
INSERT INTO @t VALUES(112,6, 'Open', 'Deferred', '2013-01-02 10:10:00.000')
INSERT INTO @t VALUES(132,6, 'Deferred', 'Closed', '2013-01-04 11:00:00.000')
INSERT INTO @t VALUES(103,7, NULL, 'New', '2013-01-01 10:03:00.000')
INSERT INTO @t VALUES(123,7, 'Ready', 'Closed', '2013-01-03 11:30:00.000')
INSERT INTO @t VALUES(133,7, 'Closed', 'Open', '2013-01-04 11:11:00.000')
INSERT INTO @t VALUES(143,7, 'Passed', 'Closed', '2013-01-05 12:15:00.000')
INSERT INTO @t VALUES(104,8, NULL, 'New', '2013-01-01 10:04:00.000')
INSERT INTO @t VALUES(114,8, 'Open', 'Closed', '2013-01-02 10:20:00.000')
INSERT INTO @t VALUES(134,8, 'Closed', 'Open', '2013-01-04 11:22:00.000')
INSERT INTO @t VALUES(144,8, 'Failed', 'Deferred', '2013-01-05 12:30:00.000')
INSERT INTO @t VALUES(154,8, 'Deferred', 'Closed', '2013-01-06 17:00:00.000')
------
DECLARE @TmpTable TABLE 
(
    Id INT,
    tranID INT,
    orderNum VARCHAR(20),
    oldValue VARCHAR(2000),
    newValue VARCHAR(2000),
    tranTime DATETIME
)
------
INSERT INTO @TmpTable
SELECT
    ROW_NUMBER() OVER(PARTITION BY orderNum ORDER BY tranID) Id,
    tranID,
    orderNum,
    oldValue,
    newValue,
    tranTime
FROM
    @t    
------
SELECT 
    CurrentRow.orderNum,
    SUM(
        CASE
            WHEN CurrentRow.newValue = NextRow.oldValue AND 
                 NextRow.oldValue IN ('Closed', 'Deferred') THEN 0
            ELSE ISNULL(DATEDIFF(DAY, CurrentRow.tranTime, NextRow.tranTime), 0) END
        )  AS resolveDays
FROM
    @TmpTable CurrentRow LEFT JOIN 
    @TmpTable NextRow ON CurrentRow.orderNum = NextRow.orderNum AND
                         CurrentRow.Id = (NextRow.Id - 1)
GROUP BY CurrentRow.orderNum

    Output:

    orderNum             resolveDays
-------------------- -----------
3                    0
4                    0
5                    1
6                    1
7                    3
8                    2