extracting specific criteria - 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 2000 Forums

Transact-SQL (2000)

extracting specific criteria

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-01-25 : 12:41:21

Hi there guys I have been trying to get my head round a t-sql statement that has got me a bit confused and wonder if anyone would be able to give me some advise please.

My SQL is as below :

SELECT distinct dbo.MEMBERSUM.MEMBERNUM, dbo.DEPARTMENTS.DESCRIPTION
FROM dbo.TRANS INNER JOIN

dbo.SALEPOINTS ON dbo.TRANS.TRANKEY = dbo.SALEPOINTS.TRANKEY INNER JOIN

dbo.MEMBERSUM ON dbo.SALEPOINTS.MEMBERKEY = dbo.MEMBERSUM.MEMBERKEY INNER JOIN

dbo.SALELINE ON dbo.TRANS.TRANKEY = dbo.SALELINE.TRANKEY INNER JOIN

dbo.DEPARTMENTS ON dbo.SALELINE.DEPTKEY = dbo.DEPARTMENTS.DEPTKEY

WHERE (DATEADD(day, dbo.TRANS.TRANDATE - '38024', CONVERT(DATETIME, '2004-02-05 00:00:00', 102)) BETWEEN CONVERT(DATETIME,

'2005-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2006-01-25 00:00:00', 102))
order by membernum asc

and my resultset is something like this :

0000040255811 BREAD
0000040255811 BUTCHERY
0000040255811 CHILLED
0000040255811 FROZEN FOODS
0000040255811 FRUIT/VEG
0000040255811 GROCERY
0000040255811 SOFT DRINKS
0000040255811 TOILETRIES
0000050219209 CIGS/TOBACCO
0000050219209 NEWSPAPER/MAGAZINES
0000050219209 WINES & SPIRITS
0000050219834 CIGS/TOBACCO
0000050219834 CONFECTIONERY
0000050219834 GROCERY
0000050219834 NEWSPAPER/MAGAZINES
0000050219834 SOFT DRINKS
0000050415939 CHILLED
0000050415939 FRUIT/VEG
0000050415939 SOFT DRINKS

Its resultset returns a description of a members transaction from begininning of 2005 to yesterday.

Now what i require is a list of all membernumbers that have conducted transactions but have no description called fuel in the description field.

ie : they can have chilled, soft drinks and houshold items in the description field for a individual membernum but if they don't have fuel I want the resultset to show this and if the membernum has grocery ,wine and fuel then i don't want this membernum to be included.

Any ideas how I can achieve this in the above query or will I have to do this seperatly ???

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-01-25 : 12:44:04

and (description not like '%Fuel%' or description is null)

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-01-26 : 04:30:22

Thanks RickD but All this does is limit fuel from the resultset it does not give me a indication of a membernumber having other descriptions and not fuel ie grocery, soft drink and toiletries without the fuel description which is what I am trying to achieve.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-26 : 04:34:37

It might be easier if you can post your tables DDL, some sample data and expected result

----------------------------------
'KH'

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-01-26 : 04:47:45

sorry KHtan this is going to be impossible due to the shear amount of fields within these tables . They are not the most normalised as they are specifically for reporting purposes.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-01-26 : 05:00:11

Let me try and explain clearer as I don't think this is rocket science by any means....
at the moment the query is bringing back a resultset like this :

0102242 CONFECTIONERY
0102242 FUEL
011610 WINES & SPIRITS
013607 CHILLED
013607 CIGS/TOBACCO
013607 FRESH BAKERY
013607 GROCERY

now because 0102242 membernum has got fuel in there description I want this membernum to be limited from the resultset but the one after 011610 has got no fuel in description so this needs to be included in the resultset same thing for 013607 that has got no fuel in there description needs to be included.

Catch my drift or is this asking too much from one query ?

szgldt
Starting Member

10 Posts

Posted - 2006-01-26 : 05:39:07

Try adding something like this to your WHERE clause

and MEMBERSUM.MEMBERNUM Not IN

(SELECT distinct dbo.MEMBERSUM.MEMBERNUM
FROM dbo.TRANS
INNER JOIN dbo.SALEPOINTS
ON dbo.TRANS.TRANKEY = dbo.SALEPOINTS.TRANKEY
INNER JOIN dbo.MEMBERSUM
ON dbo.SALEPOINTS.MEMBERKEY = dbo.MEMBERSUM.MEMBERKEY
INNER JOIN dbo.SALELINE
ON dbo.TRANS.TRANKEY = dbo.SALELINE.TRANKEY
INNER JOIN dbo.DEPARTMENTS
ON dbo.SALELINE.DEPTKEY = dbo.DEPARTMENTS.DEPTKEY
WHERE (DATEADD(day, dbo.TRANS.TRANDATE - '38024', CONVERTDATETIME, '2004-02-05 00:00:00', 102)) BETWEEN CONVERT DATETIME, '2005-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2006-01-25 00:00:00', 102)) AND (description like '%Fuel%') )

Its difficult without the database schema but hopefully this should work.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-01-26 : 05:51:52

thank you will give this a try ..

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-01-26 : 06:56:21

Ah yes this is what I am looking for I always underestimate the power of a good subquery.
Thank you for the help szgldt much appreciatted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources