InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 04:22:24 GMT20http://www.sqlservercentral.com/Forums/Topic733499-145-1.aspxPlease 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.Fri, 12 Jun 2009 03:24:41 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic733499-145-1.aspxHow 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 NextDateFROM master.dbo.spt_values AS vWHERE (type = 'P') AND (number BETWEEN 0 AND 51)) AS aAll 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 likeWeek # Week Start Purchase Done1 05/01 Yes2 12/01 No3 19/01 Yesand so on....Thanks for your time.Thu, 11 Jun 2009 22:24:42 GMTbalars_2000