CODEWITHSUNDEEP
sqlsql-servert-sql
Jørn Schou-Rode
Jørn Schou-Rode

Reputation: 38366

Finding orders made within any ISO week of a specific year using T-SQL

As part of a reporting setup, I have a SQL query fetching the number of orders placed each week:

select datepart(isowk, order_date), count(*)
from orders where year(order_date) = @Year
group by datepart(isowk, order_date), year(order_date)
order by 1

Note that I am using the new isowk format in the datepart function call, as business in Denmark typically use ISO week numbers.

Running this query with @Year = 2010 yields a result set simialar to this:

1           5
2           7
3           10
...
53          3

You and I both know that 2010 isn't over yet, and certainly there has been no week 53 yet. Actually, there has - the first three days of the year belonged to ISO week 53 of 2009. While it might be possible to explain this to other programmers, the people who are going to read my reports are never going to understand this. Hence, I want to get rid of this week 53, by "moving" the data over to 2009.

How can I rewrite my WHERE clause to filter the data set to orders made between monday of week 1 and friday of the last week (52 or 53) in the year defined as @Year?

Upvotes: 3

Views: 1943

Answers (5)

piers7
piers7

Reputation: 4414

Just to spell out the fundamental answer here:

If you are querying based on ISO weeks, you must filter based on ISO Year (and not calendar year)

How you add an ISO year column to your data (or where clause) is as others have described.

Upvotes: 0

Jørn Schou-Rode
Jørn Schou-Rode

Reputation: 38366

I am going to take a stab at this myself, using the approach described in my comments on Manfred's answer. The idea is to find the first day of the first ISO week of the specified year and the year after, allowing for index-enabled between filtering of the input records.

First, I create a function which will find the first day of the first ISO week in a specific year:

create function firstIsoDay(@year int) returns datetime as begin
    declare @first datetime
    select @first = cast(cast(@year as char(4)) as datetime)
    select @first = dateadd(ww, datediff(ww, 0, @first), 0)
    if datepart(isowk, @first) > 1 set @first = @first + 7
    return @first
end

Disclaimer: This function is untested (having no Sql Server 2008 available at the moment).

The actual data query can then be made using two invocations of this function in a between filter:

select datepart(isowk, order_date), count(*) from orders
where order_date between firstIsoDay(@Year) and firstIsoDay(@Year + 1)
group by datepart(isowk, order_date), year(order_date)
order by 1

Remember that between is two-way inclusive. If the values in order_date contain truncated dates (no time), -1 should be added to the end of the where clause. In my use case, order_date has both date and time, so between two dates at midnight should do just fine.

Upvotes: 0

Manfred Sorg
Manfred Sorg

Reputation: 1890

You could try this:

set datefirst 1;

declare @Year smallint = 2010;

declare @DayInFirstWeek datetime = cast(@Year as varchar)+'0104';
declare @FirstDayInFirstWeek datetime = 
             @DayInFirstWeek - datepart(dw,@DayInFirstWeek)+1
declare @DayInFirstWeekNextYear datetime = cast(@Year+1 as varchar)+'0104';
declare @LastDayInLastWeek datetime = 
             @DayInFirstWeekNextYear - datepart(dw,@DayInFirstWeekNextYear);

select datepart(isowk, order_date), count(*) 
from orders
where order_date between @FirstDayInFirstWeek and @LastDayInLastWeek
group by datepart(isowk, order_date)
order by 1;

If your first day of week isn't Monday it perhaps won't work!

Upvotes: 1

Manfred Sorg
Manfred Sorg

Reputation: 1890

In fact I do not know any programming language which delivers a isoyear ...

create function isoyear(@date datetime) returns smallint as begin
declare @isoyear smallint =
    case
    when datepart(isowk, @date) = 1 and month(@date) = 12
    then year(@date)+1
    when datepart(isowk, @date) = 53 and month(@date) = 1
    then year(@date)-1
    else year(@date)
    end;
return @isoyear;
end;

Hope we'll get a datepart(isoyear, ...) next time ...

Upvotes: 2

Alex Weinstein
Alex Weinstein

Reputation: 9891

Do an IF statement - I don't guarantee the SQL server syntax, but:

// IF ISO week is 53, return week 1 of next year. 
GROUP BY IF(datepart(isowk, order_date)) = 53,
  CONCAT("1/", year(order_date) + 1),
  CONCAT(datepart(isowk, order_date), "/", year(order_date))

Upvotes: 0

Related Questions