Where clause without sub query

Unsold items have a cms.odr_date that is null?  If so, you should just be able to modify your where to look something like:

where stock.number like '1SX%' and cms.paymethod<>'IN' and Where datediff (d, cms.odr_date, getdate ()) <= 210 AND cms.odr_date is not null

There would be no data in the items table or the items table for unsold items. Just in the stock table.

Try out this tuned query:

Select  stock.number, sum(items.quants ) Sold, sum(puritem.delivered) Recevied
from stock
left join items on stock.number=items.item
left join puritem on stock.number=puritem.number
left join cms on items.orderno=cms.orderno              
where stock.number like '1SX%' and cms.paymethod<>'IN'
and cms.odr_date <= dateadd (d, -210, getdate ())
group by stock.number
order by stock.number

If you didn't obtain any records, then kindly make sure you have records satisfying this scenario or not..

no issue with 2nd query but try this:

Select stock.number, sum(items.quants ) Sold, sum(puritem.delivered) Recevied
  from stock
  left join items on stock.number=items.item
  left join puritem on stock.number=puritem.number
  left join cms on items.orderno=cms.orderno               
 where stock.number like '1SX%' 
   and cms.paymethod <> 'IN' 
   and datediff (day, cms.odr_date, getdate ()) < 211
 group by stock.number
 order by stock.number

Select allOpen in new window

can you please post a few data from 1st query...

looking through the suggestions, i don't see what i would do, so, try using an isnull function in the datediff.  if one of the arguments of the datediff is null, it will return null.  the following substitutes today for the odr_date, causing it to pass:

datediff (d, isnull(cms.odr_date, getdate()), getdate ()) <= 210

number                              sold               Recevied
1SXMBN0001OS-BLACK        2.00      6.00
1SXMBN0001OS-CYAN         3.00      12.00
1SXMGL0001LG-BLACK        2.00      6.00
1SXMGL0001MD-BLACK        2.00      4.00
1SXMGL0001XL-BLACK        1.00      2.00
1SXMJK0001MD-BLACK        .00      1.00
1SXMJK0002XL-INDIGO       1.00      1.00
1SXMJK0004LG-WHITE        1.00      2.00
1SXMJK0004MD-WHITE        1.00      10.00
1SXMJK0004SM-WHITE        .00      NULL
1SXMPN0001MD-BLACK        1.00      2.00
1SXMPN0003LG-BLACK        1.00      3.00
1SXMPN0004MD-BLACK        1.00      2.00
1SXMSH0001MD-CYAN         3.00      9.00
1SXMSH0001SM-CYAN         1.00      2.00
1SXMSH0002XL-GUNMETA      1.00      2.00
1SXMSH0003MD-GUNMETA      3.00      6.00
1SXMSH0004MD-CYAN         .00      NULL
1SXMTT0001LG-GREY         1.00      4.00
1SXMTT0001MD-GREY         1.00      3.00
1SXMTT0001XL-GREY         .00      3.00
1SXWGL0001MD-BLACK        1.00      4.00
1SXWJK0001MD-BLACK        2.00      4.00
1SXWPN0001SM-BLACK        .00      2.00
1SXWPN0002LG-ORCHID       1.00      1.00
1SXWPN0002MD-ORCHID       2.00      2.00
1SXYJK0001XL-KELLY        2.00      4.00

I apolgize the correct date I would need to be looking at is items.sl_date

Select  stock.number, sum(items.quants ) Sold, sum(puritem.delivered) Recevied

from stock

left join items on stock.number=items.item
left join puritem on stock.number=puritem.number
left join cms on items.orderno=cms.orderno              

where stock.number like '1SX%' and cms.paymethod<>'IN' and datediff (d, items.it_sdate, getdate ()) <= 210

group by stock.number

order by stock.number

Via Tom gets it. I love this place thanks all!

i believe my same comment applies for items.it_sdate.  

Sorry I need to revisit this again. I am having the same problem with the cms.paymethod<>'IN'  I need all results returned from stock and if the there is no data in the items table then it just needs to be null.

Select 
LEFT(stock.number,6) AS Item,
stock.desc1, 
sum(items.quants ) Sold, 
sum(puritem.delivered) Recevied

from stock

left join items on stock.number=items.item
left join puritem on stock.number=puritem.number
left join cms on items.orderno=cms.orderno               

where stock.number like '1SX%' --and cms.paymethod<>'IN' and datediff (d, isnull(items.it_sdate, getdate()), getdate ()) <= 211-- 

group by stock.number, stock.desc1

order by stock.number

select * from stock where number like '1SX%'

Select allOpen in new window

i think what you are asking is:  if the paymethod is null, it needs to include the record.  if so, try this...

and isnull(cms.paymethod, ' ', cms.paymethod) <> 'IN'

for records that have a null paymethod, this will substitue a non-matching value, making the not-equal comparison true.

For many records in the stock table there are no CMS.PAYMETHOD as they have not sold. Does null work for records that do not exists?

My Goal is to keep the exact same amount of records in the above qurey as this produces.

select * from stock where number like '1SX%'


When I tried the code this error came up:

Server: Msg 174, Level 15, State 1, Line 13
The isnull function requires 2 arguments.

oops, sorry, got my languages confused...  this should do it:

and isnull(cms.paymethod, ' ') <> 'IN'

It returned reords, It just dosen't match the record set of querying the stock table. Any more suggestions?

Select 
stock.number,
sum(items.quants ) Sold, 
sum(puritem.delivered) Recevied

from stock

left join items on stock.number=items.item
left join puritem on stock.number=puritem.number
left join cms on items.orderno=cms.orderno               

where stock.number like '1SX%' and  isnull(cms.paymethod, ' ') <> 'IN'  and datediff (d, isnull(items.it_sdate, getdate()), getdate ()) <= 211 

group by stock.number

order by stock.number

--50 records--

select stock.number from stock where number like '1SX%'

--102 records--

Select allOpen in new window

This code returns the exact results I need. when i add in

and isnull(cms.paymethod, ' ') <> 'IN'

it cuts the records down

Select 
stock.number,
sum(items.quants ) Sold, 
sum(puritem.delivered) Recevied

from stock

left join items on stock.number=items.item
left join puritem on stock.number=puritem.number
left join cms on items.orderno=cms.orderno               

where stock.number like '1SX%' and  datediff (d, isnull(items.it_sdate, getdate()), getdate ()) <= 211 

group by stock.number

order by stock.number

Select allOpen in new window

instead of this

datediff (d, isnull(items.it_sdate, getdate()), getdate ()) <= 211
use this
((items.it_sdate is null ) or (items.it_sdate < dateadd (day, -211, getdate()))