Help with query

Question

Post reply

Help with query

LeeFAR

SSCarpal Tunnel

Points: 4644
More actions
February 9, 2007 at 4:07 pm

#118924

I have tried to figure this out with no luck.
Here is the deal.
I have a table of possible week begin dates; tblPossibleDates; PossibleDate
I have a table of Customer Orders; tblOrders. This table has the fields WeekDate, CustomerID, and OrderQty. Assume the tblOrders.WeekDate is the week begin date of when the order was placed.
What I need is a query that will return a resultset that looks like the following.
PossibleDate                    CustomerID           WeekDate       OrderQty
2006-02-26                     1                         2006-02-26     100
2006-03-05                     1                         2006-03-05     25
2006-03-12                     1                         Null                Null
2006-03-19                     1                         2006-03-19     33
As you can tell my example resultset, the PossibleDate of 2006-03-12, there would not be a record in tblOrders. But I still need to return a CustomerID so that I know CustomerID 1 does not have an order for the PossibleDate.
If you have any ideas how to do this, I would appreciate it!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Try something like this using a derived table:

select

dt.PossibleDate,

dt.CustomerId,

or.WeekDate,

or.OrderQty

from

(select

pd.PossibleDate,

o.CustomerId

from

dbo.tblPossibleDates pd

cross join dbo.tblOrders o) dt

left outer join dbo.tblOrders or

on (dt.PossibleDate = or.Weekdate

and dt.CustomerId = or.CustomerId)

LeeFAR SSCarpal Tunnel Points: 4644 More actions · Answer 2

LeeFAR

SSCarpal Tunnel

Points: 4644

February 9, 2007 at 5:16 pm

#688997

Lynn, you rock! Thank you so much!

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Lee,

Lynn is absolutely spot on... the only thing is that the necessary cross join will spawn a huge number of interim rows even if you add a DISTINCT to the outer query... make the small change to her query I've highlighted below and it'll continue to work great with much larger record sets...

--===== Create some test data...

CREATE TABLE #tblPossibleDates (PossibleDate DATETIME)

INSERT INTO #tblPossibleDates (PossibleDate)

SELECT '2006-02-26' UNION ALL

SELECT '2006-03-05' UNION ALL

SELECT '2006-03-12' UNION ALL

SELECT '2006-03-19'

CREATE TABLE #tblOrders (CustomerID INT, WeekDate DATETIME, OrderQty INT)

INSERT INTO #tblOrders (CustomerID, WeekDate, OrderQty)

SELECT 1,'2006-02-26',100 UNION ALL

SELECT 1,'2006-03-05', 25 UNION ALL

SELECT 1,'2006-03-19', 33 UNION ALL

SELECT 2,'2006-02-26',100 UNION ALL

SELECT 2,'2006-03-05', 25 UNION ALL

SELECT 3,'2006-03-19', 33 UNION ALL

SELECT 4,'2006-02-26',100 UNION ALL

SELECT 4,'2006-03-12', 25 UNION ALL

SELECT 4,'2006-03-19', 33

SELECT dt.PossibleDate,

dt.CustomerId,

[or].WeekDate,

[or].OrderQty

FROM

(

SELECT pd.PossibleDate,

o.CustomerId

FROM #tblPossibleDates pd

CROSS JOIN

(SELECT DISTINCT CustomerId FROM #tblOrders) o

) dt

LEFT OUTER JOIN

#tblOrders [or]

ON dt.PossibleDate = [or].Weekdate

AND dt.CustomerId = [or].CustomerId

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LeeFAR SSCarpal Tunnel Points: 4644 More actions · Answer 4

Jeff,

Thanks for your reply. I noticed this too and put a distinct where you mentioned. Thanks again for the replies folks!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Actually, not having put any test code together, I didn't think of making sure about only selecting disinct Customer Id's. I just looked at what it would take to create the desired output based on the single customer id in the sample. Good catch!