SQL Aggregation Issue 1

[barnettjacob]

Guys, hoping someone can help with an issue I'm having.

The following query used to work like a dream to return sales for a specific store between specified dates grouped by year and week.

I have made a change to enable me to screen out transactions using a particular voucher code by joining to the voucher table on the transaction reference.

The problem is that where two vouchers were used on a single transaction I get the total sales returned for the transaction for each voucher number, effectively double counting my sales. I am told this is becuase the voucher is at the transaction and not the line level.

Anybody got any ideas?

Regards
Jacob




Set datefirst 1
Select
s.reference1,
datepart(YEAR,s.saledate) as year,
datepart(wk,s.saledate) as week,
s.branchcode,
sum(coalesce(s.fcextendednetamount-s.fcextendedtaxamount,0)) as salesdollars,
sum(coalesce(s.quantity,0)) as saleunits,
sum(coalesce(s.grossprofitalternateGP,0)) as GPdollars,
sum(coalesce(s.fcextendednetamount-s.fcextendedtaxamount-s.grossprofitalternateGP,0)) as salescost


FROM EEE..vwsaleline_gp s
left join eee..vouchertransaction v on v.reference1 = s.reference1

where s.saledate >= '08/15/2011' and s.saledate< '08/22/2011' and s.branchcode = 'mctp' and (v.vouchernumber <> ('55555') or v.vouchernumber is null)


group by datepart(year,s.saledate),datepart(wk,s.saledate),s.branchcode,s.reference1,v.vouchernumber
order by datepart(year,s.saledate),datepart(wk,s.saledate),s.branchcode,s.reference1,v.vouchernumber

 

[r937]

could you explain how the voucher code screens out the transcations?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[barnettjacob]

Yep, sorry.

Reference1 is the transaction code that exists in both the vwsaleline and vouchertransaction tables.

If the vouchercode is specified in the 'where' part of the query, I want transactions with that vouchercode to be omitted.

Thanks
Jacob

 

[r937]

change this --

FROM  EEE..vwsaleline_gp s
left join eee..vouchertransaction v 
on v.reference1 = s.reference1
where s.saledate >= '08/15/2011' and s.saledate< '08/22/2011' 
and s.branchcode = 'mctp' 
and (v.vouchernumber <> ('55555') or v.vouchernumber is null)

to this --

  FROM EEE..vwsaleline_gp s
 WHERE s.saledate >= '08/15/2011' AND s.saledate < '08/22/2011' 
   AND s.branchcode = 'mctp' 
   AND NOT EXISTS
       ( SELECT 'oops'
           FROM eee..vouchertransaction 
          WHERE reference1 = s.reference1
            AND vouchernumber = '55555' )

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[barnettjacob]

Thats done it!!! Thanks a million!!!