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
>

Filegroup question

I was reading an article about the use of filegroups. I had alsways thought
that multiple files/ filegroups were pretty much pointless unless they were
on they're own raid array? However this article seems to disagree with that
theory and say that multiple files on the same disk array could improve
performance? Whats your take on this? And if its true, why not just split
your whole db into multiple files on the same disk array? Heres a snippet of
the article:
/*
If your database is very large and very busy, multiple files can be used to
increase performance. Here is one example of how you might use multiple
files. Let's say you have a single table with 10 million rows that is heavily
queried. If the table is in a single file, such as a single database file,
then SQL Server would only use one thread to perform a read of the rows in
the table. But if the table were divided into three physical files (all part
of the same filegroup), then SQL Server would use three threads (one per
physical file) to read the table, which potentially could be faster. In
addition, if each file were on its own separate physical disk or disk array,
the performance gain would even be greater.
*?
SQL will in different circusmstances be more aggresive in IO based on the
number of files. Is that good or bad if all the files are on the same array?
Well, like any performance question, the answer is it depends. <g>
You might get better performance this way if the 'single array' is a super
duper fast array on an EMC SAN. You might get worse performance if you're
on a low end arrary.
However, there are other cases where you might want multiiple files, on the
same array, irregardless of IO performance.
Take a look at this article...
http://www.windowsitpro.com/Article/...615/40615.html
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B17EA0E2-638C-45CD-B160-C9E304549ADB@.microsoft.com...
> I was reading an article about the use of filegroups. I had alsways
thought
> that multiple files/ filegroups were pretty much pointless unless they
were
> on they're own raid array? However this article seems to disagree with
that
> theory and say that multiple files on the same disk array could improve
> performance? Whats your take on this? And if its true, why not just split
> your whole db into multiple files on the same disk array? Heres a snippet
of
> the article:
> /*
> If your database is very large and very busy, multiple files can be used
to
> increase performance. Here is one example of how you might use multiple
> files. Let's say you have a single table with 10 million rows that is
heavily
> queried. If the table is in a single file, such as a single database file,
> then SQL Server would only use one thread to perform a read of the rows in
> the table. But if the table were divided into three physical files (all
part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster. In
> addition, if each file were on its own separate physical disk or disk
array,
> the performance gain would even be greater.
> *?

Filegroup question

I was reading an article about the use of filegroups. I had alsways thought
that multiple files/ filegroups were pretty much pointless unless they were
on they're own raid array? However this article seems to disagree with that
theory and say that multiple files on the same disk array could improve
performance? Whats your take on this? And if its true, why not just split
your whole db into multiple files on the same disk array? Heres a snippet of
the article:
/*
If your database is very large and very busy, multiple files can be used to
increase performance. Here is one example of how you might use multiple
files. Let's say you have a single table with 10 million rows that is heavil
y
queried. If the table is in a single file, such as a single database file,
then SQL Server would only use one thread to perform a read of the rows in
the table. But if the table were divided into three physical files (all part
of the same filegroup), then SQL Server would use three threads (one per
physical file) to read the table, which potentially could be faster. In
addition, if each file were on its own separate physical disk or disk array,
the performance gain would even be greater.
*?SQL will in different circusmstances be more aggresive in IO based on the
number of files. Is that good or bad if all the files are on the same array?
Well, like any performance question, the answer is it depends. <g>
You might get better performance this way if the 'single array' is a super
duper fast array on an EMC SAN. You might get worse performance if you're
on a low end arrary.
However, there are other cases where you might want multiiple files, on the
same array, irregardless of IO performance.
Take a look at this article...
http://www.windowsitpro.com/Article...0615/40615.html
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B17EA0E2-638C-45CD-B160-C9E304549ADB@.microsoft.com...
> I was reading an article about the use of filegroups. I had alsways
thought
> that multiple files/ filegroups were pretty much pointless unless they
were
> on they're own raid array? However this article seems to disagree with
that
> theory and say that multiple files on the same disk array could improve
> performance? Whats your take on this? And if its true, why not just split
> your whole db into multiple files on the same disk array? Heres a snippet
of
> the article:
> /*
> If your database is very large and very busy, multiple files can be used
to
> increase performance. Here is one example of how you might use multiple
> files. Let's say you have a single table with 10 million rows that is
heavily
> queried. If the table is in a single file, such as a single database file,
> then SQL Server would only use one thread to perform a read of the rows in
> the table. But if the table were divided into three physical files (all
part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster. In
> addition, if each file were on its own separate physical disk or disk
array,
> the performance gain would even be greater.
> *?

Filegroup question

I was reading an article about the use of filegroups. I had alsways thought
that multiple files/ filegroups were pretty much pointless unless they were
on they're own raid array? However this article seems to disagree with that
theory and say that multiple files on the same disk array could improve
performance? Whats your take on this? And if its true, why not just split
your whole db into multiple files on the same disk array? Heres a snippet of
the article:
/*
If your database is very large and very busy, multiple files can be used to
increase performance. Here is one example of how you might use multiple
files. Let's say you have a single table with 10 million rows that is heavily
queried. If the table is in a single file, such as a single database file,
then SQL Server would only use one thread to perform a read of the rows in
the table. But if the table were divided into three physical files (all part
of the same filegroup), then SQL Server would use three threads (one per
physical file) to read the table, which potentially could be faster. In
addition, if each file were on its own separate physical disk or disk array,
the performance gain would even be greater.
*?SQL will in different circusmstances be more aggresive in IO based on the
number of files. Is that good or bad if all the files are on the same array?
Well, like any performance question, the answer is it depends. <g>
You might get better performance this way if the 'single array' is a super
duper fast array on an EMC SAN. You might get worse performance if you're
on a low end arrary.
However, there are other cases where you might want multiiple files, on the
same array, irregardless of IO performance.
Take a look at this article...
http://www.windowsitpro.com/Article/ArticleID/40615/40615.html
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B17EA0E2-638C-45CD-B160-C9E304549ADB@.microsoft.com...
> I was reading an article about the use of filegroups. I had alsways
thought
> that multiple files/ filegroups were pretty much pointless unless they
were
> on they're own raid array? However this article seems to disagree with
that
> theory and say that multiple files on the same disk array could improve
> performance? Whats your take on this? And if its true, why not just split
> your whole db into multiple files on the same disk array? Heres a snippet
of
> the article:
> /*
> If your database is very large and very busy, multiple files can be used
to
> increase performance. Here is one example of how you might use multiple
> files. Let's say you have a single table with 10 million rows that is
heavily
> queried. If the table is in a single file, such as a single database file,
> then SQL Server would only use one thread to perform a read of the rows in
> the table. But if the table were divided into three physical files (all
part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster. In
> addition, if each file were on its own separate physical disk or disk
array,
> the performance gain would even be greater.
> *?