SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help building query, please


Help building query, please

Author
Message
polkadot
polkadot
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3050 Visits: 1257
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
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41024 Visits: 20000
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
polkadot
polkadot
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3050 Visits: 1257
Chris. It's Magic.
I have a lot to learn about OUTER and CROSS APPLY.

--Quote me
polkadot
polkadot
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3050 Visits: 1257
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search