Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Where clause without sub query

Posted on 2010-01-11
18
Medium Priority
?
162 Views
Last Modified: 2012-05-08
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
0
Comment
Question by:ubsmail
  • 8
  • 5
  • 3
  • +2
18 Comments
 
LVL 10

Expert Comment

by:laneduncan
ID: 26288454
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
 

Author Comment

by:ubsmail
ID: 26288469
There would be no data in the items table or the items table for unsold items. Just in the stock table.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26288479
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 61

Expert Comment

by:HainKurt
ID: 26288519
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 26288537
can you please post a few data from 1st query...
0
 
LVL 8

Expert Comment

by:ViaTom
ID: 26288603
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
 

Author Comment

by:ubsmail
ID: 26288609
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
 

Author Comment

by:ubsmail
ID: 26288632
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
 

Author Comment

by:ubsmail
ID: 26288662
Via Tom gets it. I love this place thanks all!
0
 
LVL 8

Expert Comment

by:ViaTom
ID: 26288668
i believe my same comment applies for items.it_sdate.  
0
 

Author Comment

by:ubsmail
ID: 26288804
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
 
LVL 8

Expert Comment

by:ViaTom
ID: 26289067
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
 

Author Comment

by:ubsmail
ID: 26289103
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
 
LVL 8

Expert Comment

by:ViaTom
ID: 26289133
oops, sorry, got my languages confused...  this should do it:

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

Author Comment

by:ubsmail
ID: 26289178
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
 

Author Comment

by:ubsmail
ID: 26289222
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 26289829
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
 
LVL 8

Accepted Solution

by:
ViaTom earned 2000 total points
ID: 26293762
HainKurt, that is just a different way of saying the exact same thing.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question