November 4, 2010 at 3:19 pm
I GOT IT
CREATE TABLE #jobs
(
SubJobIDINT
, [Started]DATETIME
, [Finished] DATETIME
)
DECLARE
@SDateTime DATETIME,
@EDateTime DATETIME ;
SELECT
@SDateTime = '2010-06-04 00:00:00', --'11/4/10',
@EDateTime = '2010-06-04 23:59:59'; --GETDATE() ;
INSERT #jobs
SELECTSubJobId, [Started], [finished]
FROMdbo.cps_subJobs
WHEREstarted >= @SDateTime
ANDfinished <= @EDateTime
AND subJobTypeId IN (19, 21)
;
WITH Numbers(Number)
AS (SELECT TOP 86400
ROW_NUMBER() OVER (ORDER BY S1.OBJECT_ID) - 1
FROM
sys.columns AS S1
CROSS JOIN sys.columns AS S2) ,
Times(TSec)
AS (SELECT
DATEADD(SECOND, Number, @SDateTime)
FROM
Numbers
WHERE
Number BETWEEN 0
AND DATEDIFF(SECOND, @SDateTime, @EDateTime))
SELECT
TSec AS [Time],
(SELECT count(*)
FROM #jobs WITH(NOLOCK)
WHERE[Started] <= TSec
AND[finished] >= TSec
/* andsubJobTypeId IN (19, 21)*/) AS Jobs
FROM
Times
ORDER BY
TSec ;
DROP TABLE #jobs
November 4, 2010 at 3:43 pm
Are you sure - when I run that I don't get the expected result set that you showed earlier.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 4, 2010 at 3:48 pm
Yes!
I need to show the 86400 seconds, even in the total of jobs are 0.
the result set that I sent it was only a sample to show all of you how I need the info to be duisplayed.
Thank you for all the help and review it 🙂
November 4, 2010 at 3:52 pm
K - Great.
Good luck
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply