CODEWITHSUNDEEP
sqlsql-server
user3077053
user3077053

Reputation: 31

Weekly Data Sql Query Thursday to Wednesday

By using below the link I am trying to make a weekly query. My week starts on Thursday and lasts to Wednesday which I change correctly but the sum of (Field1) is not out coming correctly. What I am doing wrong?

Query for weekly report

SELECT 
    DATEADD(WEEK, DATEDIFF(WEEK, '19000104', START_DATETIME), '19000104') AS EveryThrusday,
    DATEADD(WEEK, DATEDIFF(WEEK, '19000104', START_DATETIME)+1, '19000104') - 1 AS EveryWednesday,
    SUM(Field1)
FROM 
    Table1
GROUP BY 
    DATEADD(WEEK, DATEDIFF(WEEK, '19000104', START_DATETIME), '19000104'),  
    DATEADD(WEEK, DATEDIFF(WEEK, '19000104', START_DATETIME)+1, '19000104')-1
ORDER BY
    DATEADD(WEEK, DATEDIFF(WEEK, '19000104', START_DATETIME), '19000104'),  
    DATEADD(WEEK, DATEDIFF(WEEK, '19000104', START_DATETIME)+1, '19000104')-1

Table 1:

Date        field1
1-May-14    102948.66
2-May-14    102927.21
3-May-14    102925.89
4-May-14    102427.55
5-May-14    101551.95
6-May-14    102952.12
7-May-14    102947.53

Total = 718680.93

Table 2:

Start date  End date    field1
1-May-14    7-May-14    719793.72

In the first table I am retrieving the data on per day basis from Thursday 1 May to 7th May. The sum of those days is 718680.93

But if I run the weekly query see values is different in field1 table 2

Upvotes: 3

Views: 2415

Answers (1)

Andriy M
Andriy M

Reputation: 77737

You could easily see why the sum differs from the correct value by looking at the result of DATEADD(WEEK, DATEDIFF(WEEK, '19000104', START_DATETIME), '19000104') for every date in your range. The following query:

SELECT
  START_DATETIME,
  Thursday = DATEADD(WEEK, DATEDIFF(WEEK, '19000104', START_DATETIME), '19000104')
FROM dbo.Table1;

would give you results like these:

START_DATETIME  Thursday
--------------  ----------
2014-05-01      2014-05-01
2014-05-02      2014-05-01
2014-05-03      2014-05-01
2014-05-04      2014-05-08
2014-05-05      2014-05-08
2014-05-06      2014-05-08
2014-05-07      2014-05-08

As you can see, your expression does not return identical results for all the dates within the same week that starts on Thursday. As a result, your query groups different dates, which, in turn, is why you get the incorrect results.

Please note that the expression would return same inconsistent dates regardless of the DATEFIRST setting, because DATEDIFF does not respect that setting and always calculates the result as if DATEFIRST was set to 7.

To work around this issue, you could replace the DATEDIFF(WEEK) by DATEDIFF(DAY) / 7:

DATEADD(WEEK, DATEDIFF(DAY, '19000104', START_DATETIME) / 7, '19000104')

Since both operands would be integer, SQL Server would perform integral division and its result, therefore, would be a whole (and correct in this case) number of weeks.

Alternatively, you could calculate the beginning of the week by subtracting the week day number from the date:

DATEADD(DAY, 1 - DATEPART(WEEKDAY, START_DATETIME), START_DATETIME)

Similarly, the end of the week would be using 7 - DATEPART(...). The above assumes that DATEFIRST is set to 4 (i.e. Thursday). If it is different in your environment, you would need to adjust the second argument of DATEADD accordingly.

Upvotes: 1

Related Questions