0 votes
1 view
in SQL by (21k points)

I have a query that counts the 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

1 Answer

0 votes
by (37.4k points)

Try using the below code to find the day of the 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]

...