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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy