Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help building query, please Expand / Collapse
Author
Message
Posted Thursday, October 3, 2013 9:04 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 5:45 PM
Points: 283, Visits: 758
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?
Post #1501404
Posted Friday, October 4, 2013 3:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:50 AM
Points: 7,191, Visits: 13,645
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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1501472
Posted Friday, October 4, 2013 7:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 5:45 PM
Points: 283, Visits: 758
Chris. It's Magic.
I have a lot to learn about OUTER and CROSS APPLY.
Post #1501606
Posted Wednesday, October 9, 2013 4:29 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 5:45 PM
Points: 283, Visits: 758
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?
Post #1503322
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse