Counting weeks, without using datediff(want ISO w)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Counting weeks, without using datediff(want ISO w)

Author

Topic

ejnot
Starting Member

8 Posts

Posted - 2012-06-18 : 04:11:19

Hi,
I try to count weeks. My problem is that datediff is not using ISO-Weeks(my database do). So, I have to find another way to count them...
any ideas?

I have a table where every day has its own row. The ISO -weeknumbers are the table.

Or does anyone know a function that use the ISO-standard? maybe it is possible to manipulate the datediff?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-18 : 04:21:06

SQL 2008 has a ISO_WEEK for datepart(). You can find the ISO_WEEK no of the 2 dates and subtract it

But since you have your own calendar table, it is probably easier to use that

KH
[spoiler]Time is always against us[/spoiler]

ejnot
Starting Member

8 Posts

Posted - 2012-06-18 : 04:38:28

Thanks,you see, my problem appear when I want to count weeks exeeding a year.

Example: how many weeks ago is week no 51 2011 from week 24 2012?
or: How many weeks ahead is week no 3 2013 from week 24 2012?
or even worse: how many weeks ago is jan 1 2012 when the week number of this date is week 52?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-18 : 04:41:27

using your own calendar table


select count(distinct iso_week)
from   calendar
where  date between '2011-12-01' and '2012-03-15'

KH
[spoiler]Time is always against us[/spoiler]

ejnot
Starting Member

8 Posts

Posted - 2012-06-18 : 05:47:07

ok, I can see that is the easiest way of counting.

Let's complicate this a bit more. I want to add this number as a attribute for every date in the table. And if the counting exceeds a year, distinct() will only give me 52 or 53.

What I want is the sum of this count for every date:

select count(iso_week)
from calendar
where date between '2011-12-01' and '2012-03-15'
group by year

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-18 : 05:50:57

[code]select sum(cnt)
from
(
select year, cnt = count(distinct iso_week)
from calendar
where date between '2011-12-01' and '2012-03-15'
GROUP BY year
) d
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-18 : 05:51:52

[code]select count(distinct year * 100 + iso_week)
from calendar
where date between '2011-12-01' and '2012-03-15'[/code]

KH
[spoiler]Time is always against us[/spoiler]

ejnot
Starting Member

8 Posts

Posted - 2012-06-22 : 09:58:46

Hi,
I chose to count mondays instead.
here is what I did:

CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate date )

RETURNS varchar(10)
AS
BEGIN
DECLARE @rtDayofWeek VARCHAR(10)

select @rtDayofWeek = count(datepart(d,date))
from Calendar
where [Date] between getdate() and @today
and datepart(d,date) = 7

RETURN (@rtDayofWeek)
END

--Then I can write this statement:
select Date, dbo.udf_DayOfWeek(Date) as dayweek
from Calendar
where Date between '20120101' and getdate()
order by Date

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources