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
Author
Message
Posted Thursday, June 11, 2009 10:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, July 13, 2015 10:29 AM
Points: 6,897, Visits: 13,551
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
Posted Friday, February 27, 2015 6:21 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:19 PM
Points: 1,504, Visits: 1,675
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
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 >= 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.

Select a.Week,
Cast(StartDate as Date),
Case
When Count( 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 (
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 )
) t2
On purchaseDate > = a.StartDate and purchaseDate < a.NextDate
Group
By a.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.

Select a.Week,
Cast(StartDate as Date),
Case
When t1.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 (
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 )
)t1
On t1.WeekStart >= a.StartDate and t1.WeekStart < a.NextDate


Post #1664193
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse