Help with query
-
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!
-
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)
-
Lynn, you rock! Thank you so much!
-
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) -
Jeff,
Thanks for your reply. I noticed this too and put a distinct where you mentioned. Thanks again for the replies folks!
-
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!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply