I have a query that counts member's wedding dates in the database.
SELECT
SUM(NumberOfBrides) AS [Wedding Count]
, DATEPART( wk, WeddingDate) AS [Week Number]
, DATEPART( year, WeddingDate) AS [Year]
FROM MemberWeddingDates
GROUP BY DATEPART(year, WeddingDate), DATEPART(wk, WeddingDate)
ORDER BY SUM(NumberOfBrides) DESC
How do I work out when the start and end of each week represented in the result set?
SELECT
SUM(NumberOfBrides) AS [Wedding Count]
, DATEPART(wk, WeddingDate) AS [Week Number]
, DATEPART(year, WeddingDate) AS [Year]
, ??? AS WeekStart
, ??? AS WeekEnd
FROM MemberWeddingDates
GROUP BY DATEPART(year, WeddingDate), DATEPART(wk, WeddingDate)
ORDER BY SUM(NumberOfBrides) DESC
You can find the day of week and do a date add on days to get the start and end dates..
DATEADD(dd, -(DATEPART(dw, WeddingDate)-1), WeddingDate) [WeekStart]
DATEADD(dd, 7-(DATEPART(dw, WeddingDate)), WeddingDate) [WeekEnd]
You probably also want to look at stripping off the time from the date as well though.