SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 getting Count on weekly basis
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chetanb3
Yak Posting Veteran

India
52 Posts

Posted - 04/11/2008 :  00:15:15  Show Profile  Reply with Quote
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
30265 Posts

Posted - 04/11/2008 :  01:51:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
What defines a week at your company?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

chetanb3
Yak Posting Veteran

India
52 Posts

Posted - 04/11/2008 :  04:24:57  Show Profile  Reply with Quote
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
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts

Posted - 04/11/2008 :  04:31:34  Show Profile  Reply with Quote
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
Go to Top of Page

chetanb3
Yak Posting Veteran

India
52 Posts

Posted - 04/11/2008 :  05:38:03  Show Profile  Reply with Quote
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
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts

Posted - 04/11/2008 :  11:21:51  Show Profile  Reply with Quote

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
Go to Top of Page

chetanb3
Yak Posting Veteran

India
52 Posts

Posted - 04/14/2008 :  02:33:20  Show Profile  Reply with Quote
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
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts

Posted - 04/14/2008 :  06:10:03  Show Profile  Reply with Quote
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
Go to Top of Page

chetanb3
Yak Posting Veteran

India
52 Posts

Posted - 04/15/2008 :  02:11:26  Show Profile  Reply with Quote
it solved my problem.
Thanks all for helping me.

Edited by - chetanb3 on 04/15/2008 02:12:11
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000