June 11, 2009 at 10:24 pm
How can I avoid a cross join for the following query?
SELECT a.Week, a.StartDate,
CASE WHEN purchasedate BETWEEN a.startdate AND a.nextdate THEN 'Yes' ELSE 'No' END AS [Purchase Done]
FROM dbo.tblPurchase CROSS JOIN
(SELECT number AS Week, DATEADD(wk, number, DATEADD(DD, 1 - DATEPART(DW, '08-Jan-09'), '08-Jan-09')) AS StartDate, DATEADD(wk,
number + 1, DATEADD(DD, 1 - DATEPART(DW, '08-Jan-09'), '08-Jan-09')) AS NextDate
FROM master.dbo.spt_values AS v
WHERE (type = 'P') AND (number BETWEEN 0 AND 51)) AS a
All I am trying to get is, for all the weeks starting on Mondays, I am trying to figure out if a purchase has been made during that week.
The outer query returns the week numbers and start week of the dates for this year.
But since it is a cross join, it is returning a cartesian product and I just want week numbers, week start date and whether or not a purchase has been made.
The output should be something like
Week # Week Start Purchase Done
1 05/01 Yes
2 12/01 No
3 19/01 Yes
and so on....
Thanks for your time.
June 12, 2009 at 3:24 am
Please provide table structure, sample data and expected result set based on the sample as described in the link in my signature.
Reason: It looks like you don't need the cross join at all.
Basis idea: using a CTE/subquery with the Group By function on your table dbo.tblPurchase to get the weeks where a purchase has been made and do an right outer join to a calendar table. If you don't have a calendar table by now you might want to look into it. It also could be created on the fly within the CTE, if this query is not heavily used.
February 27, 2015 at 6:21 am
Didn't realize this was so old. Saw the link on an article in today's newsletter and assumed it was current.
It seems like your condition using BETWEEN is incorrect and the test should be
purchaseDate >= a.StartDate And puchaseDate < a.NextDate
That being said, here are three different options that should get you started:
The most straightforward approach is to do a subquery in the CASE
Selecta.Week,
Cast(StartDate as Date),
Case
When Exists(
Select*
From(
Values
( '1/6/2009' ),
( '1/25/2009' ),
( '1/26/2009' ),
( '2/1/2009' ),
( '2/9/2009' ),
( '3/2/2009' )
) as tblPurch( purchaseDate )
WherepurchaseDate >= a.StartDate and purchaseDate < a.NextDate )
Then'Yes'
Else'No'
End as PurchaseDone
From(
SELECT number AS Week,
DATEADD(wk, number, DATEADD(DD, 1 - DATEPART(DW, '08-Jan-09'), '08-Jan-09')) AS StartDate,
DATEADD(wk, number + 1, DATEADD(DD, 1 - DATEPART(DW, '08-Jan-09'), '08-Jan-09')) AS NextDate
FROM master.dbo.spt_values AS v
WHERE (type = 'P')
AND (number BETWEEN 0 AND 51)
) AS a
This version requires an aggregate to Count the number of purchase dates within each week.
Selecta.Week,
Cast(StartDate as Date),
Case
WhenCount( purchaseDate ) > 0
Then'Yes'
Else'No'
End as PurchaseDone
From(
SELECT number AS Week,
DATEADD(wk, number, DATEADD(DD, 1 - DATEPART(DW, '08-Jan-09'), '08-Jan-09')) AS StartDate,
DATEADD(wk, number + 1, DATEADD(DD, 1 - DATEPART(DW, '08-Jan-09'), '08-Jan-09')) AS NextDate
FROM master.dbo.spt_values AS v
WHERE (type = 'P')
AND (number BETWEEN 0 AND 51)
) AS a
Left
Join(
SelectpurchaseDate
From(
Values
( '1/6/2009' ),
( '1/25/2009' ),
( '1/26/2009' ),
( '2/1/2009' ),
( '2/9/2009' ),
( '3/2/2009' )
) as tblPurch( purchaseDate )
) t2
OnpurchaseDate > = a.StartDate and purchaseDate < a.NextDate
Group
Bya.Week,
a.StartDate
Finally, you can convert each purchase date to a given day in the week that it falls in. This code uses the first day
which better exposed the issue of the match criteria.
Selecta.Week,
Cast(StartDate as Date),
Case
Whent1.WeekStart is not null
Then'Yes'
Else'No'
End as PurchaseDone
From(
SELECT number AS Week,
DATEADD(wk, number, DATEADD(DD, 1 - DATEPART(DW, '08-Jan-09'), '08-Jan-09')) AS StartDate,
DATEADD(wk, number + 1, DATEADD(DD, 1 - DATEPART(DW, '08-Jan-09'), '08-Jan-09')) AS NextDate
FROM master.dbo.spt_values AS v
WHERE (type = 'P')
AND (number BETWEEN 0 AND 51)
) AS a
Left
Join(
SelectDistinct
DateAdd( dd, -1 * (DatePart( weekday, purchaseDate ) - 1), purchaseDate ) WeekStart
From(
Values
( '1/6/2009' ),
( '1/25/2009' ),
( '1/26/2009' ),
( '2/1/2009' ),
( '2/9/2009' ),
( '3/2/2009' )
) as tblPurch( purchaseDate )
)t1
Ont1.WeekStart >= a.StartDate and t1.WeekStart < a.NextDate
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply