CODEWITHSUNDEEP
sqlt-sqljoinnullsum
JJS
JJS

Reputation: 61

cumulative sum over outer joined table with null values

I have a table with 2 date columns which represent the creation and closing of a JIRA item respectively. For a report I need to get the number of created and closed items per date, and cumulative.

However on some dates there are no items created or closed, so the full outer join returns null values. This leads to null values in the cumulative sum columns which Im not able to correct.

I have tried a lot of different things with coalesce'ing:

Select
    coalesce(A.createdate, B.closedate) datum
    ,COALESCE(A.created, 0) created
    ,COALESCE(B.closed,0) closed
    ,sum(created) OVER(ORDER BY A.createdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cumSum
    ,sum(coalesce(created,0)) OVER(ORDER BY A.createdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cumSum2
    ,COALESCE(sum(created) OVER(ORDER BY A.createdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),0) cumSum3
    ,sum(coalesce(created,0)) OVER(ORDER BY A.createdate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 preceding) + coalesce(created,0) cumSum4
from
(SELECT convert(date,[Created]) createdate, COUNT(*) created
  FROM [jira].[dbo].[report_clean_full]
  group by convert(date,[Created])) A
full outer join (SELECT convert(date,Resolved) closedate, COUNT(*) closed
  FROM [jira].[dbo].[report_clean_full]
  where status = 'Closed'
  group by convert(date,Resolved)) B
on A.createdate = B.closedate
order by 1

result

2012-08-17  1   0   1   1   1   1
2012-08-23  1   0   2   2   2   2
2012-08-30  4   0   6   6   6   6
2012-09-03  1   0   7   7   7   7
2012-09-06  25  0   32  32  32  32
2012-09-07  30  3   62  62  62  62
2012-09-10  11  6   73  73  73  73
2012-09-11  6   1   79  79  79  79
2012-09-13  1   0   80  80  80  80
2012-09-14  10  1   90  90  90  90
2012-09-17  7   1   97  97  97  97
2012-09-18  4   2   101 101 101 101
2012-09-19  4   2   105 105 105 105
2012-09-20  19  5   124 124 124 124
2012-09-21  12  0   136 136 136 136
2012-09-24  7   1   143 143 143 143
2012-09-26  0   7   NULL    0   0   0
2012-09-27  18  2   161 161 161 161
2012-09-28  7   0   168 168 168 168
2012-10-01  4   1   172 172 172 172

on 2012-09-26 there were only items closed, but I want the cumSum to show 143 rather than 0 (and eventually repeat for closed items and subtract to get the nr. of open items)

can anyone explain what Im doing wrong?

Upvotes: 4

Views: 475

Answers (1)

JJS
JJS

Reputation: 61

similar to my response to gpn's comment:

the solution is to coalesce the dates of the ORDER BY in the OVER(). As I understand the problem is not the values itself but rather the NULL range.

sum(coalesce(created,0)) OVER(ORDER BY coalesce(A.createdate,b.closedate) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cumSumCreated

Upvotes: 2

Related Questions