Fill table with date range query result

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

Transact-SQL (2000)

Fill table with date range query result

Author

Topic

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2007-09-12 : 07:45:44

Hello,

I am wondering if you guys can help me on this problem. I am trying to fill a month's worth of data that derived an output. So here's the scenario.

Query: (select mydate from timesheet where mydate between 6/15/2007 and 6/19/2007)

MYDATE
=====
6/15/2007
6/18/2007
6/19/2007

I would like full month filled before and after date output. So the final output would be:

MYDATE
=====
6/1/2007
...
6/15/2007
6/18/2007
6/19/2007
...
6/30/2007

I appreciate any help and thank you.

RO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-12 : 07:56:29

INSERT TimeSheet (Mydate)
SELECT Date FROM F_TABLE_DATE('20070101', '20071231') AS d
WHERE NOT EXISTS (SELECT * FROM TimeSheet AS t WHERE t.MyDate = d.Date)

E 12°55'05.25"
N 56°04'39.16"

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-12 : 07:57:48

Something like this. Be aware I did not check the syntax for errors.

Create a tally table as seen in this post
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25685

This formula will get you the first day of the month 6/1/2007
select dateadd(m,0,datediff(m,0,'6/15/2007'))

This formula will get you the last day of the month
select dateadd(d,-1,dateadd(m,1,datediff(m,0,'6/15/2007')))

select dateadd(d,t.id,dateadd(m,0,datediff(m,0,'6/15/2007')))
from
tally t
where
t.id < 32 and
dateadd(d,t.id,dateadd(m,0,datediff(m,0,'6/15/2007'))) between
dateadd(m,0,datediff(m,0,'6/15/2007')) and
dateadd(d,-1,dateadd(m,1,datediff(m,0,'6/15/2007')))

PS: if you are looking for a SQL position in Connecticut with excellent pay please send me a resume to ValterBorges@msn.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-12 : 08:27:55

quote:
Originally posted by Peso

INSERT TimeSheet (Mydate)
SELECT Date FROM F_TABLE_DATE('20070101', '20071231') AS d
WHERE NOT EXISTS (SELECT * FROM TimeSheet AS t WHERE t.MyDate = d.Date)

E 12°55'05.25"
N 56°04'39.16"

OP would reply "I am getting the following error"

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'F_TABLE_DATE'.

Madhivanan

Failing to plan is Planning to fail

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2007-09-12 : 08:31:29

Madhivanan, I figured that out and added the function to the DB. Thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-12 : 08:32:53

quote:
Originally posted by Peso

INSERT TimeSheet (Mydate)
SELECT Date FROM F_TABLE_DATE('20070101', '20071231') AS d
WHERE NOT EXISTS (SELECT * FROM TimeSheet AS t WHERE t.MyDate = d.Date)

E 12�05.25"
N 56�39.16"

Peso -- it might to helpful to explain what F_TABLE_DATE is and where to get it when you provide solutions using that function ...

- Jeff
http://weblogs.sqlteam.com/JeffS

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2007-09-12 : 08:42:21

Guys thanks for the help so far and you've very helpful. May I add another twist to this which probably a whole different topic. What if I need dates to be updated based on repeated date and needs it to be chronological?

MYDATE
=====
6/15/2007
6/18/2007
6/19/2007
6/15/2007
6/18/2007
6/19/2007

So the final output would be:

MYDATE
=====
6/1/2007
6/2/2007
6/3/2007
6/4/2007
6/5/2007
6/6/2007
...
6/30/2007

I am basically inserting the first result set into a temp table and duplicate it twice. There are more fields than just MYDATE but the not critical that they repeat. All I care about is to fill a month's worth of data based on my initial output. Sorry for twisting this from its original question. Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources