Populate a table by varying an input parameter

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

General SQL Server Forums

New to SQL Server Programming

Populate a table by varying an input parameter

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2011-11-03 : 09:22:05

I have a query with an input parameter (date) which I usually feed with a single input value through a web-application in order to get the values for that specific date. Now I would like to display the graph describing the behavior of that query in a given time period; to do so I need a table (table2), populated with all the values that should be displayed in the graph. Avoiding to do this manually brings me to my question.

Query: select value1, value2, @date from table1 where Date=@date

insert into table2 (VALUE1, VALUE2, DATE) values (select value1 from table1 where DATE=@date, select value2 from table1 where DATE=@date, @date)

how can I define some sort of loop in sql that allows me to populate table2 by varying the input parameter @date from lets @startdate to @enddate using the increment of @month?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-03 : 10:06:49

insert into table2 (VALUE1, VALUE2, DATE)
select value1,value2,@date from table1 where DATE=@date

Madhivanan

Failing to plan is Planning to fail

barnabeck
Posting Yak Master

236 Posts

Posted - 2011-11-03 : 10:19:58

Ok, Madhivanan this is a cooler way to do the insert command, thank you. But this is just to insert one value for @date. Is there an easy way to populate table2 by running this query in a range for @date that goes from a Startdate to an Enddate with an Increment of one week?

Hope I made my point clear.
Martin

barnabeck
Posting Yak Master

236 Posts

Posted - 2011-11-03 : 11:37:31

ok, I found out... using a function:

Create FUNCTION [dbo].[AssetGap] (@StartDate smalldatetime, @EndDate smalldatetime)
RETURNS @Query TABLE(
Fecha smalldatetime,
AssetTot real,
LedgerTot real) AS
BEGIN

DECLARE @Date smalldatetime

SET @Date = @StartDate

WHILE (@Date <= @EndDate)
BEGIN
INSERT INTO @Query
Select value1,value2,@date from table1 where TRANSDATE=@Date

SET @Date = DATEADD(mm,1,@Date)
END

RETURN
END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-14 : 07:31:55

Good that you have found out that solution

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 07:39:49

i would have done it in set based way. something like


Select value1,value2,@date from table1 
where TRANSDATE >=@startdate
AND TRANSDATE <@enddate+1
AND TRANSDATE =DATEADD(mm,DATEDIFF(mm,0,TRANSDATE),0)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources