| Author |
 Topic  |
|
|
chetanb3
Starting Member
India
49 Posts |
 Posted - 04/11/2008 : 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? |
Edited by - chetanb3 on 04/11/2008 00:17:04
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/11/2008 : 01:51:22
|
What defines a week at your company?
E 12°55'05.25"
N 56°04'39.16" |
 |
|
|
chetanb3
Starting Member
India
49 Posts |
Posted - 04/11/2008 : 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
United Kingdom
1208 Posts |
Posted - 04/11/2008 : 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
Starting Member
India
49 Posts |
Posted - 04/11/2008 : 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.
|
Edited by - chetanb3 on 04/11/2008 05:39:25 |
|
|
|
elancaster
A very urgent SQL Yakette
United Kingdom
1208 Posts |
Posted - 04/11/2008 : 11:21:51
|
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)
Em |
|
|
|
chetanb3
Starting Member
India
49 Posts |
Posted - 04/14/2008 : 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 |
Edited by - chetanb3 on 04/14/2008 05:18:08 |
|
|
|
elancaster
A very urgent SQL Yakette
United Kingdom
1208 Posts |
Posted - 04/14/2008 : 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
Starting Member
India
49 Posts |
Posted - 04/15/2008 : 02:11:26
|
it solved my problem.
Thanks all for helping me. |
Edited by - chetanb3 on 04/15/2008 02:12:11 |
|
|
| |
Topic |
|
getting Count on weekly basis
