SQL Generate Missing Records in SELECT

Question

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

Answer 1

Try with following Steps. It will be very dynamic one. (Note : Same date 00 and 24 won't come)

--Create Table :
Create Table #Table
(
    Day Date,
    Hour Int,
    Value Int
)
Go

-- Insert Values :
Insert into #Table Values('2012-10-01','12','780')
Insert into #Table Values('2012-10-01','14','678')
Insert into #Table Values('2012-10-02','08','123')
Insert into #Table Values('2012-10-02','09','473')
Go

--View Data :
Select * from #Table

Declare @TempTable as Table
(Day Date,Hour Int,Value Int)

Declare @Date Date
Declare @i Int
Set @i = 0

--Using Cursor :
Declare cur  Cursor
for
Select Distinct Day from #Table 

Open cur
Fetch Next From cur Into  @Date

While @@Fetch_status = 0
BEGIN
      While (@i <=23)
      Begin
            If not exists (Select 1 from #Table Where Day =@Date and Hour = @i )
            Begin
                    Insert into @TempTable Values (@Date,@i,0)      
            End 
            Else
            Begin
                Insert into @TempTable 
                Select Day,Hour,Value from #Table Where Day =@Date and Hour = @i                    
            End         

            Set @i = @i + 1         
        End
        Fetch Next From cur Into @Date
End      
Close cur
Deallocate cur

--Results :
Select * from @TempTable

--Clean Up :
Drop Table #Table

Answer 2

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