Create Temp table with all 24 hours in day

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create Temp table with all 24 hours in day

Author

Topic

ImTheodore
Starting Member

16 Posts

Posted - 2012-07-31 : 11:59:16

I have a list of totals that are based on a three year timespan. The problem is that some hours have a total of '0' and thus do not show up in the query.

I was thinking I could simply join my query with a temp table that lists all 24 (0-23) hours per day for the three years, but I don't know how to create it.

Thanks...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-31 : 12:35:12

with cte as
(select h = converrt(datetime,'20090101'
union all
select dateadd(hh,1,h) from cte where h < getdate()
)
select h.hh, t.total
from cte h
left join mytots t
on h.h = t.h
option (maxrecursion 0)

Might be better off with a permenant calendar and hour table for this.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-31 : 12:35:45

You can use a recursive CTE like this. I have assumed SQL 2008. If you are on SQL 2005, the DATE data type would need to be changed.

CREATE TABLE #A(Dt DATETIME);

DECLARE @startDate DATE = DATEADD(YEAR,-3,GETDATE());

;WITH cte(DateAndHour) AS
(
	SELECT
		CAST(CAST(@startDate AS DATE) AS DATETIME)
	UNION ALL
	SELECT
		DATEADD(hour,1,DateAndHour)
	FROM
		cte
	WHERE
		DateAndHour < CAST(GETDATE() AS DATE)
)
INSERT INTO #A
SELECT * FROM cte OPTION (MAXRECURSION 0);

DROP TABLE #A;

ImTheodore
Starting Member

16 Posts

Posted - 2012-07-31 : 14:58:03

Thanks for your help, I was not able to get either of these to work for me I'm on SQL version 8. I have to use a temp table since I do not have access to write to the db, currently I am exporting to Access to join.

What I have to join is:

Select
[Day],
[Time],
[Day of Week],
[TL1],
[TL2-3],
[TL4-5]
From
#Temp

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-31 : 15:33:16

Are you using Microsoft SQL Server? I am not familiar with a version 8 (as commonly referred to version number). If it is SQL 2000, you should be able to use the following query

CREATE TABLE #A(DatesAndHours DATETIME NOT NULL PRIMARY KEY CLUSTERED);

DECLARE @hours INT;
SET @hours = 365*3*24;

INSERT INTO #A
SELECT DATEADD(hh, e.N*10000 + d.N*1000+c.N*100+b.N*10 + a.N, '20090101') FROM 
(
SELECT 1 as N UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL 
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 
UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 0
) a
CROSS JOIN 
(
SELECT 1 as N UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL 
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 
UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 0
) b
CROSS JOIN 
(
SELECT 1 as N UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL 
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 
UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 0
) c
CROSS JOIN 
(
SELECT 1 as N UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL 
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 
UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 0
) d
CROSS JOIN 
(
SELECT 1 as N UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL 
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 
UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 0
) e

WHERE e.N*10000+ d.N*1000+c.N*100+b.N*10 + a.N < @hours
ORDER BY 1

No, it is not very pretty :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources