November 22, 2010 at 4:16 am
I'm trying to join a 'list of dates' Select statement to a simple query in an attempt to find missing logged dates.
My basic database query is as follows:
SELECT JobID,
JobTimeStamp
FROM Job
My 'list of dates' created on the fly is:
SELECT DATEADD(dd, -D.rn, DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)) AS ReportDate
FROM (
SELECT TOP 30 number AS rn FROM master.dbo.spt_values
WHERE NAME is null
)AS D
My attempt at joining the two and getting some indication of 'missing dates' is:
SELECT J.JobID,
J.JobTimeStamp,
DATEADD(dd, DATEDIFF(dd,0,J.JobTimeStamp), 0) AS daypart,
E.ReportDate
FROM (
SELECT DATEADD(dd, -D.rn, DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)) AS ReportDate
FROM (
SELECT TOP 30 number AS rn FROM master.dbo.spt_values
WHERE NAME is null
)AS D) AS E
LEFT OUTER JOIN Job AS J
ON E.ReportDate = (DATEADD(dd, DATEDIFF(dd,0,J.JobTimeStamp), 0))
I'm not happy with the ON clause, can a column aliase be created and the join be made to that instead? I'm sure the code can be improved on and look forward to hearing from people!
Many thanks,
Dom
November 22, 2010 at 5:08 am
It is best to search on a range so that there are no functions on J.JobTimeStamp, making it SARGable:
-- either
SELECT DATEADD(d, DATEDIFF(d, 0, CURRENT_TIMESTAMP), - N.number) AS MissingDate
FROM [master].dbo.spt_values N
WHERE NOT EXISTS
(
SELECT *
FROM Job J
WHERE J.JobTimeStamp >= DATEADD(d, DATEDIFF(d, 0, CURRENT_TIMESTAMP), - N.number)
AND J.JobTimeStamp < DATEADD(d, DATEDIFF(d, 0, CURRENT_TIMESTAMP), - N.number + 1)
)
AND N.number BETWEEN 0 AND 29
--or
SELECT DATEADD(d, DATEDIFF(d, 0, CURRENT_TIMESTAMP), - N.number) AS MissingDate
FROM [master].dbo.spt_values N
LEFT JOIN Job J
ON J.JobTimeStamp >= DATEADD(d, DATEDIFF(d, 0, CURRENT_TIMESTAMP), - N.number)
AND J.JobTimeStamp < DATEADD(d, DATEDIFF(d, 0, CURRENT_TIMESTAMP), - N.number + 1)
WHERE J.JobTimeStamp IS NULL
AND N.number BETWEEN 0 AND 29
November 22, 2010 at 8:26 am
Hi Ken,
Many thanks for the reply however I'm not getting the results as expected. Is there a potential error with the date generation bit of the code?
The below creates numerous dates as opposed to a unique 'calendar':
SELECT DATEADD(d, DATEDIFF(d, 0, CURRENT_TIMESTAMP), - N.number) AS MissingDate
FROM [master].dbo.spt_values N
WHERE N.number BETWEEN 0 AND 29
hence, I get multiple entries for a particular missing date.
cheers,
Dom
November 22, 2010 at 8:30 am
WHERE N.type = 'P' AND N.number BETWEEN 0 AND 29
November 22, 2010 at 8:55 am
Of course. I'm unfamiliar with spt_values having used numbers tables in the past).
Many thanks,
Dom
November 23, 2010 at 8:03 am
Master.dbo.spt_values is alright for quick queries but for production code I would be inclinded to generate a Numbers table in the relevant DB.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply