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


wish to add rows having NULL values as placeholders for "missing" dates


wish to add rows having NULL values as placeholders for "missing" dates

Author
Message
polkadot
polkadot
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1096 Visits: 1114
I have a table that contains stuff happening. There's a datetime stamp column to show on what date the stuff happened. There are days where nothing happens. I want to select from this table and add rows to the result set on the fly, for those days on which nothing happened. Reason: I've got to pull it into excel and need a placeholder for those days.
For the purpose of illustration, I've created a table containing the start datetimes of all backups that have been kicked off for a server called Peaches. This table also contains the name of the server, the size of backup, and the associated jobid. On some days there are no backups eg. 8/05/2013 through 8/13/2013, BUT I still want to generate a row for those days as a placeholder.
Can you provide guidance on how to do?
Here's DDL.

CREATE TABLE [dbo].[CannedBackupJobs](
[jobid] [int] NULL,
[SizeTB] [float] NULL,
[StartTime] [datetime] NULL,
[ServerName] [varchar](20) NULL
)

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');

Mark Cowne
Mark Cowne
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: 3131 Visits: 24107
Use a calendar table

http://www.sqlservercentral.com/articles/T-SQL/70482/

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537




polkadot
polkadot
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1096 Visits: 1114
OK. I've got a dates table using.

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


I've tried to join to it with left, right, and full outer joins. Can't get the placeholder value:

select * from cannedbackupjobs dbj
full outer join _dates d on d.d = CONVERT(VARCHAR(10), dbj.starttime, 120)
where dbj.starttime >= (select min(starttime) from cannedbackupjobs);

How to join to the _dates table so that it pulls in the date, even when no value in the CannedBackupJobs table?
Mark Cowne
Mark Cowne
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: 3131 Visits: 24107
Try this

select * from _dates d
left outer join cannedbackupjobs dbj on d.d = CONVERT(VARCHAR(10), dbj.starttime, 120)
where d.d between (select min(starttime) from cannedbackupjobs) and (select max(starttime) from cannedbackupjobs);



____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537




GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88190 Visits: 45277
FROM CalenderTable LEFT OUTER JOIN TableWithOtherDatesInIt

That's the starting point. Your where clause was filtering out any nulls and essentially turning the join into an inner join.

You don't want to be doing any messy date to string conversions, they're both date columns, so just compare directly. CAST as DATE if one contains times.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


polkadot
polkadot
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1096 Visits: 1114
YIKES, I see the error. The where should be d.d>=
and not dbj.starttime >=


This works:
select * from cannedbackupjobs dbj
full outer join _dates d on d.d = CONVERT(VARCHAR(10), dbj.starttime, 120)
where d.d >= (select min(starttime) from cannedbackupjobs);


I'll look at ridding the calculations in the where clause for performance, now. I understand I don't want to be using functions against values that I am filtering for. I'll use a better calendar lookup table. Thanks!
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16430 Visits: 19554
polkadot (10/2/2013)
YIKES, I see the error. The where should be d.d>=
and not dbj.starttime >=


This works:
select * from cannedbackupjobs dbj
full outer join _dates d on d.d = CONVERT(VARCHAR(10), dbj.starttime, 120)
where d.d >= (select min(starttime) from cannedbackupjobs);


I'll look at ridding the calculations in the where clause for performance, now. I understand I don't want to be using functions against values that I am filtering for. I'll use a better calendar lookup table. Thanks!


Your best bet is to follow Gail's suggestion:

FROM CalenderTable LEFT OUTER JOIN TableWithOtherDatesInIt

Avoid referencing TableWithOtherDatesInIt in the WHERE clause - you will end up with an INNER join. Build filters into the JOIN condition instead.

“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
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7973 Visits: 7160
Use LEFT OUTER JOIN, but don't cast datetimes in any data table to dates to do comparisons; instead, use a range check.

Here's a sample join for the tables you've described:



SELECT ...
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.date >= date_range.start_date AND
d.date < date_range.end_date
LEFT OUTER JOIN cannedbackupjobs dbj ON
dbj.starttime >= d.date AND
dbj.starttime < DATEADD(DAY, 1, d.date)
--use the WHERE below instead of the INNER JOIN above if you want to
--report on just a specific date range that you specify
--WHERE d.date >= @start_date AND d.date < @end_date




SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
polkadot
polkadot
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1096 Visits: 1114
Thanks Scott, and everyone for replies & link.
polkadot
polkadot
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1096 Visits: 1114
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!
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