Query help

Question

Post reply

Query help

PSB

SSChampion

Points: 13195
More actions
April 7, 2016 at 12:34 pm

#325530

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

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

PSB SSChampion Points: 13195 More actions · Answer 2

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.

ScottPletcher SSC Guru Points: 101105 More actions · Answer 3

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