Query grouped by calendar week / Issue: Date expands over more than one year

Question

I have an SQL Query which calculates a quote of two sums. This quote shall be calculated on a weekly basis. Therefore is used Datepart('ww', Date, 2, 2) to get the calendar week.

This query worked fine during 2014, but now I am facing a problem.

The users choses with date pickers in a form from and to date which is then used in the where clause to only select relevant records.

If they chose a from date from past year (e.g. 2014) and a to date from this year my query shows irrelvant data, because it does not cosinder the year just the calendar week.

So, how group by calendar week AND only chose records from the correct year.

SELECT DatePart('ww',Date,2,2) AS WEEK, 
     Year(Workload_Date) AS [YEAR], 
    (SUM(Value1)+SUM(Value2))AS [Total1], 
    (SUM(Value3)+SUM(Value4)) AS [Total2],
    ((SUM(Value1)+SUM(Value2))/(SUM(Value3)+SUM(Value4))) AS [Quote]
FROM tbl
WHERE DatePart('ww',Date,2,2) Between DatePart('ww',FromDatePickerField,2,2) And DatePart('ww',ToDatePickerField,2,2)
GROUP BY DatePart('ww',Date,2,2), Year(Date)
ORDER BY Year(Date), DatePart('ww',Date,2,2); 

The table contains one record per day.

Date       | Value1 | Value2 | Value3 | Value4 |
01.01.2014 | 4      | 3      | 2      | 3      |
02.01.2014 | 4      | 3      | 9      | 3      |
03.01.2014 | 4      | 3      | 4      | 1      |
04.01.2014 | 4      | 3      | 1      | 3      |
...
01.01.2015 | 4      | 3      | 6      | 3      | 
02.01.2015 | 4      | 3      | 3      | 7      |

Answer 1

You could base your logic on Week_Ending_date instead of the week number and year, that way you could aggregate all you data on a weekly basis and let SQL handle the week/year detection logic.

Incase, you have a date range that spans 2 years, even then the calculations will be based on the week_ending_date and should work out correctly.

Something like...

  SELECT DATEADD(dd, 7-(DATEPART(dw, DATE)), DATE)  AS WEEK_ENDING_DATE
        ,Year(DATEADD(dd, 7-(DATEPART(dw, DATE)), DATE)) AS [YEAR]
        ,(SUM(Value1) + SUM(Value2)) AS [Total1]
        ,(SUM(Value3) + SUM(Value4)) AS [Total2]
        ,((SUM(Value1) + SUM(Value2)) / (SUM(Value3) + SUM(Value4))) AS [Quote]
    FROM tbl
    WHERE DATEADD(dd, 7-(DATEPART(dw, DATE)), DATE) BETWEEN DATEADD(dd, 7-(DATEPART(dw, FromDatePickerField)), FromDatePickerField)
            AND DATEADD(dd, 7-(DATEPART(dw, ToDatePickerField)), ToDatePickerField)
and date >= FromDatePickerField
and date <= ToDatePickerField
    GROUP BY DATEADD(dd, 7-(DATEPART(dw, DATE)), DATE)    
    ORDER BY DATEADD(dd, 7-(DATEPART(dw, DATE)), DATE)