Generate every combination or hour, minute and second. 1

[TheBugSlayer]

I have the need to generate all combinations of hours, minutes and seconds for an audit viewer app. I love CTEs so I came up with this:

WITH CTE_Hours
AS
(
  SELECT 0 AS HourCol
  UNION ALL
  SELECT HourCol + 1
  FROM CTE_Hours
  WHERE HourCol + 1 <= 23
)
,
CTE_Minutes
AS
(
  SELECT 0 AS MinuteCol
  UNION ALL
  SELECT MinuteCol + 1
  FROM CTE_Minutes
  WHERE MinuteCol + 1 <= 59
)
SELECT HourCol, CTE_Minutes.MinuteCol, Sec.MinuteCol AS SecondCol
FROM CTE_Hours, CTE_Minutes, CTE_Minutes AS Sec
ORDER BY HourCol, MinuteCol, SecondCol

and challenged my coworkers to suggest equivalent or better. One of them came up with this:

SELECT  h.number Hours,
        m.number Minutes,
        s.number Seconds
FROM    master..spt_values h
        CROSS JOIN master..spt_Values m
        CROSS JOIN master..spt_values s
WHERE   h.number < 24
        AND s.number < 60
        AND m.number < 60
        AND m.type = 'p'
        AND s.type = 'p'
        AND h.type = 'p'
ORDER BY h.number ,
        m.number ,
        s.number

which is much simpler IMO and does not require understanding of recursice Common Table Expressions.

There is someone on this forum who LOVES number tables...probaby George, SQLBill and the other guy whose name I forget

Do you know any other way?

Regards.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

Do you need to have 3 separate columns for hours, minutes, and seconds?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Yes George, it makes for better filtering. The Audit table keeps the time separate from the date in the 'hhmmss' format. If I kept them all together then I will have to add all 86400 members to one list box as opposed to three list boxes with 24, 60 and 60 items respectively. Do you know a different way?

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

As far as getting every combination.... I don't really see a better way. I am a fan of numbers tables. My numbers table has more than 86,400 rows so I can easily select time by using DateAdd(second, Num, 0). This gave slightly better performance and cleaner code, but results in a single datetime column.

As for the front end list boxes.... not a fan of this approach. I suppose it depends on your front end, but I would use some sort of DateTime picker control, set to a format of hh:mm:ss. Then it's a simple matter of extracting the hours, minutes, and seconds (in the front end) to pass to the database.

In my opinion, it's not bad to have a list box with 24 items, but 60 items is a bit much (for me) because it requires a lot of scrolling.


Blah, blah, blah... this is all just my opinion.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Actually, this is not even necessary. If anything the DTP with a custom format like you suggest, or a Numeric UpDown control would take care of it. Nonetheless, I just wanted to share the finding...and confirm who the fan of the numbers table was

Thank you.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)