Results 1 to 2 of 2

Thread: Query Shorthand

  1. #1
    Join Date
    Jan 2007
    Posts
    6

    Unanswered: Query Shorthand

    I'm probably only going to hear that it can't be done, but I'm going to ask anyway...

    I have a query that reads (among other things) a date/time field. The date/time field is localized to east coast. I have another table where the timezone offset is kept (for stores in central, mountain, and pacific zones). I know what the calculation is to get the local time for a store. Unfortunately my query uses the date/time a lot, so I would have to use that calculation a lot. What I would like is to ba able to perform the calculation for each record and use the result in the rest of the query.

    Something like, instead of this:
    Code:
         ,SUM(CASE WHEN DATE_FORMAT(DATE_ADD(TransDt, INTERVAL tblStores.TimeZone HOUR), '%H') = '07' THEN 1 ELSE 0 END) AS Hour7
    Id like to do something like this:
    Code:
         ,DATE_ADD(TransDt, INTERVAL tblStores.TimeZone HOUR) AS LocalDate
         ,SUM(CASE WHEN DATE_FORMAT(LocalDate, '%H') = '07' THEN 1 ELSE 0 END) AS Hour7
    Basically using the calculation of the date/time to create a value AS LocalDate and then using that in subsequent calculations (CASE statements).

    The above won't work, however. I'm getting an "Unknown column 'LocalDate' in 'field list'" error.

    Is there any other way of doing this?

    I am running MySQL 4.1 and I have no authority to create functions (or anything else).

    My current query:
    Code:
     SELECT 
         DAYNAME(TransDt) AS 'Day'
         ,SUM(CASE WHEN DATE_FORMAT(TransDt, '%H') = '07' THEN 1 ELSE 0 END) AS Hour7
         ,SUM(CASE WHEN DATE_FORMAT(TransDt, '%H') = '08' THEN 1 ELSE 0 END) AS Hour8
        #DATE_FORMAT's '09' through '19' trimmed out for length
         ,SUM(CASE WHEN DATE_FORMAT(TransDt, '%H') = '20' THEN 1 ELSE 0 END) AS Hour20
         ,SUM(CASE WHEN DATE_FORMAT(TransDt, '%H') = '21' THEN 1 ELSE 0 END) AS Hour21
         ,SUM(CASE WHEN (DATE_FORMAT(TransDt, '%H') < '07') OR (DATE_FORMAT(TransDt, '%H') > '21') THEN 1 ELSE 0 END) AS HourOther
         ,COUNT(InvNum) AS DayTotal
        ,1 AS Position1
    FROM 
        tblTransactions
            LEFT JOIN tblStores
                ON tblTransactions.StoreNum = tblStores.StoreNum 
    WHERE 
        (StoreNum = 123)
        AND (Date(TransDt) >= '2006-12-04') 
        AND (Date(TransDt) <= '2006-12-31')
    GROUP BY 
         DAYNAME(TransDt);

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    just shove the calculation down into a subquery
    Code:
    select foo
         , bar
         , foo - x as p
         , bar + x as q
         , x*x as xsquared
      from (
           select foo
                , bar
                , some_immensely_complex_expression as x
             from daTable
           ) as dt
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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