Query Shorthand

clbardwe@gpi.com · #1 (**permalink**) 01-25-07, 15:57

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);

r937 · #2 (**permalink**) 01-25-07, 17:02

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

Internet Services

Web Development

Digital Marketing

Consumer Tech