Adding records in between dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Adding records in between dates

Author

Topic

rohcky
Starting Member

38 Posts

Posted - 2007-10-19 : 08:55:25

Is it possible to fill in missing dates dynamically in SQL Server? If I had records

Date
----------
10/3/2007
10/4/2007
10/9/2007

I want a way to add records

10/5/2007
10/6/2007
10/7/2007
10/8/2007

dynamically.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-19 : 09:08:28

If you're trying to show that there is nothing for those days, you're better off with a calendar table that has all the dates and then left joining it to your tables.
e.g.,
SELECT t1.LName,t1.FName,t2.DATE
FROM
DateTable t2
LEFT JOIN
yourtable t1
ON
t2.date = t1.date

Jim

rohcky
Starting Member

38 Posts

Posted - 2007-10-19 : 09:31:47

I'm actually trying to fill in data to get inventory on a daily basis.

Right now it looks like:

Date Inv
----------------
10/3/2007 10
10/4/2007 8
10/9/2007 6

What I want to do is fill in the missing dates and add the inv amount from the previous date (which I already know how to procure).

Date Inv
----------------
10/3/2007 10
10/4/2007 8
10/5/2007 8
10/6/2007 8
10/7/2007 8
10/8/2007 8
10/9/2007 6

The problem with left joining to a calendar table is the function used to fill in the missing inventory produces incorrect when the records start off with a null inventory.

Date Inv
----------------
10/1/2007
10/2/2007
10/3/2007 10
10/4/2007 8
10/5/2007
10/6/2007
10/7/2007
10/8/2007
10/9/2007 6

It will not give me the correct results because there shouldn't be records for 10/1 and 10/2.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources