Error In Code?.......Should I use Group By

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

General SQL Server Forums

New to SQL Server Programming

Error In Code?.......Should I use Group By

Author

Topic

velvettiger
Posting Yak Master

115 Posts

Posted - 2009-01-22 : 20:59:13

Hi there,

I have problem with this query. I am trying to find all those customers who
1)Shopped in 2003 only and did not shop in 2004,2005,2006
2)Shopped in 2004 only and did not shop in 2005,2006
3)Shopped in 2005 only and did not shop in 2006
4)Shopped in 2006 only
For the above you want to see the total customers in each group and then the total points

So the first thing I did was to find all those customers who shopped in 2003-2006 because the above totals should add back to these values See the code below


MY CHECK

select count(distinct cardnumber), sum(ransactionpoints)
from transaction c 
where (transactiondate >= '20030101' and  transactiondate < '20070101')

And lets say the total spend for the period is 1,000 and the total points 500.

When I run the query for each of the criteria mention above, and I add the total spend for each month and I add the total points I get a break down down like this. So it doesn't come back out to the CHECK

Just like my query the Total Customer column is correct but the total points is not correct.


        Total Customers       Total Points
2003        200                     10
2004        500                     30
2005        100                     40
2006        150                      5
2007         50                      1
           _________              _______
           1000                    86

Please see below for the quries


select distinct c.cardnumber,sum(dailytransactionvalue)
from SubsidiaryByCustomerByClassTransactionDetail c 
where c.cardnumber in (
                      select distinct cardnumber
                      from ubsidiaryByCustomerByClassTransactionDetail
                      where (transactiondate >= '20030101' and  transactiondate < '20040101')

            and cardnumber 
                      not in (
                               select distinct cardnumber
               from SubsidiaryByCustomerByClassTransactionDetail
                   where (transactiondate >= '20040101' and transactiondate < '20080101')
          )
      )


select distinct c.cardnumber,sum(dailytransactionvalue)
from SubsidiaryByCustomerByClassTransactionDetail c 
where c.cardnumber in (
                      select distinct cardnumber
                      from ubsidiaryByCustomerByClassTransactionDetail
                      where (transactiondate >= '20040101' and  transactiondate < '20050101')

and cardnumber 
not in (
     select distinct cardnumber
    from SubsidiaryByCustomerByClassTransactionDetail
                   where (transactiondate >= '20050101' and transactiondate < '20080101')
          )
      )


select distinct c.cardnumber,sum(dailytransactionvalue)
from SubsidiaryByCustomerByClassTransactionDetail c 
where c.cardnumber in (
                      select distinct cardnumber
                      from ubsidiaryByCustomerByClassTransactionDetail
                      where (transactiondate >= '20050101' and  transactiondate < '20060101')

and cardnumber 
not in (
     select distinct cardnumber
    from SubsidiaryByCustomerByClassTransactionDetail
                   where (transactiondate >= '20060101' and transactiondate < '20080101')
          )
      )


select distinct c.cardnumber,sum(dailytransactionvalue)
from SubsidiaryByCustomerByClassTransactionDetail c 
where c.cardnumber in (
                      select distinct cardnumber
                      from ubsidiaryByCustomerByClassTransactionDetail
                      where (transactiondate >= '20060101' and  transactiondate < '20070101')

and cardnumber 
not in (
     select distinct cardnumber
    from SubsidiaryByCustomerByClassTransactionDetail
                   where (transactiondate >= '20070101' and transactiondate < '20080101')
          )
      )


select distinct c.cardnumber,sum(dailytransactionvalue)
from SubsidiaryByCustomerByClassTransactionDetail c 
where (transactiondate >= '20070101' and  transactiondate < '20080101')

Any Ideas? I was wonder if i was to do a group by year(transactiondate) would that give me customers who shopped only in 2003 ,only in 2004 ,only in 2005 etc and their spend??

e.g 5 customers shopped in 2004 and did not shop in 2005,2006,or 2007
and they spent $20 combines
I am not sure if this will show the info that I really want.Thanks for looking at my question

so something like this


select distinct c.cardnumber, sum(dailytransactionvalue), year(transactiondate)
from SubsidiaryByCustomerByClassTransactionDetail c 
where (transactiondate >= '20030101' and  transactiondate < '20080101')
group by year(transactiondate)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 23:11:31

[code]
this will give you people shopped in 2003 but not in next three years
select count(distinct cardnumber), sum(ransactionpoints)
from transaction c
where (transactiondate >= '20030101' and transactiondate < '20070101')
having sum(case when year(transactiondate) in (2004,2005,2006) then 1 else 0 end)=0

this will give you customers shopped in 2004 only

select count(distinct cardnumber), sum(ransactionpoints)
from transaction c
where (transactiondate >= '20040101' and transactiondate < '20070101')
having sum(case when year(transactiondate) in (2005,2006) then 1 else 0 end)=0
...

[/code]

velvettiger
Posting Yak Master

115 Posts

Posted - 2009-01-23 : 06:50:52

Are you sure the above is correct? Shouldnt it be

this will give you customers shopped in 2004 only

select count(distinct cardnumber), sum(ransactionpoints)
from transaction c
where (transactiondate >= '20040101' and transactiondate < '20050101')
having sum(case when year(transactiondate) in (2005,2006) then 1 else 0 end)=0
...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 09:31:35

quote:
Originally posted by velvettiger

Are you sure the above is correct? Shouldnt it be

this will give you customers shopped in 2004 only

select count(distinct cardnumber), sum(ransactionpoints)
from transaction c
where (transactiondate >= '20040101' and transactiondate < '20050101')
having sum(case when year(transactiondate) in (2005,2006) then 1 else 0 end)=0
...

nope.mine is correct as you've consider entire period (2003-2007) to understand who hasnt shopped afterwards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources