To pick the rows one by one in a variable

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)

To pick the rows one by one in a variable

Author

Topic

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-04-08 : 10:51:08

I have a table1 with two datetime columns - StartDate and EndDate and few other columns like ID, etc.

I want to use all the datetimes that are in the startdate column and the enddate column in the following query which I am planning to use in the Execute SQL Task.

SELCT * FROM TableName
WHERE StartDate between '2008-05-07 00:00:00.000' and '2008-05-07 08:30:00.000'

Now, in this query, where there is StartDate between '2008-05-07 00:00:00.000' and '2008-05-07 08:30:00.000'

I want to use table1 to get the values. The values should be stored in a variable and one by one the values should be selected and placed in place of these dates.

Which task or container will help me to loop through these rows and get the dates. If you answer is the ForeachLoopContainer, then please let me know the steps also. I am not good at using ForeachLoops, so.

If you do not understand my question, please do let me know. I am kind of stuck in this issue, so please help me if you can.

I had posted the same question in SQL Team SSIS forum thinking SSIS can help, but it did not work out. Hope T-SQL works.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-04-08 : 11:06:59

[code]
Select b.*
from
Table1 a
cross apply
(Select * from Table2 aa where aa.Mydate between a.startdate and a.Enddate) b
[/code]

it is hard to follow exactly what you are looking for, but the above query will take the results from table 1 and run them against table2 if that is what you are asking.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-04-08 : 11:13:46

quote:
Originally posted by Vinnie881

Select b.*
from
Table1 a
cross apply
(Select * from Table2 aa where aa.Mydate between a.startdate and a.Enddate) b

it is hard to follow exactly what you are looking for, but the above query will take the results from table 1 and run them against table2 if that is what you are asking.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Thank you for your reply, but I guess my question is not clear to you. I do not have two tables at all. I just have one table which has three columns in it:
ID, StartDate and EndDate.

SELCT * FROM TableName
WHERE StartDate between '2008-05-07 00:00:00.000' and '2008-05-07 08:30:00.000'

I have all:

StartDates
2009-04-08 00:00:00.000
2009-04-07 00:00:00.000
2009-04-06 00:00:00.000
2009-04-05 00:00:00.000
2009-04-04 00:00:00.000
2009-04-03 00:00:00.000
2009-04-02 00:00:00.000
2009-04-01 00:00:00.000
2009-03-31 00:00:00.000
2009-03-30 00:00:00.000
2009-03-29 00:00:00.000

EndDates
2009-04-08 08:30:00.000
2009-04-07 08:30:00.000
2009-04-06 08:30:00.000
2009-04-05 08:30:00.000
2009-04-04 08:30:00.000
2009-04-03 08:30:00.000
2009-04-02 08:30:00.000
2009-04-01 08:30:00.000
2009-03-31 08:30:00.000
2009-03-30 08:30:00.000
2009-03-29 08:30:00.000

I want to use this query again and again for all the different startdates and enddates that are in the table. I mean, in one package, in one task itself, I want to use this query but with different dates as specified in the table.

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-08 : 23:43:50

Hi,
I am not clear with yr problem.
pls provide test data and expected result.
That will help me to answer
Kunal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources