How to populate dates from one table to another

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 2000 Forums

SQL Server Development (2000)

How to populate dates from one table to another

Author

Topic

emyk
Yak Posting Veteran

57 Posts

Posted - 2011-12-27 : 14:26:25

I am joining three tables (Full Outer Join) by dates, and if a date entry is missing from one table it is leaving out usefull data for the missing dates. My query is driven by a date range (start date and end date). Is it possible to have the joint query return a date regardless of a date available in the table?

Example: @startdate = '20111201' and @enddate = '20111207' (date ranges are variables)
and Table1 does not have a date for '20111203', how do i use a CASE type scenario where it returns date '20111203' when not available in table1. Some how I should be able to display the missing date.

Any direction or assistant greatly appreciated.

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-27 : 15:13:37

Probably the simplest method is to do a LEFT JOIN to the Calendar table. The Calendar table would have all the dates you are interested in. If you have a numbers table or calendar table you can use that; if not you can create one on the fly like this (and insert it into a temporary calendar table):

DECLARE @startdate DATETIME, @days INT;

SET @startdate = '20111201';
SET @days = 10;

SELECT
	DATEADD(dd,number,@startdate)
FROM
	MASTER..spt_values
WHERE
	[type] = 'P' AND number < @days;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 00:49:33

here's a function you can use to generate calendar table

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

emyk
Yak Posting Veteran

57 Posts

Posted - 2011-12-28 : 13:48:59

I got the desired date range in a temporary table.
DateTbl
&date1
&date2
&date3

Now I am trying to use this dates to do a select from TABLE1. IF &date* is missing on TABLE1, I need to insert the missing &date* into TABLE1.

Here is the logic I am working on (It did not like the inner join)

IF NOT EXIST
(SELECT AB.date, CD.date
FROM #TABLE1 AB
INNER JOIN #DateTbl CD
ON CD.date = AB.date
WHERE AB.date = CD.date)
INSERT INTO #TABLE1
VALUES ('CD.date','AB.id', 'AB.role_id', 'AB.qty')

Example (given date range 20111201 (@st) and 20111202 (@ed)
#Table1
col1 col2 col3
20111201 1 40
#DateTbl
col1
20111201
20111202

Desired Table:
#Table1
col1 col2 col3
20111201 1 40
20111202 1 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-29 : 00:44:12

[code]
INSERT INTO #Table1
SELECT d.col1,1,0
FROM #DateTbl d
WHERE NOT EXISTS(SELECT 1 FROM #Table1 WHERE col1 = d.col1)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

emyk
Yak Posting Veteran

57 Posts

Posted - 2011-12-29 : 11:40:48

Thank you visakh16 that worked perfect.

I need to retrieve the value for the second column for the insert (SELECT d.col1,1,0) from #Table1 col2. I Can do this with another Update statement, but is there any other efficient way to incorporate the logic under the existing SQL?

INSERT INTO #Table1
SELECT d.col1,1,0 ---->instead of hardcoding 1 I need to get this from #TABLE1
FROM #DateTbl d
WHERE NOT EXISTS(SELECT 1 FROM #Table1 WHERE col1 = d.col1)

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources