if or case statement 1

[thompom]

hi,

have a query that needs to sum as holsum if hourdayid = 1
or sum as holsumhours if hourdayid = 2

am trying to use mysql IF statement had tried using case
but couldnt get it to work - could use two queries using where hourdayid=1 etc but this seems a waste

thanks MG

SELECT CC.code, CC.holtype, CBB.halfday,
IF CBB.hourdayid = '2' THEN 

(SUM(cbb.mfithours) AS holsumhours)

ELSE 

(SUM(CASE WHEN CBB.halfday = 'checked' THEN ((TO_DAYS(CBB.bookend) - TO_DAYS(CBB.bookst) + 1)/2) 
ELSE (TO_DAYS(CBB.bookend) - TO_DAYS(CBB.bookst) + 1) END) AS holsum)

END IF

FROM holtype AS CC 
INNER JOIN
(SELECT CB.bookst, CB.bookend, CB.userholid, CB.holbookid, CB.holtypeid, CB.deptid, CB.halfday, CB.hourdayid, CB.mfithours, CB.hourdayid 
FROM holbook AS CB 
WHERE CB.userholid = '79' 
AND ((cb.bookst BETWEEN '20080101' AND '20081231') 
OR (cb.bookend BETWEEN '20080101' AND '20081231') 
OR (cb.bookst <= '20080101') AND (cb.bookend >= '20081231'))) 
AS CBB ON CC.holtypeid = CBB.holtypeid 
GROUP BY CC.holtypeid 
ORDER BY CC.holtypeid

 

[r937]

SELECT CC.code
     , CC.holtype
     , CBB.halfday
     , [blue]SUM(CASE WHEN CBB.hourdayid = '2' 
                THEN CBB.mfithours
                ELSE NULL 
            END) AS holsumhours
     , SUM(CASE WHEN CBB.halfday = 'checked' 
                THEN (TO_DAYS(CBB.bookend) - TO_DAYS(CBB.bookst) + 1)/2
                ELSE (TO_DAYS(CBB.bookend) - TO_DAYS(CBB.bookst) + 1) 
            END) AS holsum[/blue]
  FROM holtype AS CC 
INNER 
  JOIN ( SELECT CB.bookst
              , CB.bookend
              , CB.userholid
              , CB.holbookid
              , CB.holtypeid
              , CB.deptid
              , CB.halfday
              , CB.hourdayid
              , CB.mfithours
              , CB.hourdayid 
           FROM holbook AS CB 
          WHERE CB.userholid = '79' 
            AND (
                (cb.bookst BETWEEN '20080101' AND '20081231') 
             OR (cb.bookend BETWEEN '20080101' AND '20081231') 
             OR (cb.bookst <= '20080101') AND (cb.bookend >= '20081231')
                )
       ) AS CBB 
    ON CC.holtypeid = CBB.holtypeid 
GROUP 
    BY CC.holtypeid 
ORDER 
    BY CC.holtypeid

r937.com | rudy.ca

 

[thompom]

rudy - thanks alot looks great but at the moment if
cbb.hourdayid = 2 then it sums mfithours but also adds a day to holsum - i need it so that if hourdayid = 2 then doesnt add a day to holsum

 

[r937]

     , SUM(CASE CBB.hourdayid = '2' 
                THEN 0
                WHEN CBB.halfday = 'checked' 
                THEN (TO_DAYS(CBB.bookend) - TO_DAYS(CBB.bookst) + 1)/2
                ELSE (TO_DAYS(CBB.bookend) - TO_DAYS(CBB.bookst) + 1) 
            END) AS holsum

r937.com | rudy.ca

 

[thompom]

hi - get a syntax error near THEN 0

SELECT CC.code
     , CC.holtype
     , CBB.halfday
     , SUM(CASE WHEN CBB.hourdayid = '2' 
                THEN CBB.mfithours
                ELSE NULL 
            END) AS holsumhours
     , SUM(CASE CBB.hourdayid = '2' 
                THEN 0
                WHEN CBB.halfday = 'checked' 
                THEN (TO_DAYS(CBB.bookend) - TO_DAYS(CBB.bookst) + 1)/2
                ELSE (TO_DAYS(CBB.bookend) - TO_DAYS(CBB.bookst) + 1) 
            END) AS holsum
  FROM holtype AS CC

 

[r937]

well, it looks like i forgot to copy/paste the WHEN keyword

you should be able to spot that type of error, eh

;-)

r937.com | rudy.ca

 

[thompom]

got it - thanks sooo much heres a * - cheers rudy