Query Aggregation Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query Aggregation Issue

Author

Topic

barnettjacob
Starting Member

9 Posts

Posted - 2011-08-23 : 04:19:54

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 04:24:05

[code]
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 (select reference1,
sum(case when v.vouchernumber = '55555' then 1 else 0 end) as vcnt
from eee..vouchertransaction
group by reference1)v
on v.reference1 = s.reference1
where s.saledate >= '08/15/2011'
and s.saledate< '08/22/2011'
and s.branchcode = 'mctp'
and (v.vcnt=0 or v.vouchernumber is null)

group by datepart(year,s.saledate),datepart(wk,s.saledate),s.branchcode,s.reference1
order by datepart(year,s.saledate),datepart(wk,s.saledate),s.branchcode,s.reference1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

barnettjacob
Starting Member

9 Posts

Posted - 2011-08-23 : 06:08:25

Thanks, makes sense and I'm sure you're on the right lines but I get the following error when I run:

Server: Msg 207, Level 16, State 1, Line 22
Invalid column name 'vouchernumber'.
Server: Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "v.vouchernumber" could not be bound.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-23 : 06:20:08

I think you need to change the following:

quote:

Originally posted by visakh16


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 (select reference1,
sum(case when v.vouchernumber = '55555'  then 1 else 0 end) as vcnt
from eee..vouchertransaction 
group by reference1)v 
on v.reference1 = s.reference1
where s.saledate >= '08/15/2011' 
and s.saledate< '08/22/2011' 
and s.branchcode = 'mctp' 
and (v.vcnt=0 or v.vouchernumbervcnt is null)

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Corey

I Has Returned!!

barnettjacob
Starting Member

9 Posts

Posted - 2011-08-23 : 06:29:04

ok.

Also changed:
sum(case when v.vouchernumber = '55555' then 1 else 0 end) as vcnt

To:
sum(case when vouchernumber = '55555' then 1 else 0 end) as vcnt

And it worked a treat.

Thanks Guys

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 07:18:28

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources