Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
38200 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
38200 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
30421 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
Aged Yak Warrior

545 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  
 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.09 seconds. Powered By: Snitz Forums 2000