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
 General SQL Server Forums
 New to SQL Server Programming
 count
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

M_henriksen
Starting Member

5 Posts

Posted - 07/02/2014 :  15:47:02  Show Profile  Reply with Quote
Hi all.

I have a table called "jobstatus" where I want to know how many servers there don't have a statuscode = 0 for the last 24 hours.

I have made this query, but I can see there is a issue when one server have more than one status ex. both statuscode = 0 and status code = 50.
If this is the case the server is ok and should not be listed in the count

( SELECT count(DISTINCT clientname) AS "No backup for 24 hours" FROM Jobstatus where statusCode not in (0,1) and DATEDIFF(hour,UtcBigIntToNomTime(endTime), GETDATE()) <= 24)

Br Morten

tkizer
Almighty SQL Goddess

USA
37316 Posts

Posted - 07/02/2014 :  15:52:34  Show Profile  Visit tkizer's Homepage  Reply with Quote
Show us some sample data as I don't see server in your query. I suspect there is more to the story and will lead to back and forth questions/answers, which can be avoided with sample data.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

M_henriksen
Starting Member

5 Posts

Posted - 07/02/2014 :  16:01:20  Show Profile  Reply with Quote
Hi Tara.
sorry for the confusion "clientname" is servername

Br Morten
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37316 Posts

Posted - 07/02/2014 :  16:17:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
;with cte (clientname, countt)
as
(
SELECT clientname, count(*) AS countt
FROM @t
where DATEDIFF(hour,createdate, GETDATE()) <= 24
group by clientname
)
SELECT t.clientname, count(*) AS "No backup for 24 hours"
FROM @t t
JOIN cte
ON t.clientname = cte.clientname
where statusCode not in (0,1) and DATEDIFF(hour,createdate, GETDATE()) <= 24 and countt = 1
group by t.clientname

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/02/2014 :  16:21:09  Show Profile  Reply with Quote
Again, it is not clear what you want for output. Against my better judgment, I'm taking a guess. If my guess is not correct, please post sample data and expected output as Tara has suggested. If you are not familiar with how to do that please follow these links:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

SELECT COUNT(*)
FROM
(
	SELECT ClientName
	FROM Jobstatus 
	WHERE DATEDIFF(hour,UtcBigIntToNomTime(endTime), GETDATE()) <= 24
	GROUP BY ClientName
	HAVING MAX(statusCode) > 0
) AS T
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/02/2014 :  16:24:51  Show Profile  Reply with Quote
Also, it may not matter for your case, but using nested functions in your WHERE clause is very inefficient. I'd suggest calculating a date to compare against. For example:
WHERE endTime > DATEADD(DAY, -1 GETUTCDATE())
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 07/02/2014 :  16:53:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT	COUNT(*)
FROM	(
		SELECT		ClientName
		FROM		dbo.Jobstatus 
		WHERE		DATEDIFF(HOUR, dbo.UtcBigIntToNomTime(EndTime), GETDATE()) <= 24
		GROUP BY	ClientName
		HAVING		MIN(StatusCode) > 0
	) AS t;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

M_henriksen
Starting Member

5 Posts

Posted - 07/03/2014 :  06:51:58  Show Profile  Reply with Quote
Thank you all so far.
Here is what the table "jobstatus" contains.

jobid - clientname - statuscode - starttime - endtime

30001 - Boxer - 0 - 02-07-2014-20:00 - 02-07-2014-01:45
30000 - Mickey - 0 - 02-07-2014-20:00 - 02-07-2014-01:45
29999 - faxe - 0 - 02-07-2014-23:30 - 02-07-2014-02:30
29998 - alfa - 0 - 02-07-2014-20:00 - 02-07-2014-01:45
29997 - token - 76 - 02-07-2014-20:00 - 02-07-2014-01:45
29996 - brake - 0 - 02-07-2014-20:00 - 02-07-2014-01:45
29995 - moon - 0 - 02-07-2014-20:00 - 02-07-2014-01:45
29994 - 77-prod - 0 - 02-07-2014-20:00 - 02-07-2014-01:45
29993 - faxe - 50 - 02-07-2014-20:00 - 02-07-2014-22:00
29992 - ggrip - 0 - 02-07-2014-20:00 - 02-07-2014-01:45

And the result after count should be 1. (jobid 29997 token does not have a status code 0 whitin the last 24 hours)
clientname faxe with status code 0 and 76 is ok because it have a status code 0 whitin the last 24 hours

Edited by - M_henriksen on 07/03/2014 06:55:51
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/03/2014 :  11:25:15  Show Profile  Reply with Quote
I'm assuming that none of the queries provided thus far have worked. So, if you want more assistance I'd suggest you re-read the links I posted on 07/02/2014 16:21:09 and post your data in a format we can run queries against (create table and insert statements).
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

423 Posts

Posted - 07/03/2014 :  12:40:11  Show Profile  Reply with Quote
Just to prove that Lamprey's solution with SwePeso's correction works, I tested and the result=1 just as op's wanted. I have corrected datetimes so that the endtime is within the last 24 hours.
with jobstatus (jobid,clientname,statuscode,starttime,endtime)
  as (          select 30001,'Boxer',  0,cast('20140702 20:00' as datetime),cast('20140703 01:45' as datetime)
      union all select 30000,'Mickey', 0,cast('20140702 20:00' as datetime),cast('20140703 01:45' as datetime)
      union all select 29999,'faxe',   0,cast('20140702 23:30' as datetime),cast('20140703 02:30' as datetime)
      union all select 29998,'alfa',   0,cast('20140702 20:00' as datetime),cast('20140703 01:45' as datetime)
      union all select 29997,'token', 76,cast('20140702 20:00' as datetime),cast('20140703 01:45' as datetime)
      union all select 29996,'brake',  0,cast('20140702 20:00' as datetime),cast('20140703 01:45' as datetime)
      union all select 29995,'moon',   0,cast('20140702 20:00' as datetime),cast('20140703 01:45' as datetime)
      union all select 29994,'77-prod',0,cast('20140702 20:00' as datetime),cast('20140703 01:45' as datetime)
      union all select 29993,'faxe',  50,cast('20140702 20:00' as datetime),cast('20140702 22:00' as datetime)
      union all select 29992,'ggrip',  0,cast('20140702 20:00' as datetime),cast('20140703 01:45' as datetime)
     )
select count(*)
  from (select clientname
          from jobstatus 
         where endtime>dateadd(day,-1,getutcdate())
         group by clientname
         having min(statuscode)>0
       ) as t
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.12 seconds. Powered By: Snitz Forums 2000