Cross Joins

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Cross Joins

Cross Joins

Rate Topic

Display Mode

Topic Options

Author

Message

balars_2000

Posted Thursday, June 11, 2009 10:24 PM

Grasshopper

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

LutzM

Posted Friday, June 12, 2009 3:24 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

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.

Lutz
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 »

Permissions