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
 using condition in SQL query

Author  Topic 

AlbEng
Starting Member

1 Post

Posted - 2014-12-12 : 03:33:03
Hi all,

i have a situation,must prepare a report to find availability of it devices.they are given the specific ip of devices.

my query


SELECT
Resultdate, [SERVICE] ,[SERVICE TYPE] ,[IP]
, DailyAvailability




FROM [Orion].[dbo].[DepartmentNodesNew] t1

left join ( select round(1-(sum([DownTimeMin])/(1440*count(*))),4)*100 as DailyAvailability,
count(*) as Node, c2.ResultDate ,[Departament] ,[IPAdress]
from [Orion].[dbo].[OrionEventsNodeDownTime] as c2 group by [Departament] ,[IPAdress] , c2.ResultDate) t2
on t1.IP=t2.IPAdress

where t2.DailyAvailability is not null and t1.SERVICE='SAP CRM' and t2.ResultDate='2014-12-10'

order by ResultDate

now how can i calculate the daily availability of sap crm which have 2 servers and they are in cluster,when one is down service is ok.when both of them are down service is off. how can i calculate by writing conditions.

case when ([SERVICE]='SAP CRM' AND [SERVICE TYPE]= 'DB Server' and [DownTimeMin] =0) then DBAvailability 100

else then DBAvailability select round(1-(sum([DownTimeMin])/(1440*count(*))),4)*100 ???

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-12 : 08:52:44
write:


select ...
, CASE
WHEN (first condition) THEN 100
WHEN (second condition) THEN ...
ELSE <default value>
END AS DBAvailability
FROM ...
Go to Top of Page
   

- Advertisement -