filling in the holes in the result of a query
- by ????? ????????
my query is returning:
+------+------+------+------+------+------+------+-------+------+------+------+------+-----+
| Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Bla |
+------+------+------+------+------+------+------+-------+------+------+------+------+-----+
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 13 |
| 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 14 |
| 0 | 0 | 0 | 0 | 0 | 9 | 0 | 0 | 0 | 0 | 8 | 37 | 29 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 374 | 30 |
| 0 | 0 | 1 | 0 | 78 | 2 | 4 | 8 | 57 | 169 | 116 | 602 | 31 |
| 156 | 255 | 79 | 75 | 684 | 325 | 289 | 194 | 407 | 171 | 584 | 443 | 32 |
| 1561 | 2852 | 2056 | 796 | 2004 | 1755 | 879 | 1052 | 1490 | 1683 | 2532 | 2381 | 33 |
| 4167 | 3841 | 4798 | 3399 | 4132 | 5849 | 3157 | 4381 | 4424 | 4487 | 4178 | 5343 | 34 |
| 5472 | 5939 | 5768 | 4150 | 7483 | 6836 | 6346 | 6288 | 6850 | 7155 | 5706 | 5231 | 35 |
| 5749 | 4741 | 5264 | 4045 | 6544 | 7405 | 7524 | 6625 | 6344 | 5508 | 6513 | 3854 | 36 |
| 5464 | 6323 | 7074 | 4861 | 7244 | 6768 | 6632 | 7389 | 8077 | 8745 | 6738 | 5039 | 37 |
| 5731 | 7205 | 7476 | 5734 | 9103 | 9244 | 7339 | 8970 | 9726 | 9089 | 6328 | 5512 | 38 |
| 7262 | 6149 | 8231 | 6654 | 9886 | 9834 | 9306 | 10065 | 9983 | 9984 | 6738 | 5806 | 39 |
| 5886 | 6934 | 7137 | 6978 | 9034 | 9155 | 7389 | 9437 | 9711 | 8665 | 6593 | 5337 | 40 |
+------+------+------+------+------+------+------+-------+------+------+------+------+-----+
as you can see the BLA column starts from 13. i want it to start from 1, then 2, then 3 etc......I do not want any gaps in the data. The reason there are gaps is because all of the months are 0 for that specific bla
how do i get the result set to include ALL values for BLA, even ones that will yield 0 for the months?
here are the desired results:
+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+
| Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Bla |
+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 5 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 6 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 7 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 8 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 9 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 10 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 11 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 12 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 13 |
| 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 14 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 15 |
| … | … | … | … | … | … | … | … | … | … | … | … | … |
+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+
here's my query:
WITH CTE AS
(
select
sum(case when datepart(month,[datetime entered]) = 1 then 1 end) as Jan,
sum(case when datepart(month,[datetime entered]) = 2 then 1 end) as Feb,
sum(case when datepart(month,[datetime entered]) = 3 then 1 end) as Mar,
sum(case when datepart(month,[datetime entered]) = 4 then 1 end) as Apr,
sum(case when datepart(month,[datetime entered]) = 5 then 1 end) as May,
sum(case when datepart(month,[datetime entered]) = 6 then 1 end) as Jun,
sum(case when datepart(month,[datetime entered]) = 7 then 1 end) as Jul,
sum(case when datepart(month,[datetime entered]) = 8 then 1 end) as Aug,
sum(case when datepart(month,[datetime entered]) = 9 then 1 end) as Sep,
sum(case when datepart(month,[datetime entered]) = 10 then 1 end) as Oct,
sum(case when datepart(month,[datetime entered]) = 11 then 1 end) as Nov,
sum(case when datepart(month,[datetime entered]) = 12 then 1 end) as Dec,
DATEPART(yyyy,[datetime entered]) as [Year],
bla= CASE WHEN datediff(d, CAST([datetime entered] as DATE), CAST([datetime completed] as DATE))*24 +
CONVERT(CHAR(2),[datetime completed],108)
>191 THEN 192 ELSE datediff(d, CAST([datetime entered] as DATE), CAST([datetime completed] as DATE))*24 +
CONVERT(CHAR(2),[datetime completed],108) END
--,datediff(d, CAST([datetime entered] as DATE), CAST([datetime completed] as DATE)) AS Sort_Days,
--DATEPART(hour, [datetime completed] ) AS Sort_Hours
from [TurnAround]
group by
datediff(d, CAST([datetime entered] as DATE), CAST([datetime completed] as DATE))*24 +
CONVERT(CHAR(2),[datetime completed],108),
DATEPART(yyyy,[datetime entered])
, [datetime entered]
--[DateTime Completed]
)
SELECT ISNULL(SUM(Jan),0) Jan,
ISNULL(SUM(Feb),0) Feb,
ISNULL(SUM(Mar),0) Mar,
ISNULL(SUM(Apr),0) Apr,
ISNULL(SUM(May),0) May,
ISNULL(SUM(Jun),0) Jun,
ISNULL(SUM(Jul),0) Jul,
ISNULL(SUM(Aug),0) Aug,
ISNULL(SUM(Sep),0) Sep,
ISNULL(SUM(Oct),0) Oct,
ISNULL(SUM(Nov),0) Nov,
ISNULL(SUM(Dec),0) Dec,
[year],
--,Sort_Hours,
--Sort_Days,
A.RN Bla
FROM ( SELECT *, RN=ROW_NUMBER() OVER(ORDER BY object_id)
FROM sys.all_objects) A
LEFT JOIN CTE B
ON A.RN = CASE WHEN B.Bla > 191 THEN 192 ELSE B.Bla END
WHERE A.RN BETWEEN 1 AND 192
GROUP BY A.RN,[year]
