Search for orders placed during any ISO week of a given year using T-SQL

Question

As part of setting up reports, I have an SQL query that selects the number of orders placed every 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

Please note that I use the new format isowkin the function call datepart, as business in Denmark usually uses ISO week numbers.

Running this query with help @Year = 2010gives a result similar to this:

1           5
2           7
3           10
...
53          3

, 2010 , , , 53 . , 53 2009 . , , , . , 53, "" 2009 .

WHERE, , 1 (52 53) , @Year?

Answer 1

, , . , , between .

, :

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

: ( Sql Server 2008).

between:

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

, between . order_date ( ), -1 where. order_date , , .

Answer 2

- , 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;

, (isoyear,...)...

Answer 3

:

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;

, , !

Answer 4

IF - SQL-, :

// 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))

Answer 5

:

ISO, , ISO ( )

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