April 7, 2016 at 12:34 pm
Hi,
DECLARE @minDateTime AS DATETIME;
DECLARE @maxDateTime AS DATETIME;
SET @minDateTime = (SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -6))
SET @maxDateTime = (SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
CREATE TABLE #DateHr
(
[Hour] int,
[StartTime] datetime
)
;WITH Dates_CTE
AS (SELECT @minDateTime AS Dates
UNION ALL
SELECT Dateadd(hh, 1, Dates)
FROM Dates_CTE
WHERE Dates < @maxDateTime + 1)
INSERT INTO #DateHr
(
[Hour] ,
[StartTime]
)
SELECT
DATEPART(HOUR, (FORMAT(Dates , 'MM/dd/yyyy HH:mm:ss'))) As [Hour],
ISNULL(CONVERT(DATETIME, Dates), GETDATE())
FROM Dates_CTE
OPTION (MAXRECURSION 0)
SELECT * FROM #DateHr
CREATE TABLE #JobStatus
(
ProjectName NVARCHAR(128) NOT NULL,
PackageName NVARCHAR(260) NOT NULL,
StatusDescription NVARCHAR(50) NOT NULL,
[Hour] INT NULL,
StartTime DATETIME NULL,
EndTime DATETIME NULL,
)
INSERT INTO #JobStatus ( ProjectName,PackageName,StatusDescription,Hour,StartTime,EndTime)
SELECT 'Project1','Package1.dtsx','Suceeded',23,'2016-04-04 23:02:09.000','2016-04-04 23:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Suceeded',22,'2016-04-04 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Suceeded',10,'2016-04-04 10:02:09.000','2016-04-04 10:02:11.000' UNION ALL
SELECT 'Project2','Package2.dtsx','Suceeded',0,'2016-04-04 00:02:09.000','2016-04-04 00:02:11.000' UNION ALL
SELECT 'Project2','Package2.dtsx','Suceeded',1,'2016-04-04 01:02:09.000','2016-04-04 01:02:11.000' UNION ALL
SELECT 'Project3','Package2.dtsx','Suceeded',2,'2016-04-04 02:02:09.000','2016-04-04 02:02:11.000' UNION ALL
SELECT 'Project2','Package3.dtsx','Suceeded',3,'2016-04-04 03:02:09.000','2016-04-04 03:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Suceeded',23,'2016-04-05 23:02:09.000','2016-04-05 23:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Suceeded',22,'2016-04-05 22:02:09.000','2016-04-05 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Suceeded',10,'2016-04-05 10:02:09.000','2016-04-05 10:02:11.000'
SELECT * FROM #JobStatus
--Desired : Since Package1.dtsx executed only in the 23rd,22nd and 10th hour of the 4/5 and 4/4 and rest hours it didn't , for the hours where the package didnt execute, want a 'Not-Executed' status Joining on #DateHr
SELECT 'Project1','Package1.dtsx','Suceeded',23,'2016-04-04 23:02:09.000','2016-04-04 23:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Suceeded',22,'2016-04-04 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',21,'2016-04-04 23:02:09.000','2016-04-04 23:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',20,'2016-04-04 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',19,'2016-04-04 23:02:09.000','2016-04-04 23:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',18,'2016-04-04 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',17,'2016-04-04 23:02:09.000','2016-04-04 23:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',16,'2016-04-04 23:02:09.000','2016-04-04 23:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',15,'2016-04-04 23:02:09.000','2016-04-04 23:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',14,'2016-04-04 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',13,'2016-04-04 23:02:09.000','2016-04-04 23:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',12,'2016-04-04 23:02:09.000','2016-04-04 23:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',11,'2016-04-04 23:02:09.000','2016-04-04 23:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Suceeded' ,10,'2016-04-04 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',09,'2016-04-04 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',08,'2016-04-04 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',07,'2016-04-04 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',06,'2016-04-04 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',05,'2016-04-04 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',04,'2016-04-04 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',03,'2016-04-04 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',02,'2016-04-04 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',01,'2016-04-04 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Suceeded',23,'2016-04-05 23:02:09.000','2016-04-04 23:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Suceeded',22,'2016-04-05 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',21,'2016-04-05 23:02:09.000','2016-04-04 23:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',20,'2016-04-05 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',19,'2016-04-05 23:02:09.000','2016-04-04 23:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',18,'2016-04-05 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',17,'2016-04-05 23:02:09.000','2016-04-04 23:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',16,'2016-04-05 23:02:09.000','2016-04-04 23:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',15,'2016-04-05 23:02:09.000','2016-04-04 23:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',14,'2016-04-05 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',13,'2016-04-05 23:02:09.000','2016-04-04 23:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',12,'2016-04-05 23:02:09.000','2016-04-04 23:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',11,'2016-04-05 23:02:09.000','2016-04-04 23:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Suceeded' ,10,'2016-04-05 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',09,'2016-04-05 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',08,'2016-04-05 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',07,'2016-04-05 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',06,'2016-04-05 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',05,'2016-04-05 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',04,'2016-04-05 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',03,'2016-04-05 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',02,'2016-04-05 22:02:09.000','2016-04-04 22:02:11.000' UNION ALL
SELECT 'Project1','Package1.dtsx','Not-Executed',01,'2016-04-05 22:02:09.000','2016-04-04 22:02:11.000'
DROP TABLE #JobStatus
DROP TABLE #DateHr
April 7, 2016 at 12:56 pm
select from your hours table
left join to your job status table
use CASE WHEN js.something is null then 'failed' else 'succeeded' end as jobstatus
this works because on the left join you will get back NULL from job status table for missing rows
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 7, 2016 at 1:05 pm
SELECT JS.PackageName,JS.ProjectName,JS.StartTime,JS.EndTime,CASE WHEN JS.StatusDescription is NULL THEN ' Not-Executed' ELSE JS.StatusDescription END AS StatusDescription
FROM #DateHr DHr LEFT JOIN #JobStatus JS ON DHr.Hour = JS.Hour AND Dhr.StartTime = JS.StartTime
didn't give me what I was looking for .. The other fields are all nulls.
April 7, 2016 at 2:24 pm
Add another temp table to hold the Project & Packages you want to list. Then run the SELECT below.
Btw, you should add a clustering key to the #DateHr table for efficiency, as shown in the code:
--new code at start
CREATE TABLE #Packages (
ProjectName NVARCHAR(128) NOT NULL,
PackageName NVARCHAR(260) NOT NULL
)
INSERT INTO #Packages ( ProjectName, PackageName ) SELECT 'Project1','Package1.dtsx'
--INSERT INTO #Packages SELECT DISTINCT ProjectName, PackageName FROM #JobStatus
...same code as before...
CREATE TABLE #DateHr
(
[Hour] int,
[StartTime] datetime,
UNIQUE CLUSTERED(StartTime, Hour) --<<--
)
...same code as before...
SELECT p.ProjectName, p.PackageName, ISNULL(js.StatusDescription, 'Not executed.') AS StatusDescription,
dh.Hour, ISNULL(js.StartTime, dh.StartTime) AS StartTime, js.EndTime
FROM #Packages p
CROSS JOIN #DateHr dh
LEFT OUTER JOIN #JobStatus js ON js.StartTime >= dh.StartTime AND js.StartTime < DATEADD(HOUR, 1, dh.StartTime)
DROP TABLE #JobStatus
DROP TABLE #DateHr
DROP TABLE #Packages
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply