Using MS SQL Server 8.0.760 (2000)
I have a Table like this:
Table A
Day | Hour | Value
2012-10-01| 12 | 780
2012-10-01| 14 | 678
2012-11-02| 08 | 123
2012-11-02| 09 | 473
Expected Result should be:
Day | Hour | Value
2012-10-01| 00 | 0
2012-10-01| 01 | 0
2012-10-01| 02 | 0
2012-10-01| .. | ..
2012-10-01| 12 | 780
2012-10-01| 13 | 0
2012-10-01| 14 | 678
2012-10-01| .. | ..
2012-10-01| 22 | 0
2012-10-01| 23 | 0
2012-10-01| 24 | 0
2012-11-02| 00 | 0
2012-11-02| 01 | 0
2012-11-02| 02 | 0
2012-11-02| .. | ..
2012-11-02| 08 | 123
2012-11-02| 09 | 473
2012-11-02| .. | ..
2012-11-02| 22 | 0
2012-11-02| 23 | 0
2012-11-02| 24 | 0
So the missing hours are generated with Zero Values..
Any Idea?
EDIT 1
Tried this:
DECLARE @tmpHours TABLE
(
tmpHour varchar(2)
)
INSERT INTO @tmpHours VALUES ('00')
INSERT INTO @tmpHours VALUES ('01')
...
INSERT INTO @tmpHours VALUES ('23')
INSERT INTO @tmpHours VALUES ('24')
SELECT * FROM [A]
FULL JOIN @tmpHours tmp ON tmp.[tmpHour] = [A].[Hour]
ORDER BY [Day], [Hour], [tmpHour]
But this generates this:
Day Hour Value tmpHour
NULL NULL NULL 00
NULL NULL NULL 01
NULL NULL NULL 02
NULL NULL NULL 03
NULL NULL NULL 04
NULL NULL NULL 05
NULL NULL NULL 06
NULL NULL NULL 07
NULL NULL NULL 10
NULL NULL NULL 11
NULL NULL NULL 13
NULL NULL NULL 15
NULL NULL NULL 16
NULL NULL NULL 17
NULL NULL NULL 18
NULL NULL NULL 19
NULL NULL NULL 20
NULL NULL NULL 21
NULL NULL NULL 22
NULL NULL NULL 23
NULL NULL NULL 24
2012-10-01 00:00:00.000 12 780 12
2012-10-01 00:00:00.000 14 678 14
2012-11-02 00:00:00.000 08 123 08
2012-11-02 00:00:00.000 09 473 09
You can create an table (possibly temporary) called container your hours (your example shows 25 hours from 00 to 24 but, I guess you want 24 hours). Then you can do an outer join between Table A
with your HOURS table. This will generate NULL
values instead of 0 values. If need be, you can use a function to convert NULL values to 0.
EDIT refactored answer into a single SQL query:
SELECT X.*, A.VALUE
FROM A
RIGHT OUTER JOIN
(
SELECT *
FROM
(SELECT DISTINCT A.DAY FROM A) DAYS,
(SELECT 0 HOUR
UNION ALL SELECT 1 HOUR
UNION ALL SELECT 2 HOUR
UNION ALL SELECT 3 HOUR
UNION ALL SELECT 4 HOUR
UNION ALL SELECT 5 HOUR
UNION ALL SELECT 6 HOUR
UNION ALL SELECT 7 HOUR
UNION ALL SELECT 8 HOUR
UNION ALL SELECT 9 HOUR
UNION ALL SELECT 10 HOUR
UNION ALL SELECT 11 HOUR
UNION ALL SELECT 12 HOUR
UNION ALL SELECT 13 HOUR
UNION ALL SELECT 14 HOUR
UNION ALL SELECT 15 HOUR
UNION ALL SELECT 16 HOUR
UNION ALL SELECT 17 HOUR
UNION ALL SELECT 18 HOUR
UNION ALL SELECT 19 HOUR
UNION ALL SELECT 20 HOUR
UNION ALL SELECT 21 HOUR
UNION ALL SELECT 22 HOUR
UNION ALL SELECT 23 HOUR
) HOURS
) X
ON X.DAY = A.DAY AND X.HOUR = A.HOUR