When I join to the Dates table, I am now successful in creating placeholders for those dates missing. The values for all the columns from the CannedBackupJobs table are NULL, wherever there is no data for a specific date.....
..............which would be fine if there were ever only one servername.
My fault for not providing/ or thinking about the additional layer of the problem. The DDL I provided should have contained rows with dates for backups that were done for more than one server eg Peaches & Pears.
New DDL INSERT statement
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');
Each server has it's own unique history of backups, against a continuum of time, sometimes sharing dates with other servers, on which no backups occurred, sometimes not. On those days where neither server was backed up, I need the same date placeholder specifically for Peaches and another for Pears.
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?
Thanks much in advance!
--Quote me