October 3, 2013 at 9:04 pm
I have a table that contains times of server backups. There are days where nothing happens so I join this table to a calendar table to create placeholders for those dates. This results in additional rows populated with a date that was missing in the serverbackup table, but having NULL values for the remaining columns. Since I have more than one server in the table, I need a date placeholder for each server that hasn't had a backup on any given day.
I don't know how to do this. I've created DDL below. Please read on.
--server backup table
CREATE TABLE [dbo].[CannedBackupJobs](
[jobid] [int] NULL,
[SizeTB] [float] NULL,
[StartTime] [datetime] NULL,
[ServerName] [varchar](20) NULL
)
--server backup data
-- As you can see, since neither Peaches nor Pears was backed up on 2013-08-05 through 2013-08-13.
insert into [dbo].[CannedBackupJobs]
values
(83, 365.226943141887,'2013-08-04 03:20:30.777', 'Peaches'),
(83, 408.830221699759, '2013-08-14 18:26:53.220', 'Peaches'),
(83, 391.654500133873, '2013-08-15 15:44:34.977', 'Peaches'),
(83, 397.063717616127, '2013-08-20 02:10:57.747', 'Peaches'),
(83, 353.803773579467, '2013-08-24 05:56:26.090', 'Peaches'),
(100, 533.226943141887,'2013-08-07 03:20:30.777', 'Pears'),
(100, 788.830221699759, '2013-08-09 18:26:53.220', 'Pears'),
(100, 351.654500133873, '2013-09-07 15:44:34.977', 'Pears'),
(100, 347.063717616127, '2013-09-09 02:10:57.747', 'Pears'),
(100, 663.803773579467, '2013-10-09 05:56:26.090', 'Pears');
--calendar look up table
CREATE TABLE _Dates (
d DATE,
PRIMARY KEY (d)
)
DECLARE @dIncr DATE = '2000-01-01'
DECLARE @dEnd DATE = '2100-01-01'
WHILE ( @dIncr < @dEnd )
BEGIN
INSERT INTO _Dates (d) VALUES( @dIncr )
SELECT @dIncr = DATEADD(DAY, 1, @dIncr )
END
Here's join. It only produces one placeholder for missing dates.
SELECT d, StartTime, ServerName, SizeTB
FROM dbo._dates d
INNER JOIN (
SELECT
DATEADD(DAY, DATEDIFF(DAY, 0, MIN(starttime)), 0) AS start_date,
DATEADD(DAY, DATEDIFF(DAY, 0, MAX(starttime)) + 1, 0) AS end_date
FROM dbo.cannedbackupjobs
) AS date_range ON
d.d >= date_range.start_date AND
d.d < date_range.end_date
LEFT OUTER JOIN cannedbackupjobs dbj ON
dbj.starttime >= d.d AND
dbj.starttime < DATEADD(DAY, 1, d.d)
Desired output to look like this. Notice on 2013-08-09, there is only one placeholder date for Peaches, none for Pears because Pears had a backup.. But elsewhere two placeholder rows for same date, for times when neither backed up.:
SELECT '2013-08-04','2013-08-04 03:20:30.777', 'Peaches', 365.226943141887 UNION ALL
SELECT '2013-08-05', NULL, 'Peaches',NULL UNION ALL
SELECT '2013-08-05', NULL, 'Pears',NULL UNION ALL
SELECT '2013-08-06', NULL, 'Peaches',NULL UNION ALL
SELECT '2013-08-06', NULL, 'Pears',NULL UNION ALL
SELECT '2013-08-07', NULL, 'Peaches',NULL UNION ALL
SELECT '2013-08-07', NULL, 'Pears',NULL UNION ALL
SELECT '2013-08-08', NULL, 'Peaches',NULL UNION ALL
SELECT '2013-08-08', NULL, 'Pears',NULL UNION ALL
SELECT '2013-08-09', NULL, 'Peaches',NULL UNION ALL
SELECT '2013-08-09','2013-08-09 18:26:53.220', 'Pears', 788.830221699759 UNION ALL
SELECT '2013-08-10', NULL, 'Peaches',NULL UNION ALL
SELECT '2013-08-10', NULL, 'Pears',NULL UNION ALL
SELECT '2013-08-11', NULL, 'Peaches',NULL UNION ALL
SELECT '2013-08-11', NULL, 'Pears',NULL UNION ALL
SELECT '2013-08-12', NULL, 'Peaches',NULL UNION ALL
SELECT '2013-08-12', NULL, 'Pears',NULL UNION ALL
SELECT '2013-08-13', NULL, 'Peaches',NULL UNION ALL
SELECT '2013-08-13', NULL, 'Pears',NULL UNION ALL
SELECT '2013-08-14', NULL, 'Peaches',NULL UNION ALL
SELECT '2013-08-14', NULL, 'Pears',NULL
Do I do this with a bunch of seperate select statements (with WHERE ServerName = Peaches and with WHERE ServerName = Pears) run against the _Dates table with a UNION ALL to recombine them? Or is there a better way?
--Quote me
October 4, 2013 at 3:04 am
You need to cross join the calendar with the list of servers, like this:
;WITH Calendar AS (
SELECT
Server_range.ServerName,
Today = d.d,
Tomorrow = DATEADD(DAY, 1, d.d)
FROM dbo._dates d
CROSS APPLY (
SELECT
CAST(MIN(starttime) AS DATE) AS Range_Start,
CAST(MAX(starttime)+1 AS DATE) AS Range_End
FROM dbo.cannedbackupjobs
) date_range
CROSS APPLY (
SELECT ServerName
FROM dbo.cannedbackupjobs
GROUP BY ServerName
) Server_range
WHERE d.d >= date_range.Range_Start
AND d.d < date_range.Range_End
)
SELECT c.Today, j.StartTime, c.ServerName, j.SizeTB
FROM Calendar c
LEFT JOIN dbo.cannedbackupjobs j
ON j.ServerName = c.ServerName
AND j.StartTime >= c.Today
AND j.StartTime < c.Tomorrow
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 4, 2013 at 7:57 am
Chris. It's Magic.
I have a lot to learn about OUTER and CROSS APPLY.
--Quote me
October 9, 2013 at 4:29 pm
Chris/anyone, will you kindly explain to me the factors that require one to use CROSS APPLY for the above 3 queries of the CTE?
--Quote me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply