getting Count on weekly basis - 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

SQL Server 2005 Forums

Transact-SQL (2005)

getting Count on weekly basis

Author

Topic

chetanb3
Yak Posting Veteran

52 Posts

Posted - 2008-04-11 : 00:15:15

Hi All
I am using following code for my application
SELECT
convert(varchar(10),acr._eventTime,120) [Date],
sum(case r.Severity WHEN 'Warning' THEN 1 end)[Warning],
isnull(sum(case r.Severity WHEN 'OK' THEN 1 end),0)[OK],
FROM ActiveCheckIssueResult r
GROUP BY convert(varchar(10),acr._eventTime,120)

it gives me Result as,

Date warning OK
--------- --------- -----
1/1/1008 1 1
2/1/2008 0 2

i want count on weekly basis
Could any one help me?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 01:51:22

What defines a week at your company?

E 12°55'05.25"
N 56°04'39.16"

chetanb3
Yak Posting Veteran

52 Posts

Posted - 2008-04-11 : 04:24:57

quote:
Originally posted by Peso

What defines a week at your company?

E 12°55'05.25"
N 56°04'39.16"

I have data for every second in my table for column eventDate

i want to show how many count of event in a week

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-11 : 04:31:34

yeh, but Peter's question was what defines a week? you can use the datepart function to get week, but that's not necessarily going to get you what you want.

a week could be defined as 00:00 monday to 23:59 sunday? or 09:00 monday to 17:00 friday etc...?

Em

chetanb3
Yak Posting Veteran

52 Posts

Posted - 2008-04-11 : 05:38:03

here is my table ...

_eventTime Severity
----------------------------------------------
2008-04-11 14:59:02.237 Warning
2008-04-12 14:59:02.237 OK
2008-04-13 14:59:01.200 OK
2008-04-13 14:59:02.231 Warning

.
.
.
with above query i m getting result ,

Date warning OK
--------- --------- -----
2008-04-11 1 0
2008-04-12 0 1
2008-04-13 1 1

but i want ,

week -- warning --- ok
------------------------------------------
2008-04-06 to 2008-04-12 1 1
2008-04-13 to 2008-04-19 1 1

like that..
thanks guys for helping me.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-11 : 11:21:51

[code]
declare @t table (eventtime datetime,severity varchar(20))
insert into @t
select '2008-04-11 14:59:02.237', 'Warning'
union all select '2008-04-12 14:59:02.237', 'OK'
union all select '2008-04-13 14:59:01.200', 'OK'
union all select '2008-04-13 14:59:02.231', 'Warning'

select datepart(wk,eventtime) as weekNo
,sum(case Severity WHEN 'Warning' THEN 1 else 0 end) as[Warning]
,sum(case Severity WHEN 'OK' THEN 1 else 0 end) as [OK]
from @t
group by datepart(wk,eventtime)
[/code]

Em

chetanb3
Yak Posting Veteran

52 Posts

Posted - 2008-04-14 : 02:33:20

thanks i got my answer just one query.

in following code i have changed date

declare @t table (eventtime datetime,severity varchar(20))
insert into @t
select '2008-04-11 14:59:02.237', 'Warning'
union all select '2008-04-12 14:59:02.237', 'OK'
union all select '2006-04-13 14:59:01.200', 'OK'
union all select '2005-04-13 14:59:02.231', 'Warning'
select datepart(wk,eventtime) as weekNo
,sum(case severity WHEN 'Warning' THEN 1 else 0 end) as[Warning]
,sum(case severity WHEN 'OK' THEN 1 else 0 end) as [OK]
from @t
group by datepart(wk,eventtime)

it gives me same week number as that of previous.and i don't want duplicate values.
i have data for last 2,3 years.
Could you please tell me from where 'datepart' function start counting week.
do i need to set someting to get last year result also

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-14 : 06:10:03

select and group by the year as well.... datepart(yyyy,eventtime)

what BOL says about wk...

The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year.

Em

chetanb3
Yak Posting Veteran

52 Posts

Posted - 2008-04-15 : 02:11:26

it solved my problem.
Thanks all for helping me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources