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

count

Author

Topic

M_henriksen
Starting Member

5 Posts

Posted - 2014-07-02 : 15:47:02

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

38200 Posts

Posted - 2014-07-02 : 15:52:34

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/

M_henriksen
Starting Member

5 Posts

Posted - 2014-07-02 : 16:01:20

Hi Tara.
sorry for the confusion "clientname" is servername

Br Morten

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-02 : 16:17:39

;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/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-02 : 16:21:09

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-02 : 16:24:51

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())

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-02 : 16:53:31

[code]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;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

M_henriksen
Starting Member

5 Posts

Posted - 2014-07-03 : 06:51:58

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-03 : 11:25:15

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

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-07-03 : 12:40:11

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources