Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Cross Joins Expand / Collapse
Posted Thursday, June 11, 2009 10:24 PM


Group: General Forum Members
Last Login: Wednesday, June 24, 2009 7:50 PM
Points: 17, Visits: 31
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.
Post #733499
Posted Friday, June 12, 2009 3:24 AM



Group: General Forum Members
Last Login: Yesterday @ 2:38 PM
Points: 6,897, Visits: 13,531
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.

A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #733616
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse