Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011
    Posts
    10

    Unanswered: SQL Query assistance

    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.


    Select
    S.ZTStart,
    S.ZTEnd,
    C.cost,
    C.costdate,
    S.ZTOpeningFloat,
    S.ZTClosingFloat,
    S.ZTCashTendered,
    S.ZTCashDeclared,
    S.ZTCashTendered - (S.ZTCashDeclared + C.cost) As variance
    From
    ztotals As S Left Join
    (Select
    costs.CTill As till,
    Date(costs.CDate) As costdate,
    Sum(costs.CAmount) As cost
    From
    costs
    Where
    costs.CDate > '20111031' And
    costs.CNoSaleType = 'Petty Cash'
    Group By
    costs.CTill, Date(costs.CDate)
    Order By
    costs.CTill) As C On C.till = S.ZTTill And C.costdate Between S.ZTStart And
    S.ZTEnd
    Where
    S.ZTStart > '20111101'
    Order By
    S.ZTStart


    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?

    Many thanks
    Pete

  2. #2
    Join Date
    Aug 2012
    Posts
    7
    Why don't You try to execute it in 2 queries ?

  3. #3
    Join Date
    Mar 2010
    Posts
    16
    I would try the following:

    Code:
    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
    LEFT OUTER
      JOIN ( SELECT CTill as till, date( CDate ) as costdate, sum( CAmount ) as cost
               FROM costs
              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 14:06. Reason: forgot the group by

Tags for this Thread

Posting Permissions

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