Count by Weeks - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Count by Weeks

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-11-21 : 05:34:02

HI All

Sorry to bother you yet again but i need some assistance and i am hoping you can help.
Aim - Looks at the “CreatedDate” for the latest month and count how many created dates fall within a given week
Currently the created date is displayed as
2013-11-01T09:08:54.000Z
2013-11-05T12:32:26.000Z
2013-11-15T12:37:17.000Z

So for Example
Week 1 = 2
Week 2=1
Week 3=0
Week 4= 0

My table is
select
[CreatedDate],
from #build

Looking forward to your help

KR
D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-21 : 06:53:36

count how many created dates fall within a given week
How do you pass the week? Is it absolute week or do we have different day as start of week?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-21 : 07:01:15

what is your definition of week ?

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

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-11-21 : 07:38:55

HI All

I created the query as follows

Not sure if there is a simpler method though

with cte as
(
select
(DATEPART(day,left([CreatedDate],10))-1)/7 + 1 as week_no,
left([CreatedDate],7) + ' - ' +
convert(varchar(20),(DATEPART(day,left([CreatedDate],10))-1)/7 + 1) as week_no1,
left([CreatedDate],7) + '-01' as Report_Month,

*
from #build
)

select COUNT(id),
report_month,
week_no1
from cte
where report_month = convert(varchar(8),convert(date,GETDATE(),109)) + '01'
group by week_no1, report_month
order by week_no1 desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-21 : 07:43:52

again it depends how you want week to be considered

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-11-21 : 07:52:36

HI visakh16

A week is considered monday to sunday

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-21 : 09:21:07

quote:
Originally posted by masond

HI visakh16

A week is considered monday to sunday

Then how would 2013-11-01 and 2013-11-05 consider under week 1 ?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources