SQLServerCentral.com / Development / SQL Server 2005 / Cross Joins / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 08:57:21 GMT20RE: Cross Joinshttp://www.sqlservercentral.com/Forums/Topic733499-145-1.aspxDidn'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 &gt;= a.StartDate And puchaseDate &lt; a.NextDateThat being said, here are three different options that should get you started:The most straightforward approach is to do a subquery in the CASE[code="plain"]Select a.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 ) Where purchaseDate &gt;= a.StartDate and purchaseDate &lt; a.NextDate ) Then 'Yes' Else 'No' End as PurchaseDoneFrom ( 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[/code]This version requires an aggregate to Count the number of purchase dates within each week.[code="plain"]Select a.Week, Cast(StartDate as Date), Case When Count( purchaseDate ) &gt; 0 Then 'Yes' Else 'No' End as PurchaseDoneFrom ( 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 aLeftJoin ( Select purchaseDate From ( Values ( '1/6/2009' ), ( '1/25/2009' ), ( '1/26/2009' ), ( '2/1/2009' ), ( '2/9/2009' ), ( '3/2/2009' ) ) as tblPurch( purchaseDate ) ) t2On purchaseDate &gt; = a.StartDate and purchaseDate &lt; a.NextDateGroupBy a.Week, a.StartDate[/code]Finally, you can convert each purchase date to a given day in the week that it falls in. This code uses the first daywhich better exposed the issue of the match criteria.[code="plain"]Select a.Week, Cast(StartDate as Date), Case When t1.WeekStart is not null Then 'Yes' Else 'No' End as PurchaseDoneFrom ( 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 aLeftJoin ( Select Distinct 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 ) )t1On t1.WeekStart &gt;= a.StartDate and t1.WeekStart &lt; a.NextDate[/code]Fri, 27 Feb 2015 06:21:57 GMTbrdudleyRE: Cross Joinshttp://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 GMTLutzMCross Joinshttp://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