?
Solved

Where clause without sub query

Posted on 2010-01-11
18
Medium Priority
?
160 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Quick Start: DOCKER

Sometimes you just need a Quick Start on a topic in order to begin using it.. this is just what you need to know to get up and running with Docker!

 
LVL 57

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 57

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 57

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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month13 days, 7 hours left to enroll

800 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