Where clause without sub query

I am trying to create a sales report the includes sales data from a particular timeframe in the items table, however I want to show all items in the stock table. when I run query 1 it returns 27 rows. When I try and add a specific date range to the where clause (Query 2) it no longer includeds items which have not sold. I would like to keep this information trying not to use sub queries.

--Query 1--
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'

group by stock.number

order by stock.number


--Query 2--

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 Where datediff (d, cms.odr_date, getdate ()) <= 210

group by stock.number

order by stock.number
ubsmailAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

laneduncanCommented:
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
0
ubsmailAuthor Commented:
There would be no data in the items table or the items table for unsold items. Just in the stock table.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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..
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

HainKurtSr. System AnalystCommented:
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

Open in new window

0
HainKurtSr. System AnalystCommented:
can you please post a few data from 1st query...
0
ViaTomCommented:
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
0
ubsmailAuthor Commented:
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
0
ubsmailAuthor Commented:
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
0
ubsmailAuthor Commented:
Via Tom gets it. I love this place thanks all!
0
ViaTomCommented:
i believe my same comment applies for items.it_sdate.  
0
ubsmailAuthor Commented:
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%'

Open in new window

0
ViaTomCommented:
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.
0
ubsmailAuthor Commented:
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.
0
ViaTomCommented:
oops, sorry, got my languages confused...  this should do it:

and isnull(cms.paymethod, ' ') <> 'IN'
0
ubsmailAuthor Commented:
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--

Open in new window

0
ubsmailAuthor Commented:
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

Open in new window

0
HainKurtSr. System AnalystCommented:
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()))
0
ViaTomCommented:
HainKurt, that is just a different way of saying the exact same thing.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.