Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Unanswered: More elegant way to write this w/ CTE's?

    Is there a more elegant way to write this cross join to create 5 minute intervals over 24 hours using a CTE? I need to write the same query for 1 minute intervals and I am not looking forward to it.


    Code:
    SELECT CAST ((CAST(Q1.HH AS VARCHAR(2)) + ':' + CAST(Q2.MM AS VARCHAR(2)) + ':00') AS TIME)
    FROM
    (
    	-- HOUR
    	SELECT 0 AS HH
    	UNION
    	SELECT 1
    	UNION
    	SELECT 2
    	UNION
    	SELECT 3
    	UNION
    	SELECT 4
    	UNION
    	SELECT 5
    	UNION
    	SELECT 6
    	UNION
    	SELECT 7
    	UNION
    	SELECT 8
    	UNION
    	SELECT 9
    	UNION
    	SELECT 10
    	UNION
    	SELECT 11
    	UNION
    	SELECT 12
    	UNION
    	SELECT 13
    	UNION
    	SELECT 14
    	UNION
    	SELECT 15
    	UNION
    	SELECT 16
    	UNION
    	SELECT 17
    	UNION
    	SELECT 18
    	UNION
    	SELECT 19
    	UNION
    	SELECT 20
    	UNION
    	SELECT 21
    	UNION
    	SELECT 22
    	UNION
    	SELECT 23
    ) Q1
    CROSS JOIN
    (
    	SELECT 0 AS MM
    	UNION
    	SELECT 5
    	UNION
    	SELECT 10
    	UNION
    	SELECT 15
    	UNION
    	SELECT 20
    	UNION
    	SELECT 25
    	UNION
    	SELECT 30
    	UNION
    	SELECT 35
    	UNION
    	SELECT 40
    	UNION
    	SELECT 45
    	UNION
    	SELECT 50
    	UNION
    	SELECT 55
    ) Q2
    ORDER BY 
    1
    results...

    Code:
    ----------------
    00:00:00.0000000
    00:05:00.0000000
    00:10:00.0000000
    00:15:00.0000000
    00:20:00.0000000
    00:25:00.0000000
    00:30:00.0000000
    00:35:00.0000000
    00:40:00.0000000
    00:45:00.0000000
    00:50:00.0000000
    00:55:00.0000000
    01:00:00.0000000
    01:05:00.0000000
    01:10:00.0000000
    01:15:00.0000000
    01:20:00.0000000
    01:25:00.0000000
    01:30:00.0000000
    01:35:00.0000000
    01:40:00.0000000
    01:45:00.0000000
    01:50:00.0000000
    01:55:00.0000000
    02:00:00.0000000
    02:05:00.0000000
    02:10:00.0000000
    02:15:00.0000000
    02:20:00.0000000
    02:25:00.0000000
    02:30:00.0000000
    02:35:00.0000000
    02:40:00.0000000
    02:45:00.0000000
    02:50:00.0000000
    02:55:00.0000000
    03:00:00.0000000
    03:05:00.0000000
    03:10:00.0000000
    03:15:00.0000000
    03:20:00.0000000
    03:25:00.0000000
    03:30:00.0000000
    03:35:00.0000000
    03:40:00.0000000
    03:45:00.0000000
    03:50:00.0000000
    03:55:00.0000000
    04:00:00.0000000
    04:05:00.0000000
    04:10:00.0000000
    04:15:00.0000000
    04:20:00.0000000
    04:25:00.0000000
    04:30:00.0000000
    04:35:00.0000000
    04:40:00.0000000
    04:45:00.0000000
    04:50:00.0000000
    04:55:00.0000000
    05:00:00.0000000
    05:05:00.0000000
    05:10:00.0000000
    05:15:00.0000000
    05:20:00.0000000
    05:25:00.0000000
    05:30:00.0000000
    05:35:00.0000000
    05:40:00.0000000
    05:45:00.0000000
    05:50:00.0000000
    05:55:00.0000000
    06:00:00.0000000
    06:05:00.0000000
    06:10:00.0000000
    06:15:00.0000000
    06:20:00.0000000
    06:25:00.0000000
    06:30:00.0000000
    06:35:00.0000000
    06:40:00.0000000
    06:45:00.0000000
    06:50:00.0000000
    06:55:00.0000000
    07:00:00.0000000
    07:05:00.0000000
    07:10:00.0000000
    07:15:00.0000000
    07:20:00.0000000
    07:25:00.0000000
    07:30:00.0000000
    07:35:00.0000000
    07:40:00.0000000
    07:45:00.0000000
    07:50:00.0000000
    07:55:00.0000000
    08:00:00.0000000
    08:05:00.0000000
    08:10:00.0000000
    08:15:00.0000000
    08:20:00.0000000
    08:25:00.0000000
    08:30:00.0000000
    08:35:00.0000000
    08:40:00.0000000
    08:45:00.0000000
    08:50:00.0000000
    08:55:00.0000000
    09:00:00.0000000
    09:05:00.0000000
    09:10:00.0000000
    09:15:00.0000000
    09:20:00.0000000
    09:25:00.0000000
    09:30:00.0000000
    09:35:00.0000000
    09:40:00.0000000
    09:45:00.0000000
    09:50:00.0000000
    09:55:00.0000000
    10:00:00.0000000
    10:05:00.0000000
    10:10:00.0000000
    10:15:00.0000000
    10:20:00.0000000
    10:25:00.0000000
    10:30:00.0000000
    10:35:00.0000000
    10:40:00.0000000
    10:45:00.0000000
    10:50:00.0000000
    10:55:00.0000000
    11:00:00.0000000
    11:05:00.0000000
    11:10:00.0000000
    11:15:00.0000000
    11:20:00.0000000
    11:25:00.0000000
    11:30:00.0000000
    11:35:00.0000000
    11:40:00.0000000
    11:45:00.0000000
    11:50:00.0000000
    11:55:00.0000000
    12:00:00.0000000
    12:05:00.0000000
    12:10:00.0000000
    12:15:00.0000000
    12:20:00.0000000
    12:25:00.0000000
    12:30:00.0000000
    12:35:00.0000000
    12:40:00.0000000
    12:45:00.0000000
    12:50:00.0000000
    12:55:00.0000000
    13:00:00.0000000
    13:05:00.0000000
    13:10:00.0000000
    13:15:00.0000000
    13:20:00.0000000
    13:25:00.0000000
    13:30:00.0000000
    13:35:00.0000000
    13:40:00.0000000
    13:45:00.0000000
    13:50:00.0000000
    13:55:00.0000000
    14:00:00.0000000
    14:05:00.0000000
    14:10:00.0000000
    14:15:00.0000000
    14:20:00.0000000
    14:25:00.0000000
    14:30:00.0000000
    14:35:00.0000000
    14:40:00.0000000
    14:45:00.0000000
    14:50:00.0000000
    14:55:00.0000000
    15:00:00.0000000
    15:05:00.0000000
    15:10:00.0000000
    15:15:00.0000000
    15:20:00.0000000
    15:25:00.0000000
    15:30:00.0000000
    15:35:00.0000000
    15:40:00.0000000
    15:45:00.0000000
    15:50:00.0000000
    15:55:00.0000000
    16:00:00.0000000
    16:05:00.0000000
    16:10:00.0000000
    16:15:00.0000000
    16:20:00.0000000
    16:25:00.0000000
    16:30:00.0000000
    16:35:00.0000000
    16:40:00.0000000
    16:45:00.0000000
    16:50:00.0000000
    16:55:00.0000000
    17:00:00.0000000
    17:05:00.0000000
    17:10:00.0000000
    17:15:00.0000000
    17:20:00.0000000
    17:25:00.0000000
    17:30:00.0000000
    17:35:00.0000000
    17:40:00.0000000
    17:45:00.0000000
    17:50:00.0000000
    17:55:00.0000000
    18:00:00.0000000
    18:05:00.0000000
    18:10:00.0000000
    18:15:00.0000000
    18:20:00.0000000
    18:25:00.0000000
    18:30:00.0000000
    18:35:00.0000000
    18:40:00.0000000
    18:45:00.0000000
    18:50:00.0000000
    18:55:00.0000000
    19:00:00.0000000
    19:05:00.0000000
    19:10:00.0000000
    19:15:00.0000000
    19:20:00.0000000
    19:25:00.0000000
    19:30:00.0000000
    19:35:00.0000000
    19:40:00.0000000
    19:45:00.0000000
    19:50:00.0000000
    19:55:00.0000000
    20:00:00.0000000
    20:05:00.0000000
    20:10:00.0000000
    20:15:00.0000000
    20:20:00.0000000
    20:25:00.0000000
    20:30:00.0000000
    20:35:00.0000000
    20:40:00.0000000
    20:45:00.0000000
    20:50:00.0000000
    20:55:00.0000000
    21:00:00.0000000
    21:05:00.0000000
    21:10:00.0000000
    21:15:00.0000000
    21:20:00.0000000
    21:25:00.0000000
    21:30:00.0000000
    21:35:00.0000000
    21:40:00.0000000
    21:45:00.0000000
    21:50:00.0000000
    21:55:00.0000000
    22:00:00.0000000
    22:05:00.0000000
    22:10:00.0000000
    22:15:00.0000000
    22:20:00.0000000
    22:25:00.0000000
    22:30:00.0000000
    22:35:00.0000000
    22:40:00.0000000
    22:45:00.0000000
    22:50:00.0000000
    22:55:00.0000000
    23:00:00.0000000
    23:05:00.0000000
    23:10:00.0000000
    23:15:00.0000000
    23:20:00.0000000
    23:25:00.0000000
    23:30:00.0000000
    23:35:00.0000000
    23:40:00.0000000
    23:45:00.0000000
    23:50:00.0000000
    23:55:00.0000000
    Last edited by Gagnon; 09-07-10 at 16:09.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I contest the reason for doing this in the first place...

    can you explain why?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2005
    Posts
    319
    I have tick data for futures contracts, it lists transactions down to the second (sometimes multiple transactions for any given second).

    I want to roll it up for 1-minute, 5-minute, 30-minute(, etc...) intervals so I can chart against it.

    There could be gaps in the data (low volume ticker), I will be left outer joining against this derived table.

    Alternatively I could make this a regular table (instead of calculating it on the fly every time), but I still run into the same problem of the quickest way to generate the initial data load.

    I know cross join will work fine, just seeing if there is a cool recursive CTE trick I can use to save me a lot of copy/pasting/typing.

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Gagnon, Here is an example (using DB2 syntax but the basic concept of the CTE is the same).
    Code:
    WITH HOUR_TAB (HH)
      AS (SELECT 0 FROM SYSIBM.SYSDUMMY1
          UNION ALL
          SELECT HH + 1 FROM HOUR_TAB
          WHERE HH < 23
         )
       , MIN_TAB (MN)
      AS (SELECT 0 FROM SYSIBM.SYSDUMMY1
          UNION ALL
          SELECT MN + 5 FROM MIN_TAB
          WHERE MN < 55
         )
    SELECT CAST(CASE WHEN HH BETWEEN  0 AND  9 THEN '0' || HH ELSE '' || HH END AS CHAR(2))
        || ':'
        || CAST(CASE WHEN MN BETWEEN  0 AND  9 THEN '0' || MN ELSE '' || MN END AS CHAR(2))
        || ':00.000000'
    FROM HOUR_TAB
       , MIN_TAB
    ORDER BY 1
    You can remove the FROM SYSIBM.SYSDUMMY1 and the various formatting I did to get the proper output.

    The basic concept is 2 CTE queries. One to generate a list of Hours (0-23) and one for Minutes (0-55 at 5 minute intervals).

    The main query joins the 2 CTE 'table' without a join predicate (which you normally don't want but in this case it works for you) to apply every set of Minutes to every set of Hours.

    Code:
    00:00:00.000000
    00:05:00.000000
    00:10:00.000000
    00:15:00.000000
    00:20:00.000000
    00:25:00.000000
    00:30:00.000000
    00:35:00.000000
    00:40:00.000000
    00:45:00.000000
    00:50:00.000000
    00:55:00.000000
    01:00:00.000000
    01:05:00.000000
    01:10:00.000000
    01:15:00.000000
    (list truncated to save space)
    22:35:00.000000
    22:40:00.000000
    22:45:00.000000
    22:50:00.000000
    22:55:00.000000
    23:00:00.000000
    23:05:00.000000
    23:10:00.000000
    23:15:00.000000
    23:20:00.000000
    23:25:00.000000
    23:30:00.000000
    23:35:00.000000
    23:40:00.000000
    23:45:00.000000
    23:50:00.000000
    23:55:00.000000
    The intervals can be tweaked by changing the amount added.
    If you Seconds, you can use a third CTE to generate a seconds list.

    PS you can make the main query a CTE since you probably want to Left Outer Join it to another table.

  5. #5
    Join Date
    Jun 2005
    Posts
    319
    Much appreciated Stealth, I have enclosed the final SQL for anyone interested

    Code:
    WITH CTE_HOUR (HH)
      AS (SELECT 0 
          UNION ALL
          SELECT HH + 1 FROM CTE_HOUR
          WHERE HH < 23
         )
       , CTE_MIN (MN)
      AS (SELECT 0 
          UNION ALL
          SELECT MN + 5 FROM CTE_MIN
          WHERE MN < 55
         )
    
    SELECT 
    	CAST(
    		CASE 
    			WHEN H.HH BETWEEN  0 AND  9 
    			THEN '0' 
    			ELSE '' 
    		END 
    	+ CAST(H.HH AS VARCHAR(2))
    	+ ':' 
    	+
    		CASE 
    			WHEN M.MN BETWEEN  0 AND  9 
    			THEN '0' 
    			ELSE '' 
    		END 
    	+ CAST(M.MN AS VARCHAR(2)) 
    	+ ':00.000000' AS TIME
    	) AS TIME_SHORT
    FROM 
    	CTE_HOUR H
    CROSS JOIN	CTE_MIN M
    ORDER BY 1

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
        CAST(
            CASE 
                WHEN H.HH BETWEEN  0 AND  9 
                THEN '0' 
                ELSE '' 
            END 
        + CAST(H.HH AS VARCHAR(2))
    This also works:
    Code:
      RIGHT('0' + CAST(H.HH AS VARCHAR(2)), 2)
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'd use a physical numbers table, no CTE required. You'd be surprised how often a simple table of integers comes in handy (even more so before 2005 when we got nifty ranking functionality). A cross join with numbers 1-24 for hours and 0-60, where "value mod 5 = 0" should knock it out. I may be able to finagle an example later, morning meetings...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Jun 2005
    Posts
    319
    interesting Wim & Teddy, the CTE did not perform well at all, took exactly 14 minutes to generate 1-, 5-, 15-, and 30-minute chart data for 10 different contracts. Once I put the CTE data into a permanent table it took 9 seconds.

    Still send it tho Teddy, I like elegant solutions like that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •