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

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 ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources