I have two tables, one with costs data, and another with session data. The costs data contains zero, one or more records for a given day and the session data usually starts and ends on the same day, but can span any number of days.
I want to sum the costs for a particular day then join this to the session data where the costs date is between the start and end dates of the session data.
I have created the following query which runs but only shows the last cost record, rather than sums all the (summed) cost records.
S.ZTCashTendered - (S.ZTCashDeclared + C.cost) As variance
ztotals As S Left Join
costs.CTill As till,
Date(costs.CDate) As costdate,
Sum(costs.CAmount) As cost
costs.CDate > '20111031' And
costs.CNoSaleType = 'Petty Cash'
costs.CTill) As C On C.till = S.ZTTill And C.costdate Between S.ZTStart And
S.ZTStart > '20111101'
The inner query works and sums costs by day. Any ideas how I can get it to sum all records returned by the inner query?
SELECT s.ZTStart, s.ZTEnd, c.cost, c.costdate, s.ZTOpeningFloat,
s.ZTClosingFloat, s.ZTCashTendered, s.ZTCashDeclared,
sum( case when c.costdate between s.ZTStart and s.ZTEnd
then s.ZTCashTendered - s.ZTCashDeclared + ifnull( c.cost, 0 )
else 0 end ) as variance
FROM ztotals s
JOIN ( SELECT CTill as till, date( CDate ) as costdate, sum( CAmount ) as cost
WHERE CDate > '20111031'
AND CNoSaleType = 'Petty Cash'
GROUP BY 1, 2 ) as c on c.till = s.ZTTill
WHERE s.ZTStart > '20111101'
GROUP BY 1, 2
ORDER BY 1
Without some value examples - i'm thinking this is what your looking for...
Last edited by dockraz; 08-28-12 at 13:06.
Reason: forgot the group by