Post reply

Cumulative count

  • December 8, 2015 at 8:20 am

    #399437

    Hi,

    I need a cumulative count of the closed and open dates . Please help .

    CREATE TABLE #TestTable(

    [IndividualDate] [datetime] NULL,

    [ClosedDate] [datetime] NULL,

    [OpenDate] [datetime] NULL,

    CummClosedDate INT,

    CumOpenDate INT

    )

    INSERT INTO #TestTable

    ([IndividualDate],

    [ClosedDate] ,

    [OpenDate])

    SELECT '2015-07-05 00:00:00.000',NULL,NULL UNION ALL

    SELECT '2015-07-06 00:00:00.000',NULL,NULL UNION ALL

    SELECT '2015-07-07 00:00:00.000','2015-07-07 00:00:00.000',NULL UNION ALL

    SELECT '2015-07-08 00:00:00.000',NULL,'2015-07-08 00:00:00.000' UNION ALL

    SELECT '2015-07-09 00:00:00.000','2015-07-09 00:00:00.000','2015-07-09 00:00:00.000'

    SELECT * FROM #TestTable

    --Required results

    SELECT '2015-07-05 00:00:00.000' AS [IndividualDate] ,NULL AS [ClosedDate] ,NULL AS [OpenDate] ,0 AS CummClosedDate , 0 AS CumOpenDate UNION ALL

    SELECT '2015-07-06 00:00:00.000' AS [IndividualDate] ,NULL AS [ClosedDate] ,NULL AS [OpenDate] ,0 AS CummClosedDate , 0 AS CumOpenDate UNION ALL

    SELECT '2015-07-07 00:00:00.000' AS [IndividualDate] ,'2015-07-07 00:00:00.000' AS [ClosedDate] ,NULL AS [OpenDate] ,1 AS CummClosedDate , 0 AS CumOpenDate UNION ALL

    SELECT '2015-07-08 00:00:00.000' AS [IndividualDate] ,NULL AS [ClosedDate] ,'2015-07-08 00:00:00.000' AS [OpenDate] ,0 AS CummClosedDate , 1 AS CumOpenDate UNION ALL

    SELECT '2015-07-09 00:00:00.000' AS [IndividualDate] ,'2015-07-09 00:00:00.000' AS [ClosedDate] ,'2015-07-09 00:00:00.000' AS [OpenDate] ,2 AS CummClosedDate , 2 AS CumOpenDate

    DROP TABLE #TestTable

    Thanks,

    PSB

  • December 8, 2015 at 8:34 am

    #1844670

    SELECT

    IndividualDate

    ,ClosedDate

    ,OpenDate

    ,CASE

    WHEN ClosedDate IS NULL THEN 0

    ELSE COUNT(ClosedDate) OVER (ORDER BY IndividualDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

    END AS CumClosedDate

    ,CASE

    WHEN OpenDate IS NULL THEN 0

    ELSE COUNT(OpenDate) OVER (ORDER BY IndividualDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

    END AS CumOpenDate

    FROM #TestTable

    John

  • December 8, 2015 at 8:49 am

    #1844678

    Why do you have a zero on CummClosedDate for 2015-07-08? If you really need a cumulative count, the case is not needed.

    SELECT IndividualDate,

    ClosedDate,

    OpenDate,

    COUNT(ClosedDate) OVER(ORDER BY IndividualDate ROWS UNBOUNDED PRECEDING) AS CummClosedDate,

    COUNT(OpenDate) OVER(ORDER BY IndividualDate ROWS UNBOUNDED PRECEDING) AS CummOpenDate

    FROM #TestTable

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • December 8, 2015 at 9:01 am

    #1844685

    That doesn't return the result set that the OP requires. He appears to want the cumulative count to show 0 unless there is actually a value in the corresponding date column.

    John

  • December 8, 2015 at 9:05 am

    #1844689

    John Mitchell-245523 (12/8/2015)

    That doesn't return the result set that the OP requires. He appears to want the cumulative count to show 0 unless there is actually a value in the corresponding date column.

    John

    That's why I said if. I wasn't sure if the zero was a real necessity or a copy/paste error.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • December 8, 2015 at 11:25 am

    #1844742

    Thanks All for your response . I basically want the cumulatives to be grouped by Open or Closed Date . Example shown below .

    CREATE TABLE #TestTable(

    [IndividualDate] [datetime] NULL,

    ClosedCount INT,

    [ClosedDate] [datetime] NULL,

    [OpenDate] [datetime] NULL,

    OpenCount INT,

    VALUE1 VARCHAR(10)

    )

    INSERT INTO #TestTable

    ([IndividualDate] ,

    OpenCount ,

    [OpenDate] ,

    [ClosedDate] ,

    ClosedCount ,

    VALUE1)

    SELECT '2015-07-14 00:00:00.000',0,NULL,NULL,0,NULL UNION ALL

    SELECT '2015-07-15 00:00:00.000',2,'2015-07-15 00:00:00.000',NULL,0,'HUM' UNION ALL

    SELECT '2015-07-16 00:00:00.000',1,'2015-07-16 00:00:00.000',NULL,0,'HUM - Lega' UNION ALL

    SELECT '2015-07-17 00:00:00.000',1,'2015-07-17 00:00:00.000',NULL,0,'CUS' UNION ALL

    SELECT '2015-07-17 00:00:00.000',3,'2015-07-17 00:00:00.000',NULL,0,'DEV' UNION ALL

    SELECT '2015-07-17 00:00:00.000',4,'2015-07-17 00:00:00.000',NULL,0,'HUM' UNION ALL

    SELECT '2015-07-18 00:00:00.000',0,NULL,NULL,0,NULL UNION ALL

    SELECT '2015-07-19 00:00:00.000',5,'2015-07-19 00:00:00.000',NULL,0,'HUM' UNION ALL

    SELECT '2015-07-20 00:00:00.000',4,'2015-07-20 00:00:00.000',NULL,0,'HUM' UNION ALL

    SELECT '2015-07-21 00:00:00.000',1,'2015-07-21 00:00:00.000',NULL,0,'PSD'UNION ALL

    SELECT '2015-07-21 00:00:00.000',12,'2015-07-21 00:00:00.000',NULL,0,'HUM'UNION ALL

    SELECT '2015-07-22 00:00:00.000',4,'2015-07-22 00:00:00.000','2015-07-22 00:00:00.000',2,'HUM'UNION ALL

    SELECT '2015-07-23 00:00:00.000',2,'2015-07-23 00:00:00.000','2015-07-23 00:00:00.000',6,'SUP - ITT' UNION ALL

    SELECT '2015-07-23 00:00:00.000',5,'2015-07-23 00:00:00.000','2015-07-23 00:00:00.000',6,'HUM'UNION ALL

    SELECT '2015-07-23 00:00:00.000',8,'2015-07-23 00:00:00.000','2015-07-23 00:00:00.000',6,'SUP'UNION ALL

    SELECT '2015-07-24 00:00:00.000',3,'2015-07-24 00:00:00.000','2015-07-24 00:00:00.000',18,'HUM' UNION ALL

    SELECT '2015-07-25 00:00:00.000',0,NULL,'2015-07-25 00:00:00.000',3,NULL

    SELECT * FROM #TestTable

    SELECT '2015-07-14 00:00:00.000',0 AS OpenCount,0 AS DesiredOpen,NULL,NULL,0 AS CloseCount,0 AS DesiredClose,NULL UNION ALL

    SELECT '2015-07-15 00:00:00.000',2 AS OpenCount,2 AS DesiredOpen,'2015-07-15 00:00:00.000',NULL, 0 AS CloseCount,0AS DesiredClose,'HUM' UNION ALL

    SELECT '2015-07-16 00:00:00.000',1 AS OpenCount,3 AS DesiredOpen,'2015-07-16 00:00:00.000',NULL, 0 AS CloseCount,0AS DesiredClose,'HUM - Lega' UNION ALL

    SELECT '2015-07-17 00:00:00.000',1 AS OpenCount,11 AS DesiredOpen,'2015-07-17 00:00:00.000',NULL, 0 AS CloseCount,0AS DesiredClose,'CUS' UNION ALL

    SELECT '2015-07-17 00:00:00.000',4 AS OpenCount,11 AS DesiredOpen ,'2015-07-17 00:00:00.000',NULL, 0 AS CloseCount,0AS DesiredClose,'HUM' UNION ALL

    SELECT '2015-07-17 00:00:00.000',3 AS OpenCount,11 AS DesiredOpen ,'2015-07-17 00:00:00.000',NULL, 0 AS CloseCount,0AS DesiredClose,'DEV' UNION ALL

    SELECT '2015-07-18 00:00:00.000',0 AS OpenCount,11 AS DesiredOpen,NULL,NULL,0 AS CloseCount,0 AS DesiredClose,NULL UNION ALL

    SELECT '2015-07-19 00:00:00.000',5 AS OpenCount,16 AS DesiredOpen,'2015-07-19 00:00:00.000',NULL,0 AS CloseCount,0AS DesiredClose,'HUM' UNION ALL

    SELECT '2015-07-20 00:00:00.000',4 AS OpenCount,20 AS DesiredOpen,'2015-07-20 00:00:00.000',NULL,0 AS CloseCount,0AS DesiredClose,'HUM' UNION ALL

    SELECT '2015-07-21 00:00:00.000',1 AS OpenCount,33 AS DesiredOpen,'2015-07-21 00:00:00.000',NULL,0 AS CloseCount,0AS DesiredClose,'PSD' UNION ALL

    SELECT '2015-07-21 00:00:00.000',12 AS OpenCount,33 AS DesiredOpen,'2015-07-21 00:00:00.000',NULL,0 AS CloseCount,0AS DesiredClose,'HUM' UNION ALL

    SELECT '2015-07-22 00:00:00.000',4 AS OpenCount,37 AS DesiredOpen,'2015-07-22 00:00:00.000','2015-07-22 00:00:00.000',2 AS CloseCount,2 AS DesiredClose,'HUM'UNION ALL

    SELECT '2015-07-23 00:00:00.000',2 AS OpenCount,52 AS DesiredOpen,'2015-07-23 00:00:00.000','2015-07-23 00:00:00.000',6 AS CloseCount,8 AS DesiredClose,'SUP - ITT' UNION ALL

    SELECT '2015-07-23 00:00:00.000',5 AS OpenCount,52 AS DesiredOpen ,'2015-07-23 00:00:00.000','2015-07-23 00:00:00.000',6 AS CloseCount,8 AS DesiredClose,'HUM'UNION ALL

    SELECT '2015-07-23 00:00:00.000',8 AS OpenCount,52 AS DesiredOpen,'2015-07-23 00:00:00.000','2015-07-23 00:00:00.000',6 AS CloseCount,8 AS DesiredClose,'SUP'UNION ALL

    SELECT '2015-07-24 00:00:00.000',3 AS OpenCount,55AS DesiredOpen,'2015-07-24 00:00:00.000','2015-07-24 00:00:00.000',18 AS CloseCount,26 AS DesiredClose,'HUM' UNION ALL

    SELECT '2015-07-25 00:00:00.000',0 AS OpenCount, 55 AS DesiredOpen,NULL,'2015-07-25 00:00:00.000',3 AS CloseCount,29 AS DesiredClose,NULL

    DROP TABLE #TestTable

  • December 8, 2015 at 11:44 am

    #1844745

    Can you explain the rows for 2015-07-23 on the new sample data?

    Why are the Open counts summed individually and the Closed counts aren't? Why are the previous summed as a group and not by row?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • December 8, 2015 at 1:01 pm

    #1844760

    For Desired Open Count ,7/17/2015, (1+4+3) = 8 and DesiredOpen for 7/16 = 3 so 8 + 3 = 11 is the cumulative sum for 7/17 . We have different values for Value1 field for this date . Whenever user searches DesiredOpen based on this value (VALUE1) he should see 11 for 7/17 .

    For Desired Open counts

    7/23/2015, (2+5+8) = 15 and DesiredOpen for 7/122 = 37 so 37 + 15 = 52 is the cumulative sum for 7/17 . We have different values for Value1 field for this date . Whenever user searches DesiredOpen based on this value (VALUE1) he should see 11 for 7/17 .

    There is some issue with ClosedDate that I am still looking at.

    Thanks,

    PSB

  • December 8, 2015 at 1:11 pm

    #1844762

    This follows the rule for Desired Open.

    I'm guessing that you're getting these values from a query and not a table, and the Closed Count should have the value only for one row, instead of 3. That problem is not shown before because of the zeros.

    You might need to do the calculations before joining open and closed columns.

    WITH CTE AS(

    SELECT IndividualDate,

    OpenCount,

    SUM(OpenCount) OVER(ORDER BY IndividualDate ROWS UNBOUNDED PRECEDING) AS DesiredOpen,

    OpenDate,

    ClosedDate,

    ClosedCount,

    SUM(ClosedCount) OVER(ORDER BY IndividualDate ROWS UNBOUNDED PRECEDING) AS DesiredClose,

    VALUE1

    FROM #TestTable

    )

    SELECT IndividualDate,

    OpenCount,

    MAX(DesiredOpen) OVER(PARTITION BY IndividualDate) AS DesiredOpen,

    OpenDate,

    ClosedDate,

    ClosedCount,

    MAX(DesiredClose) OVER(PARTITION BY IndividualDate) AS DesiredClose,

    VALUE1, 'x'

    FROM CTE;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply