Using 'not in' - 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

General SQL Server Forums

New to SQL Server Programming

Using 'not in'

Author

Topic

velvettiger
Posting Yak Master

115 Posts

Posted - 2008-06-21 : 11:09:07

Hi Guys,

I am trying to do the following

Find customers who has not shopped within the last twelve months at a supermarket called SC however has shopped at other
supermarkets within that same time period.

Note SC storeids are (3001,3004,3003,3002,3025,3026,3032,3031,3050,3028,3030,3033,3005,3023,3022,3010,3013,3012,3011,3006,3016,3017,3018,3019,3021,10945,3080,3007,3008,3009,3060)

Below is my solution


select count(distinct c.cardnumber)Customers
	,month(TransactionDate)Month
	,year(TransactionDate)Year
from customer c inner join SubsidiaryByCustomerByClassTransactionDetail s on c.cardnumber=s.cardnumber
where TransactionDate >= { ts '2007-09-01 00:00:00.000' } and TransactionDate < { ts '2008-06-01 00:00:00.000' }
	and subsidiaryid not in (3001,3004,3003,3002,3025,3026,3032,3031,3050,3028,3030,3033,3005,3023,3022,3010,3013,3012,3011,3006,3016,3017,3018,3019,3021,10945,3080,3007,3008,3009,3060)
group by month(TransactionDate)
	,year(TransactionDate)

when i did a check on the above query i realised that customersthat shopped at SC are still coming up but that should not be so. It is suppose to bring up person who did not make any transaction at the SC store.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-21 : 11:17:30

NEVER MIX T-SQL and ODBC calls!

1) Store this SubsidiaryID in a separate table and do a NOT EXISTS or a LEFT JOIN instead
2) IN is a little tricky beacuse of column/table relationship.

select		count(distinct c.cardnumber) as Customers,
		month({missing table alias}.TransactionDate)as Month,
		year({missing table alias}.TransactionDate) as Year
from		customer as c
inner join	SubsidiaryByCustomerByClassTransactionDetail as s on c.cardnumber = s.cardnumber
left join	Subsidiary AS ss ON ss.ID = s.subsidiaryid
where		{missing table alias}.TransactionDate >= '20070901'
		and {missing table alias}.TransactionDate < '20080601'
		and ss.subsidiaryid is null
group by	month({missing table alias}.TransactionDate),
		year({missing table alias}.TransactionDate)

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-21 : 11:47:54

[code]select count(distinct c.cardnumber) AS Customers
,tranmonth
,tranyear
from customer c
inner join
(select cardnumber,month(TransactionDate) as tranmonth,year(TransactionDate) as tranyear
From SubsidiaryByCustomerByClassTransactionDetail
where TransactionDate >= '2007-09-01 00:00:00.000'
and TransactionDate < '2008-06-01 00:00:00.000'
GROUP BY cardnumber,month(TransactionDate),year(TransactionDate)
having sum(case when subsidiaryid in (3001,3004,3003,3002,3025,3026,3032,3031,3050,3028,3030,3033,3005,3023,3022,3010,3013,3012,3011,3006,3016,3017,3018,3019,3021,10945,3080,3007,3008,3009,3060)
then 1 else 0 end)=0)s
on c.cardnumber=s.cardnumber
group by tranmonth,tranyear
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources