Days with 0 record - 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 2000 Forums

Transact-SQL (2000)

Days with 0 record

Author

Topic

baranozgul
Starting Member

20 Posts

Posted - 2005-05-17 : 09:01:05

Hello,

I want to calculate availability score of some services, by summing up the downtime entries encountered by the service each day.

When there is no down time entered for the service, I want the service to be scored 100% available.

Downtime table has the following columns:

ServiceID DownTime Date
19 20min 10.05.2005
22 18min 11.05.2005

and summing query goes like this:

Select SUM(DownTime) from DownTimeTable
GroupBy ServiceID, Date

Naturaly, when there is no downtime encountered for a service during a given day, the SUM returns NULL for that day.

How can I repsresent the service as being 100% availabkle when there are no downtime entries encountered by the service?

thnx

Baran

mr_mist
Grunnio

1870 Posts

Posted - 2005-05-17 : 09:09:29

SUM (isnull(downtime, 0)) ?

-------
Moo. :)

baranozgul
Starting Member

20 Posts

Posted - 2005-05-17 : 09:20:46

Thanks mr_mist,

To be more clear: Suppose that I need the scores for the days from 01.05.2005 to 05.05.2005.

The ideal result table would be:

ServiceID Score Date
==========================
19 ------ 98% - 01.05.2005
19 ------ 91% - 02.05.2005
19 ------ 100%- 03.05.2005 (no downtimes at all)
19 ------ 88% - 04.05.2005
19 ------ 100%- 05.05.2005 (no downtimes at all)

However, when I run my query I get:
ServiceID Score Date
==========================
19 ------ 98% - 01.05.2005
19 ------ 91% - 02.05.2005
19 ------ 88% - 04.05.2005

The problem occurs when I group and SUM downtimes according to the date, the dates in which no downtimes are encountered naturaly is not listed.

Is there a workaround for this?

Thanks again!

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-17 : 09:23:40

LEFT JOIN against a date table???

Andy

Beauty is in the eyes of the beerholder

mr_mist
Grunnio

1870 Posts

Posted - 2005-05-17 : 09:37:08

Yep, if you do not record downtime = 0 then the only thing that I can think of is to have a table that includes all the possible dates.

-------
Moo. :)

baranozgul
Starting Member

20 Posts

Posted - 2005-05-17 : 09:45:05

Thanks guys, I think I will do so

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources