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
Selecta.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 )
WherepurchaseDate >= 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.
Selecta.Week,
Cast(StartDate as Date),
Case
WhenCount( 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(
SelectpurchaseDate
From(
Values
( '1/6/2009' ),
( '1/25/2009' ),
( '1/26/2009' ),
( '2/1/2009' ),
( '2/9/2009' ),
( '3/2/2009' )
) as tblPurch( purchaseDate )
) t2
OnpurchaseDate > = a.StartDate and purchaseDate < a.NextDate
Group
Bya.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.
Selecta.Week,
Cast(StartDate as Date),
Case
Whent1.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(
SelectDistinct
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
Ont1.WeekStart >= a.StartDate and t1.WeekStart < a.NextDate