Query Help

Question

Query Help

SqlSavvy

SSCrazy

Points: 2641
More actions
May 18, 2009 at 10:28 am

#132289

I want only the records that have stage_load_date is today's date from either of the tables involved in join. But its returning the records that don't have stage_load_date as today's date..Can you help me figure out what I am doing wrong here?'
select *
from Table1 a WITH (NOLOCK)
join Table2 b WITH (NOLOCK) on a.nanum = nonum and a.DMACTIVITY <> 'DL' and b.DMACTIVITY <> 'DL' AND LTRIM(RTRIM(NANUM)) = '7203610'
left join Table3 c WITH (NOLOCK) on b.nocgrp = c.ddcgrp and c.DMACTIVITY <> 'DL'
join Table4 d WITH (NOLOCK) on a.nanum = d.cmcuno and d.DMACTIVITY <> 'DL'
where
(CONVERT(VARCHAR(10), a.Stage_LoadDate, 112) = CONVERT(VARCHAR(10), GETDATE(), 112)
OR CONVERT(VARCHAR(10), b.Stage_LoadDate, 112) = CONVERT(VARCHAR(10), GETDATE(), 112)
OR CONVERT(VARCHAR(10), c.Stage_LoadDate, 112) = CONVERT(VARCHAR(10), GETDATE(), 112)
OR CONVERT(VARCHAR(10), d.Stage_LoadDate, 112) = CONVERT(VARCHAR(10), GETDATE(), 112))
AND
DDCDMC in(
'1004', '1092', '1078', '1090', '1152', '1091','TEST', '1010', '1093' )
and CMCSTS in( '1004', '1092', '1078', '1090', '1152', '1091', 'TEST', '1010', '1093' )
or DDCDMC='web' or CMCSTS='web'

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

I think you need another set of parenthesis around the bottom of the WHERE caluse because the last two condtitions are seperate from the AND.

(

DDCDMC in(

'1004', '1092', '1078', '1090', '1152', '1091','TEST', '1010', '1093' )

and CMCSTS in( '1004', '1092', '1078', '1090', '1152', '1091', 'TEST', '1010', '1093' )

or DDCDMC='web' or CMCSTS='web'

)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

(CONVERT(VARCHAR(10), a.Stage_LoadDate, 112) = CONVERT(VARCHAR(10), GETDATE(), 112)
OR CONVERT(VARCHAR(10), b.Stage_LoadDate, 112) = CONVERT(VARCHAR(10), GETDATE(), 112)
OR CONVERT(VARCHAR(10), c.Stage_LoadDate, 112) = CONVERT(VARCHAR(10), GETDATE(), 112)
OR CONVERT(VARCHAR(10), d.Stage_LoadDate, 112) = CONVERT(VARCHAR(10), GETDATE(), 112))

Try changing the OR's above to AND's. But then again, I may be wrong. I may have miss counted the paren's myself.

SqlSavvy SSCrazy Points: 2641 More actions · Answer 3

SqlSavvy

SSCrazy

Points: 2641

May 18, 2009 at 10:41 am

#996498

Thanks a lot guys...Adding parentheses worked

FelixG SSCrazy Points: 2550 More actions · Answer 4

Your problem resides in the WHERE clause where you have 4 OR operator. If one of the 4 conditions becomes true, then the WHERE will be true too.

This should work:

select *

from Table1 a WITH (NOLOCK)

join Table2 b WITH (NOLOCK) on a.nanum = nonum and a.DMACTIVITY 'DL' and b.DMACTIVITY 'DL' AND LTRIM(RTRIM(NANUM)) = '7203610'

and (CONVERT(VARCHAR(10), a.Stage_LoadDate, 112) = CONVERT(VARCHAR(10), GETDATE(), 112)

and CONVERT(VARCHAR(10), b.Stage_LoadDate, 112) = CONVERT(VARCHAR(10), GETDATE(), 112)

join Table4 d WITH (NOLOCK) on a.nanum = d.cmcuno and d.DMACTIVITY 'DL'

and CONVERT(VARCHAR(10), d.Stage_LoadDate, 112) = CONVERT(VARCHAR(10), GETDATE(), 112))

left join Table3 c WITH (NOLOCK) on b.nocgrp = c.ddcgrp and c.DMACTIVITY 'DL'

where

OR CONVERT(VARCHAR(10), c.Stage_LoadDate, 112) = CONVERT(VARCHAR(10), GETDATE(), 112)

DDCDMC in(

'1004', '1092', '1078', '1090', '1152', '1091','TEST', '1010', '1093' )

and CMCSTS in( '1004', '1092', '1078', '1090', '1152', '1091', 'TEST', '1010', '1093' )

or DDCDMC='web' or CMCSTS='web'