filling in the holes in the result of a query

Posted by ????? ???????? on Stack Overflow See other posts from Stack Overflow or by ????? ????????
Published on 2012-06-28T16:47:05Z Indexed on 2012/06/28 21:16 UTC
Read the original article Hit count: 326

Filed under:
|
|

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]

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server