Weekly group?

Question

I am trying to group a series of dates by week. So far I have the following:

SELECT DATEPART(week, CONVERT(VARCHAR(50), e.event_date, 107)) AS 'Date' , c.setting_secondary AS 'Workflow Cat' , d.setting_main AS 'Error Type' , SUM(e.event_count) AS 'Total' FROM marlin.support_events AS e INNER JOIN marlin.support_config AS c ON e.event_category = c.setting_code AND config_code = 60 INNER JOIN marlin.support_config AS d ON e.event_type = d.setting_code AND d.config_code = 70 WHERE e.event_date BETWEEN DATEADD(MONTH, -2, GETDATE()) AND GETDATE() AND c.setting_secondary = 'Expenditure Voucher' AND d.setting_main IN ( 'Unstarted' , 'Timeout' ) GROUP BY DATEPART(week, CONVERT(VARCHAR(50), e.event_date, 107)) , c.setting_secondary , d.setting_main , e.event_summary 

This shows me the week number, but not the date on which this week began, for example:

How can I show what date this week starts?

Answer:

The answer below and an alternative method that I also found for this:

 DATEADD(dd, -(DATEPART(dw, e.event_date)-1), e.event_date) 

Answer 1

You can get the part of the year from the date, add the first day of the first month, and then add (#week - 1) to get the start day of the week to which event_date belongs, as follows:

 SELECT EventDate, WorkflowCat, ErrorType, SUM(EventCount) AS 'Total' FROM ( SELECT DATEADD(ww, DATEPART(ww, e.event_date) - 1, CONVERT(DATETIME, CONVERT(VARCHAR(4), DATEPART(yy, e.event_date)) + '-01-01')) AS 'EventDate' , c.setting_secondary AS 'WorkflowCat' , d.setting_main AS 'ErrorType', e.event_summary as 'EventSummary' e.event_count AS 'EventCount' FROM marlin.support_events AS e INNER JOIN marlin.support_config AS c ON e.event_category = c.setting_code AND config_code = 60 INNER JOIN marlin.support_config AS d ON e.event_type = d.setting_code AND d.config_code = 70 WHERE e.event_date BETWEEN DATEADD(MONTH, -2, GETDATE()) AND GETDATE() AND c.setting_secondary = 'Expenditure Voucher' AND d.setting_main IN ( 'Unstarted' , 'Timeout' ) ) GROUP BY EventDate, WorkflowCat, ErrorType, EventSummary