Selecting within a subquery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting within a subquery

Author

Topic

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2008-02-12 : 18:02:53

I'm not completely new to SQL, but I have run into an issue that has me stumped. I have a list of vendors that contain orders from a given time period (say.. 3 months) and I also have a list of vendors for a 1-month time period. What I need to do is show the entire list of vendors for the 3-month time period but with only the orders for the 1-month time period so that the vendors with no orders still show with zero orders. Here is my query for the list of vendors for the 3-months:

SELECT DISTINCT New_VendorName
FROM New_Vendor
LEFT JOIN Orders ON Orders.VendorId = New_Vendor.New_VendorId
WHERE Orders.CreateDate >= ('6/1/07') AND Orders.CreateDate < ('9/30/07')
ORDER BY New_VendorName

The results of the first query is just a list of vendors that had orders in the time period:

A
B
C
D
E
G
I

etc.

My second query pulls the vendors from a 1-month time period and counts the orders for each vendor within that time period:

SELECT New_VendorName, COUNT(Orders.OrderId) AS 'Orders'
FROM New_Vendor
LEFT JOIN Orders ON Orders.VendorId = New_Vendor.New_VendorId
WHERE Orders.CreateDate >= ('9/1/07') AND Orders.CreateDate < ('9/30/07')
GROUP BY New_VendorName
ORDER BY New_VendorName

The results of the second query could be as follows:

A 4
B 8
D 10
I 13

What I'd like to do is somehow show all of the vendors for the 3-month period with only the orders from the 1-month period:

A 4
B 8
C 0
D 10
E 0
G 0
I 13

Note: I don't want to show the entire list of vendors from A to I, just the ones that had orders in the 3-month period but showing orders only from the 1-month period. Also, the 1-month time period will always be within the 3-month time period, if that matters.

Is this possible in SQL? I've been struggling with this problem for a few days now and would really appreciate any help. Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 18:05:59

[code]SELECT New_VendorName,
COUNT(Orders.OrderId) AS Orders
FROM New_Vendor
LEFT JOIN Orders ON Orders.VendorId = New_Vendor.New_VendorId
AND Orders.CreateDate >= '20070901'
AND Orders.CreateDate < '20071001'
GROUP BY New_VendorName
ORDER BY New_VendorName[/code]

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-12 : 18:08:29

try:


SELECT New_VendorName,
  sum(case when Orders.CreateDate >= '9/1/07' and Orders.CreateDate < '9/30/07' then 1 else 0 end) as LastMonthOrderCount
FROM New_Vendor
INNER JOIN Orders ON Orders.VendorId = New_Vendor.New_VendorId
WHERE Orders.CreateDate >= ('6/1/07') AND Orders.CreateDate < ('9/30/07')
GROUP BY New_VendorName
ORDER BY New_VendorName

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-12 : 18:10:20

by the way -- you know that your query excludes all orders made on 9/30/07, right? Are you sure you don't want <'10/1/2007' ?

- Jeff
http://weblogs.sqlteam.com/JeffS

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2008-02-12 : 18:19:35

quote:

Originally posted by jsmith8858

try:


SELECT New_VendorName,
  sum(case when Orders.CreateDate >= '9/1/07' and Orders.CreateDate < '9/30/07' then 1 else 0 end) as LastMonthOrderCount
FROM New_Vendor
INNER JOIN Orders ON Orders.VendorId = New_Vendor.New_VendorId
WHERE Orders.CreateDate >= ('6/1/07') AND Orders.CreateDate < ('9/30/07')
GROUP BY New_VendorName
ORDER BY New_VendorName

- Jeff
http://weblogs.sqlteam.com/JeffS

Thank you SO much, this works perfectly!

Thank you all for your prompt replies.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources