Report Containing Sequential Dates (removing date gaps)

christopher.secord

I have a table containing typed log entries. One log entry is supposed
to be created every twelve hours, but sometimes there are gaps. I need
to create a report showing the time of entry, and the actual log entry.
I can't just list the contents of the log table, because if I do that
there will be dates missing. Instead, when there isn't a log entry for
a date, I need to print the date, and then just leave the log entry
blank.

The SQL bellows shows what the output should look like. HOWEVER, the
code below makes use of a temp table containing all possible dates. My
question is, is there a better way to do this - one that doesn't
involve the temp table? Thanks in advance.
create table StationLog (LogDate datetime, LogText char(11))
insert StationLog values ('1/1/2005 00:00:00','entry one')
insert StationLog values ('1/1/2005 12:00:00','entry two')
insert StationLog values ('1/2/2005 00:00:00','entry three')
insert StationLog values ('1/3/2005 00:00:00','entry four')

create table Date_List (TempDate datetime)
insert Date_List values ('1/1/2005 00:00:00')
insert Date_List values ('1/1/2005 12:00:00')
insert Date_List values ('1/2/2005 00:00:00')
insert Date_List values ('1/2/2005 12:00:00')
insert Date_List values ('1/3/2005 00:00:00')
insert Date_List values ('1/3/2005 12:00:00')

select TempDate, LogText
from Date_List
left outer join StationLog on Date_List.TempDate = StationLog.LogDate

drop table StationLog
drop table Date_List

Jul 23 '05 #1

Ray

Basically your've got it. I would suggest creating two permanent tables one
of dates and another for hours in a day. Create date rows for a few years
back and several years forward and using it going forward instead of
creating and dropping each time.

Also adding other associated values like for each day what month, day of
week, day of month, company holiday, etc type information usually comes in
handy sooner or later.

<ch****************@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

I have a table containing typed log entries. One log entry is supposed
to be created every twelve hours, but sometimes there are gaps. I need
to create a report showing the time of entry, and the actual log entry.
I can't just list the contents of the log table, because if I do that
there will be dates missing. Instead, when there isn't a log entry for
a date, I need to print the date, and then just leave the log entry
blank.

The SQL bellows shows what the output should look like. HOWEVER, the
code below makes use of a temp table containing all possible dates. My
question is, is there a better way to do this - one that doesn't
involve the temp table? Thanks in advance.
create table StationLog (LogDate datetime, LogText char(11))
insert StationLog values ('1/1/2005 00:00:00','entry one')
insert StationLog values ('1/1/2005 12:00:00','entry two')
insert StationLog values ('1/2/2005 00:00:00','entry three')
insert StationLog values ('1/3/2005 00:00:00','entry four')

create table Date_List (TempDate datetime)
insert Date_List values ('1/1/2005 00:00:00')
insert Date_List values ('1/1/2005 12:00:00')
insert Date_List values ('1/2/2005 00:00:00')
insert Date_List values ('1/2/2005 12:00:00')
insert Date_List values ('1/3/2005 00:00:00')
insert Date_List values ('1/3/2005 12:00:00')

select TempDate, LogText
from Date_List
left outer join StationLog on Date_List.TempDate = StationLog.LogDate

drop table StationLog
drop table Date_List

Jul 23 '05 #2

dbmonitor

ch****************@gmail.com wrote:

I have a table containing typed log entries. One log entry is supposed to be created every twelve hours, but sometimes there are gaps. I need to create a report showing the time of entry, and the actual log entry. I can't just list the contents of the log table, because if I do that there will be dates missing. Instead, when there isn't a log entry for a date, I need to print the date, and then just leave the log entry
blank.

The SQL bellows shows what the output should look like. HOWEVER, the
code below makes use of a temp table containing all possible dates. My question is, is there a better way to do this - one that doesn't
involve the temp table? Thanks in advance.
create table StationLog (LogDate datetime, LogText char(11))
insert StationLog values ('1/1/2005 00:00:00','entry one')
insert StationLog values ('1/1/2005 12:00:00','entry two')
insert StationLog values ('1/2/2005 00:00:00','entry three')
insert StationLog values ('1/3/2005 00:00:00','entry four')

create table Date_List (TempDate datetime)
insert Date_List values ('1/1/2005 00:00:00')
insert Date_List values ('1/1/2005 12:00:00')
insert Date_List values ('1/2/2005 00:00:00')
insert Date_List values ('1/2/2005 12:00:00')
insert Date_List values ('1/3/2005 00:00:00')
insert Date_List values ('1/3/2005 12:00:00')

select TempDate, LogText
from Date_List
left outer join StationLog on Date_List.TempDate = StationLog.LogDate

drop table StationLog
drop table Date_List

You could create a temporary table with the required dates very easily
using the following code:
--------------------------------
declare @startdate datetime
set @startdate = '2005-03-04 12:00:00'

select identity(int,0,-12) as hours
into #tmphours
from sysobjects

select dateadd(hh, hours, @startdate)
from #tmphours
order by 1
-------------------------------

Once you have your base table (#tmphours), use an outer join to your
station log table.

--
Cheers
David Rowland
http://dbmonitor.tripod.com

Jul 23 '05 #3

Ross Presser

On 3 Mar 2005 15:06:40 -0800, ch****************@gmail.com wrote:

The SQL bellows shows what the output should look like. HOWEVER, the
code below makes use of a temp table containing all possible dates. My
question is, is there a better way to do this - one that doesn't
involve the temp table? Thanks in advance.

Instead of a temp table, you could create the needed dates on the fly:

CREATE VIEW Digits (NUM)
AS
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9
GO

SELECT TempDate, LogText
FROM StationLog
RIGHT OUTER JOIN
(
SELECT dateadd(hour, 6 * NUM,
(select min(LogDate) FROM StationLog)) AS TempDate
FROM (
SELECT D1.NUM + 10* D10.NUM + 100* D100.NUM AS NUM
FROM DIGITS D1, DIGITS D10, DIGITS D100
) AS NUMBERS
WHERE NUM <= datediff(hour,
(select min(LogDate) FROM StationLog),
(select max(LogDate) FROM StationLog)) / 6
) AS Date_List
ON StationLog.LogDate = Date_List.TempDate

here's the output:

TempDate LogText
------------------------------------------------------ -----------
2005-01-01 00:00:00.000 entry one
2005-01-01 06:00:00.000 NULL
2005-01-01 12:00:00.000 entry two
2005-01-01 18:00:00.000 NULL
2005-01-02 00:00:00.000 entry three
2005-01-02 06:00:00.000 NULL
2005-01-02 12:00:00.000 NULL
2005-01-02 18:00:00.000 NULL
2005-01-03 00:00:00.000 entry four

Jul 23 '05 #4

christopher.secord

That's very slick. Is it ok to use sysobjects like that? On the
database I'm using, sysobjects contains 1393 rows, so that means I
could get about 700 days worth of data before I'd run into problems.

Jul 23 '05 #5

dbmonitor

If you use sysobjects twice with no join, then you will get 1393*1393
rows!

i.e.
FROM sysobjects,
sysobjects

There is no reason why you can't use this table at all.

Jul 23 '05 #6

dbmonitor

You could also use the select top n clause to limit the output to only
the number of rows you want

Jul 23 '05 #7