Another query - 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

Another query

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-13 : 13:41:47

How do I write this query to exclude the drycde <> records.

select b.area,count(b.pan) as Tot 
from alldiaries a join ssidiaries b
on a.hun=b.hun
where drycde <> 1B,1Z,22,23,2B,2C,2E,2F,2G,2H,2J,2K,2L,2M,2N,2P,2U,
2W,2X,2Y,3D,3L,3W,4B,4W,5B,5F,5G,5H,5W,6W,7C,7D,7E,7M,7W,
8W,9W,A1,A2,A3,A4,A9,AA,AI,AR,AT,B1,B2,B3,B4,B5,B6,B8,B9,
BZ,C3,CB,CC,CL,CM,CP,CR,CZ,D1,D2,D3,D6,D8,DA,DI,DL,DU,E4,
EA,F1,F3,F4,F5,F7,FB,FC,FD,FE,FM,FP,FQ,G4,G7,GQ,I1,I8,IN,
IR,J2,J3,JR,K1,K3,K5,K6,K7,K8,K9,KB,KE,KP,KZ,L4,L9,M1,M4,
M5,MD,ME,MN,MP,MR,N3,N7,N8,N9,NM,OP,P1,P2,P3,P5,PD,PP,Q1,
Q3,Q6,Q7,R2,R3,R4,R5,R8,R9,RB,RD,RE,RI,RT,S2,S5,S6,S7,S9,
SB,SZ,T9,TZ,U1,U2,U3,U4,U5,UA,US,V6,V7,V9,VA,VP,VR,Z1,Z2,
Z3,Z7,ZA,ZB,ZI,ZQ and 
a.drycde ='5y'and a.drydat < DATEADD(dd,DATEDIFF(dd,0,GETDATE())-270,0)
and a.curstat not like 'T%'
group by area
order by area

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 13:43:25

use NOT IN

drycde NOT IN ( '1B','1Z','22',...
'Z3','Z7','ZA','ZB','ZI','ZQ' )

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

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-13 : 13:51:28

Thanks! I had to go through and add all of the ticks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 13:52:44

ticks? sorry didnt get that

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

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-13 : 14:01:54

I had (drycd1 NOT IN (1B,1Z...

So I had to add the ticks

(drycd1 NOT IN ('1B','1Z' ...)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 14:06:10

oh ok

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources