Filling in date records

Hi:
This should be pretty simple, but I'm struggling with it. I've got
a table with two columns, Site and dtm_Date. I start with one record
for site that represents the minimum date for that site, and I want to
fill up the table with rows for every date up to the day before the
query is run. So if I start with:
Site dtm_Date
SiteA 11/27/2005 12:00:00AM
SiteB 11/29/2005 12:00:00AM
and I run my SQL Code on Dec 3, 2005, I want to end up with a table
like:
Site dtm_Date
SiteA 11/27/2005 12:00:00AM
SiteA 11/28/2005 12:00:00AM
SiteA 11/29/2005 12:00:00AM
SiteA 11/30/2005 12:00:00AM
SiteA 12/1/2005 12:00:00AM
SiteA 12/2/2005 12:00:00AM
SiteB 11/29/2005 12:00:00AM
SiteB 11/30/2005 12:00:00AM
SiteB 12/1/2005 12:00:00AM
SiteB 12/2/2005 12:00:00AMThe easiest approach is to use a Calendar table. Search the archives of this
newsgroup & you'll find several methods to create one.
Once you have it, you come up with something along the lines of :
SELECT tbl.site, Calendar.dt
FROM Calendar
LEFT OUTER JOIN tbl
ON Calendar.dt BETWEEN tbl.dtm_Date AND CURRENT_TIMESTAMP ;
Anith|||If you plan to insert data into a table, you need to have a source
for the data. In this case, a convenient source is a calendar
table containing all possible SQL Server dates. You can create the
table this way:
create table AllDates (
d datetime primary key
)
go
declare @.mindate datetime set @.mindate = '19000101'
declare @.maxdate datetime set @.maxdate = '21001231'
insert into AllDates values (@.mindate)
while @.@.rowcount > 0 begin
insert into AllDates
select d + tabsize
from AllDates, (
select datediff(day,min(d),max(d))+1 as tabsize from AllDates
) M
where d <= @.maxdate - tabsize
end
go
Once that's done, you can
INSERT INTO yourTable
SELECT yourTable.Site, AllDates.d
FROM yourTable, AllDates
WHERE AllDates.d > yourTable.dtm_Date
AND AllDates.d <= getdate() - 1
[not tested]
This assumes there is exactly one row per Site in your table,
so it will fail if you run it twice, for example.
Steve Kass
Drew University
blairjee@.gmail.com wrote:

> Hi:
> This should be pretty simple, but I'm struggling with it. I've got
> a table with two columns, Site and dtm_Date. I start with one record
> for site that represents the minimum date for that site, and I want to
> fill up the table with rows for every date up to the day before the
> query is run. So if I start with:
> Site dtm_Date
> SiteA 11/27/2005 12:00:00AM
> SiteB 11/29/2005 12:00:00AM
> and I run my SQL Code on Dec 3, 2005, I want to end up with a table
> like:
> Site dtm_Date
> SiteA 11/27/2005 12:00:00AM
> SiteA 11/28/2005 12:00:00AM
> SiteA 11/29/2005 12:00:00AM
> SiteA 11/30/2005 12:00:00AM
> SiteA 12/1/2005 12:00:00AM
> SiteA 12/2/2005 12:00:00AM
> SiteB 11/29/2005 12:00:00AM
> SiteB 11/30/2005 12:00:00AM
> SiteB 12/1/2005 12:00:00AM
> SiteB 12/2/2005 12:00:00AM
>|||I think the Calendar idea is probably a better idea, but you can accomplish
your goals with a cursor:
go
set nocount on
DECLARE @.markdate datetime ,@.site varchar(10)
DECLARE sites_cursor CURSOR
FOR
Select site from sitedate order by site desc
OPEN SITES_cursor
FETCH NEXT FROM SITES_cursor INTO @.site
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.markdate = (select min(dtm_date) from sitedate)
WHILE @.markdate < getdate()
BEGIN
Insert into sitedate values(@.site,@.markdate)
select @.markdate = dateadd(d,1,@.markdate)
END
FETCH NEXT FROM SITES_cursor into @.site
END
Close SITES_cursor
DEALLOCATE SITES_cursor
<blairjee@.gmail.com> wrote in message
news:1133804570.141750.248940@.g49g2000cwa.googlegroups.com...
> Hi:
> This should be pretty simple, but I'm struggling with it. I've got
> a table with two columns, Site and dtm_Date. I start with one record
> for site that represents the minimum date for that site, and I want to
> fill up the table with rows for every date up to the day before the
> query is run. So if I start with:
> Site dtm_Date
> SiteA 11/27/2005 12:00:00AM
> SiteB 11/29/2005 12:00:00AM
> and I run my SQL Code on Dec 3, 2005, I want to end up with a table
> like:
> Site dtm_Date
> SiteA 11/27/2005 12:00:00AM
> SiteA 11/28/2005 12:00:00AM
> SiteA 11/29/2005 12:00:00AM
> SiteA 11/30/2005 12:00:00AM
> SiteA 12/1/2005 12:00:00AM
> SiteA 12/2/2005 12:00:00AM
> SiteB 11/29/2005 12:00:00AM
> SiteB 11/30/2005 12:00:00AM
> SiteB 12/1/2005 12:00:00AM
> SiteB 12/2/2005 12:00:00AM
>