Hi all,
I have a bit of a challenging on my hands with this,
i have a result set in a table which i am trying to iterate through the rows one by one i.e
Values
Values Dates
1.10
1.20
1.30
1.40
2006-03-31 00:00:00.000
2006-06-30 00:00:00.000
2006-09-30 00:00:00.000
2006-12-31 00:00:00.000
go to each row get the value and the date perform some calculation with the value and the date and insert it into another table(which sounds easy enough) my problem is how do i iterate through the result set one by one without using a cursor or a while loop?
do i have any other options at all?
Shadow:
You can take advantage of the explicit values you have in your "Values" column and use that as an in situ iterator table. For example:
set nocount on
declare @.quarter table
( quarter# numeric (9,2) not null,
qtrDate datetime not null
)insert into @.quarter values (1.10, '2006-03-31 00:00:00.000')
insert into @.quarter values (1.20, '2006-06-30 00:00:00.000')
insert into @.quarter values (1.30, '2006-09-30 00:00:00.000')
insert into @.quarter values (1.40, '2006-12-31 00:00:00.000')declare @.mockData table
( mock_id integer not null,
mock_dt datetime not null,
mock_value float not null
)insert into @.mockData values (1, '1/14/6 1:00', 25.4)
insert into @.mockData values (2, '2/15/6 2:00', 17.3)
insert into @.mockData values (3, '5/16/6 5:00', 4.3)
insert into @.mockData values (4, '7/5/6 6:00', 7.7)
insert into @.mockData values (5, '9/30/6 23:00', 96.6)
insert into @.mockData values (6, '10/7/6', 14.5)select iter,
qtrDate,
sum (mock_value)
from ( select convert (int, 10*(quarter# - convert (integer, quarter#))) as iter,
qtrDate
from @.quarter
) qtr
inner join @.mockData d
on mock_dt < dateadd (day, 1, qtrDate)
group by iter, qtrDate
An alternative is to establish an explicit iterator table in your database. Such as:
create table dbo.SMALL_ITERATOR
( iter smallint not null
constraint PK_SMALL_ITERATOR primary key
)
I populate my iterator tables with values 1-32767.
|||It seems what you need is a straight set operation in SQL. Post the caculation you want to operate on the result set.Dave
I think you can do this in a SELECT statement , then INTO another new table with the caculated results.|||
Hi Dave thanks for the solution but iam not quite sure how the above works I can see you have declared another table 'mock table ' and given it like an identity field, what is the select statement:
select iter,
qtrDate,
sum (mock_value)
from ( select convert (int, 10*(quarter# - convert (integer, quarter#))) as iter,
qtrDate
from @.quarter
) qtr
inner join @.mockData d
on mock_dt < dateadd (day, 1, qtrDate)
group by iter, qtrDate
supposed to be doing?
|||Hi thanks for your reply, but i am not sure how you mean could you give me an example with my table above on how to do this ? imagin that the values in each row some how breaks up into 3 (calc) and the valuedate for that row has been calculated into three seperate months....how do you then do it in a straight set as you have suggested?
|||Sorry that I rushed this, Shadow. I needed to get to a meeting so I rushed.
|||Hello:Dave
-- --
-- First, let's load your quarterly values into a table.
-- --
set nocount on
declare @.quarter table
( quarter# numeric (9,2) not null,
qtrDate datetime not null
)insert into @.quarter values (1.10, '2006-03-31 00:00:00.000')
insert into @.quarter values (1.20, '2006-06-30 00:00:00.000')
insert into @.quarter values (1.30, '2006-09-30 00:00:00.000')
insert into @.quarter values (1.40, '2006-12-31 00:00:00.000')-- --
-- Now lets show it; notice that we can leverage the fractional part of the
-- first data element and use that as an iterator from 1-4.
-- --
select convert (int, 10*(quarter# - convert (integer, quarter#))) as iter,
quarter#,
qtrDate
from @.quarter-- --
-- Some arbitrary data that can be partitioned by date
-- --
declare @.mockData table
( mock_id integer not null,
mock_dt datetime not null,
mock_value float not null
)insert into @.mockData values (1, '1/14/6 1:00', 25.4)
insert into @.mockData values (2, '2/15/6 2:00', 17.3)
insert into @.mockData values (3, '5/16/6 5:00', 4.3)
insert into @.mockData values (4, '7/5/6 6:00', 7.7)
insert into @.mockData values (5, '9/30/6 23:00', 96.6)
insert into @.mockData values (6, '10/7/6', 14.5)-- --
-- Now, we can use the defactor iterator that came from the data -- in this
-- case the "inner select." The scan will now make 4 passes through the
-- mock data and generate a sum for each pass.
--
-- What I am showing here is YTD sum of the "mock_value" field. Note that if
-- we would want to show this value for only a quarter that it would be best
-- to modify our @.quarter table to include a data range instead of a simple
-- date. Also notice the "DATEADD". This is because the "time" that is
-- part of the 9/30 record puts this record technically past the 9/30 EOQ date
-- --
select iter,
qtrDate,
sum (mock_value)
from ( select convert (int, 10*(quarter# - convert (integer, quarter#))) as iter,
qtrDate
from @.quarter
) qtr
inner join @.mockData d
on mock_dt < dateadd (day, 1, qtrDate)
group by iter, qtrDate-- --
-- OK, the derived table is not necessary; a straight-up join in this case
-- is better. I hurried because I had a meeting to go to -- you know, a work
-- break. The "convert" feature is not necessary unless you need the
-- enumerations.
-- --
select convert (int, 10*(quarter# - convert (integer, quarter#))) as iter,
qtrDate,
sum (mock_value)
from @.quarter
inner join @.mockData d
on mock_dt < dateadd (day, 1, qtrDate)
group by convert (int, 10*(quarter# - convert (integer, quarter#))), qtrDate
You can post a few records for your first table, then describe how you want your calculation done, then post the result you want in another table. In this way, it will hep to work out a SQL solution which may use a set operation, instead of the loop technique you are looking for.
Thanks.|||
Hi Thanks,
For your reply below is basically what i am trying to do the process is almost the same for my monthly and quaterly table except for the calculations in my quarterly table this is a detailed process below...
Basically i want to iterate through the top table getting each row and then expanding it into another table that represents a month and inserting it into a final table that may or may not have those dates essentially doing and insert( if the consecutive dates do not exist, hence create them in the final and then add in the data ) or an update (if the consecutive dates exists) in the final..
..for simplicity i have created all tables involved, but in reality the final table is not a temp table and exists already
--This is the Sample Monthly Data Table that needs to be iterated through..That i dont want to use a cursor for or a while loop
if Object_id('tempdb..#MonthlyTable') IS NOT NULL
Drop Table #MonthlyTable
Create table #MonthlyTable(MonthlyIndexCode varchar(10), MonthlyIndexLevel float, MonthlyValueDate datetime)
Insert Into #MonthlyTable
(MonthlyIndexCode, MonthlyIndexLevel, MonthlyValueDate)
Select 'Test1' , 1.50 , '20060131' --Jan
Union
Select 'Test2' , 2.50 , '20060228' --Feb
Union
Select 'Test3' , 8.50 , '20060331' --Mar
Union
Select 'Test4' , 4.50 , '20060430' --Apr
Union
Select 'Test5' , 5.50 , '20060531' --May
Union
Select 'Test6' , 6.50 , '20060630' --Jun
Union
Select 'Test7' , 5.50 , '20060731' --Jul
Union
Select 'Test8' , 8.50 , '20060831' --Aug
Union
Select 'Test9' , 9.50 , '20060930' --Sep
Union
Select 'Test10' , 10.50 , '20061031' --Oct
Union
Select 'Test11' , 11.50 , '20061130' --Nov
Union
Select 'Test12' , 12.50 , '20061231' --Dec
--Check table
--Select * from #MonthlyTable Order by MonthlyValueDate
--START PROCESS: FOR EACH ROW IN THE ABOVE #MONTHLY TABLE
--For each record in the above Table decompound each record as it represents data for a whole month (for that records ValueDate)
Declare @.StartDate datetime,@.TempStartDate Datetime, @.EndDate datetime
set @.StartDate = '20060131'
--derive the MonthEndDate from the StartDate(make sure the start date is corrected to its end of the month)
set @.EndDate = DateAdd(Month, 1, dbo.GetMonthendDate(@.StartDate) )
if Object_id('tempdb..#FullMonthlyTable') IS NOT NULL
Drop Table #FullMonthlyTable
Create table #FullMonthlyTable(IndexCode varchar(10), IndexLevel float, ValueDate datetime)
--Insert the start and endDate
Insert Into #FullMonthlyTable
(IndexCode, IndexLevel, ValueDate)
Select 'Test1' , 1.50 , @.StartDate
Union
Select 'Test1' , 1.50 ,@.EndDate
--use a tempstart date for the while loop population
Set @.TempStartDate = @.StartDate
--now populate the rows between StartDate and EndDate
-- i.e Create all the consecutive ValueDates so as to have a complete month(from the 31 of the previous month to the end of next month -1day)
While(@.TempStartDate < @.EndDate -1 )
Begin
Insert Into #FullMonthlyTable( ValueDate ) Values (@.TempStartDate + 1)
Set @.TempStartDate = @.TempStartDate + 1 --Increment dates consecutively..can do monthly as well as quarterly
End
--now populate the Values for the valuedates between start and end date
Update #FullMonthlyTable
Set IndexCode = 'Test1',
IndexLevel = 1.50
--Where #FullMonthlyTable -- for some reason this bloody where clause ain't working!! no matter i can just update since i have the start and end date
--Between @.StartDate And @.EndDate
--Create final Table:
Create table #MyFinalTable(IndexCode varchar(10), IndexLevel float, ValueDate datetime)
--1,DO UPDATE IF THE DATES ALREADY EXIST IN THE FINAL Table
--update final table:
Update #MyFinalTable
Set IndexLevel = #FullMonthlyTable.IndexLevel
ValueDate = #FullMonthlyTable.ValueDate
Where #FullMonthlyTable
Between @.StartDate And @.EndDate
--2,DO INSERT IF THE DATES DONT ALREADY EXIST IN THE FINAL Table
--Insert Into MyFinal Table:
--Create Date Range in the final table first...and insert the value in the start and end date
--(that will be used to populate all the dates for that month)as above using the file loop
Insert Into #MyFinalTable
(IndexCode, IndexLevel , ValueDate)
Select 'Test1' , #FullMonthlyTable.IndexLevel , @.StartDate
Union
Select 'Test1' , #FullMonthlyTable.IndexLevel , @.EndDate
--...then do the first update to create the consecutive dates between start and end dates
--use a tempstart date for the while loop population
Set @.TempStartDate = @.StartDate
--now populate the rows between StartDate and EndDate
-- i.e Create all the consecutive ValueDates so as to have a complete month(from the 31 of the previous month to the end of next month -1day)
While(@.TempStartDate < @.EndDate -1 )
Begin
Insert Into #MyFinalTable( ValueDate ) Values (@.TempStartDate + 1)
Set @.TempStartDate = @.TempStartDate + 1 --Increment dates consecutively..can do monthly as well as quarterly
End
--Now do this for every row in
--END PROCESS:
Drop Table #MonthlyTable
Drop Table #FullMonthlyTable
Drop Table #MyFinalTable
--As you can see i have to use a while loop each tme i need to create my consecutive dates between the start and end dates in both the #FullMonthlyTable and the #MyFinalTable...and i really dont want to do it this way or with a cursor for lots of reasons as well as perfomance!
if there is a way with set operations please help.
|||
Hi Thanks,
For your reply below is basically what i am trying to do the process is almost the same for my monthly and quaterly table except for the calculations in my quarterly table this is a detailed process below...
Basically i want to iterate through the top table getting each row and then expanding it into another table that represents a month and inserting it into a final table that may or may not have those dates essentially doing and insert( if the consecutive dates do not exist, hence create them in the final and then add in the data ) or an update (if the consecutive dates exists) in the final..
..for simplicity i have created all tables involved, but in reality the final table is not a temp table and exists already
--This is the Sample Monthly Data Table that needs to be iterated through..That i dont want to use a cursor for or a while loop
if Object_id('tempdb..#MonthlyTable') IS NOT NULL
Drop Table #MonthlyTable
Create table #MonthlyTable(MonthlyIndexCode varchar(10), MonthlyIndexLevel float, MonthlyValueDate datetime)
Insert Into #MonthlyTable
(MonthlyIndexCode, MonthlyIndexLevel, MonthlyValueDate)
Select 'Test1' , 1.50 , '20060131' --Jan
Union
Select 'Test2' , 2.50 , '20060228' --Feb
Union
Select 'Test3' , 8.50 , '20060331' --Mar
Union
Select 'Test4' , 4.50 , '20060430' --Apr
Union
Select 'Test5' , 5.50 , '20060531' --May
Union
Select 'Test6' , 6.50 , '20060630' --Jun
Union
Select 'Test7' , 5.50 , '20060731' --Jul
Union
Select 'Test8' , 8.50 , '20060831' --Aug
Union
Select 'Test9' , 9.50 , '20060930' --Sep
Union
Select 'Test10' , 10.50 , '20061031' --Oct
Union
Select 'Test11' , 11.50 , '20061130' --Nov
Union
Select 'Test12' , 12.50 , '20061231' --Dec
--Check table
--Select * from #MonthlyTable Order by MonthlyValueDate
--START PROCESS: FOR EACH ROW IN THE ABOVE #MONTHLY TABLE
--For each record in the above Table decompound each record as it represents data for a whole month (for that records ValueDate)
Declare @.StartDate datetime,@.TempStartDate Datetime, @.EndDate datetime
set @.StartDate = '20060131'
--derive the MonthEndDate from the StartDate(make sure the start date is corrected to its end of the month)
set @.EndDate = DateAdd(Month, 1, dbo.GetMonthendDate(@.StartDate) )
if Object_id('tempdb..#FullMonthlyTable') IS NOT NULL
Drop Table #FullMonthlyTable
Create table #FullMonthlyTable(IndexCode varchar(10), IndexLevel float, ValueDate datetime)
--Insert the start and endDate
Insert Into #FullMonthlyTable
(IndexCode, IndexLevel, ValueDate)
Select 'Test1' , 1.50 , @.StartDate
Union
Select 'Test1' , 1.50 ,@.EndDate
--use a tempstart date for the while loop population
Set @.TempStartDate = @.StartDate
--now populate the rows between StartDate and EndDate
-- i.e Create all the consecutive ValueDates so as to have a complete month(from the 31 of the previous month to the end of next month -1day)
While(@.TempStartDate < @.EndDate -1 )
Begin
Insert Into #FullMonthlyTable( ValueDate ) Values (@.TempStartDate + 1)
Set @.TempStartDate = @.TempStartDate + 1 --Increment dates consecutively..can do monthly as well as quarterly
End
--now populate the Values for the valuedates between start and end date
Update #FullMonthlyTable
Set IndexCode = 'Test1',
IndexLevel = 1.50
--Where #FullMonthlyTable -- for some reason this bloody where clause ain't working!! no matter i can just update since i have the start and end date
--Between @.StartDate And @.EndDate
--Create final Table:
Create table #MyFinalTable(IndexCode varchar(10), IndexLevel float, ValueDate datetime)
--1,DO UPDATE IF THE DATES ALREADY EXIST IN THE FINAL Table
--update final table:
Update #MyFinalTable
Set IndexLevel = #FullMonthlyTable.IndexLevel
ValueDate = #FullMonthlyTable.ValueDate
Where #FullMonthlyTable
Between @.StartDate And @.EndDate
--2,DO INSERT IF THE DATES DONT ALREADY EXIST IN THE FINAL Table
--Insert Into MyFinal Table:
--Create Date Range in the final table first...and insert the value in the start and end date
--(that will be used to populate all the dates for that month)as above using the file loop
Insert Into #MyFinalTable
(IndexCode, IndexLevel , ValueDate)
Select 'Test1' , #FullMonthlyTable.IndexLevel , @.StartDate
Union
Select 'Test1' , #FullMonthlyTable.IndexLevel , @.EndDate
--...then do the first update to create the consecutive dates between start and end dates
--use a tempstart date for the while loop population
Set @.TempStartDate = @.StartDate
--now populate the rows between StartDate and EndDate
-- i.e Create all the consecutive ValueDates so as to have a complete month(from the 31 of the previous month to the end of next month -1day)
While(@.TempStartDate < @.EndDate -1 )
Begin
Insert Into #MyFinalTable( ValueDate ) Values (@.TempStartDate + 1)
Set @.TempStartDate = @.TempStartDate + 1 --Increment dates consecutively..can do monthly as well as quarterly
End
--Now do this for every row in #MonthlyTable
--END PROCESS:
Drop Table #MonthlyTable
Drop Table #FullMonthlyTable
Drop Table #MyFinalTable
--As you can see i have to use a while loop each tme i need to create my consecutive dates between the start and end dates in both the #FullMonthlyTable and the #MyFinalTable...and i really dont want to do it this way or with a cursor for lots of reasons as well as perfomance!
if there is a way with set operations please help.
|||
Hi Dave,
thanks for your reply..can you see my last post this is exactly the opration i am trying to run, on my monthly table as well as the quarterly table apart from some calculations the operations are exactly the same, but as you can see the monthly one has 12 records one for each month of the year...that is expanded into another table and then inserted or updated into my final table this is the whole process repeated for each record in the #Monthly Table, and in some cases the values can be the same so i am not sure if your solutions will work as you say it is taking advantage of the values as unique?
regards
Shadow
|||Shadow:
I assume that the fragment that says
Select 'Test7' , 5.50 , '20060731' --Jul
is a typo that should say
Select 'Test7' , 7.50 , '20060731' --Jul
Is that true?
|||Shadow:
Another problem that I see consistently is the likes incrementing datetime variables by an integer such as:
@.EndDate -1
This is not going to work correctly. Where you want to increment or decrement a date by a month or a date use the DATEADD function.
|||Dave
Hello:
Thanks for putting all the information together.
In my opion, you need an auxiliary calendar table for quick set based solution. If you can figure out something from this article, it would be great.
"Why should I consider using an auxiliary calendar table?" http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
If this doesn't point to the right direction, please post back.
By the way, I am reading a few SQL books now and a few authors are here all the time. They are really helpful and each day I can learn something new here.
|||Hi Dave,
no its not a typo, as you can see, the values can be the same in some case in some months..hence it is arbritrary which is not unique, according to my spec it cannot be unique because the same value can arrive in any month...
the code here is a more refined version of the last although it seems to be running (the outer while loop) only for one month as the final table should have values for 12 months i.e the outer loop should execute 12 times ..
but i have to check it that... this is the only way i know how to implement this...
--#MonthlyTable
--
--This is the Sample Monthly Data Table that needs to be iterated through..That i dont want to use a cursor for or a while loop
if Object_id('tempdb..#MonthlyTable') IS NOT NULL
Drop Table #MonthlyTable
Create table #MonthlyTable(MonthlyIndexCode varchar(10), MonthlyIndexLevel float, MonthlyValueDate datetime)
Insert Into #MonthlyTable
(MonthlyIndexCode, MonthlyIndexLevel, MonthlyValueDate)
Select 'Test1' , 1.50 , '20060131' --Jan
Union
Select 'Test2' , 2.50 , '20060228' --Feb
Union
Select 'Test3' , 8.50 , '20060331' --Mar
Union
Select 'Test4' , 4.50 , '20060430' --Apr
Union
Select 'Test5' , 5.50 , '20060531' --May
Union
Select 'Test6' , 6.50 , '20060630' --Jun
Union
Select 'Test7' , 5.50 , '20060731' --Jul
Union
Select 'Test8' , 8.50 , '20060831' --Aug
Union
Select 'Test9' , 9.50 , '20060930' --Sep
Union
Select 'Test10' , 10.50 , '20061031' --Oct
Union
Select 'Test11' , 11.50 , '20061130' --Nov
Union
Select 'Test12' , 12.50 , '20061231' --Dec
--Check table
--Select * from #MonthlyTable Order by MonthlyValueDate
--
DECLARE @.MonthlyStartDate DATETIME, @.MonthlyEndDate DATETIME
SET @.MonthlyStartDate = DATEADD( Day, 0, Datename(year, getdate() )+ '01'+ '31' ) --'20060131'
SET @.MonthlyEndDate = DATEADD( Day, 0, Datename(year, getdate() )+ '12'+ '31' ) --'20061231'
--Print @.MonthlyStartDate
--print @.MonthlyEndDate
WHILE (@.MonthlyStartDate < @.MonthlyEndDate )
BEGIN
--START PROCESS: FOR EACH ROW IN THE ABOVE #MONTHLY TABLE
--For each record in the above Table decompound each record as it represents data for a whole month (for that records ValueDate)
--#FullMonthlyTable:
--prelim:
Declare @.IndexCode Varchar(20), @.IndexLevel float, @.ValueDate DateTime
Select Top 1 @.IndexCode = MonthlyIndexCode,
@.IndexLevel = MonthlyIndexLevel--,
-- @.ValueDate = MonthlyValueDate
From #MonthlyTable order by MonthlyValueDate --no need for 'Order By' as all values are the same
--since we have the record we can delete it...
Delete #MonthlyTable
Where MonthlyValueDate = (Select Top 1 MonthlyValueDate From #MonthlyTable)
-
Declare @.StartDate datetime,@.TempStartDate Datetime, @.EndDate datetime
Select Top 1 @.StartDate = MonthlyValueDate From #MonthlyTable order by MonthlyValueDate
--derive the MonthEndDate from the StartDate(make sure the start date is corrected to its end of the month usind my UDF)
set @.EndDate = DateAdd(Month, 1, dbo.GetMonthEndDate(@.StartDate) ) - 1
if Object_id('tempdb..#FullMonthlyTable') IS NOT NULL
Drop Table #FullMonthlyTable
Create table #FullMonthlyTable(IndexCode varchar(10), IndexLevel float, ValueDate datetime)
--Insert the start and endDate
Insert Into #FullMonthlyTable
(IndexCode , IndexLevel , ValueDate)
Select @.IndexCode, @.IndexLevel, @.StartDate --In practice only IndexLevel and Valuedate are needed as indexcodes will already exist in the final table.
Union --These Select are Exactly the same and represent the first row fetched from the #MonthlyTable above...
Select @.IndexCode , @.IndexLevel, @.EndDate --...and are being inserted alonge with the date range this is o.k as the values within a month are the sameall through
--use a tempstart date for the while loop population
Set @.TempStartDate = @.StartDate
--Method1: populate the dates and values at the same time within the while loop, more efficiently executed than method 2
--now populate the value, rows between StartDate and EndDate
-- i.e Create all the consecutive ValueDates so as to have a complete month(from the 31 of the previous month to the end of next month -1day)
While(@.TempStartDate < @.EndDate - 1 )
Begin
Insert Into #FullMonthlyTable
( IndexCode, IndexLevel, ValueDate )
Values ('Test1' , 1.50 , @.TempStartDate + 1)
Set @.TempStartDate = @.TempStartDate + 1 --Increment dates consecutively..can do monthly as well as quarterly
End
--or method 2: use update after populating the dates in the while above
--now populate the Values for the valuedates between start and end date
/*Update #FullMonthlyTable Set IndexCode = 'Test1', IndexLevel = 1.50 Where #FullMonthlyTable.ValueDate Between @.StartDate And @.EndDate
*/
--Select * from #FullMonthlyTable order by ValueDate
Declare @.MonthlyIndexCode Varchar(20), @.monthlyIndexLevel float, @.MonthlyValueDate DateTime
Select Top 1 @.MonthlyIndexCode = IndexCode, @.monthlyIndexLevel = IndexLevel, @.MonthlyValueDate = ValueDate from #FullMonthlyTable
--#MyFinalTable:
--Create final Table:
if Object_id('tempdb..#MyFinalTable') IS NOT NULL
Drop Table #MyFinalTable
Create table #MyFinalTable(IndexCode varchar(10), IndexLevel float, ValueDate datetime)
--UPDATE:
--1,DO UPDATE IF THE DATES ALREADY EXIST IN THE FINAL Table
--update final table:
Update #MyFinalTable
Set IndexLevel = @.MonthlyIndexLevel, --#FullMonthlyTable.IndexLevel,
ValueDate = @.MonthlyValueDate --#FullMonthlyTable.ValueDate
Where #MyFinalTable.ValueDate
Between @.StartDate And @.EndDate
--and Exists(Select ValueDate from #MyFinalTable.ValueDate Where ValueDate is not NULL ) -- update only if consecutive ValueDates exist in final..
--..check this later on..
--INSERT:
--2,DO INSERT IF THE DATES DONT ALREADY EXIST IN THE FINAL Table--
--Insert Into MyFinal Table:
--Create Date Range in the final table first...along with the value in the start and end date
--(that will be used to populate all the dates [in between the range] for that month)as above using the file loop
Insert Into #MyFinalTable
(IndexCode , IndexLevel , ValueDate )
Select @.MonthlyIndexCode, @.monthlyIndexLevel, @.StartDate --In Practice there would be no need to insert the IndexCode they would already exist in
Union --in the final table
Select @.MonthlyIndexCode, @.monthlyIndexLevel, @.EndDate
--use a tempstart date for the while loop population
Set @.TempStartDate = @.StartDate
--now populate the rows between StartDate and EndDate
-- i.e Create all the consecutive ValueDates so as to have a complete month(from the 31 of the previous month to the end of next month -1day)
While(@.TempStartDate < @.EndDate - 1 )
Begin
Insert Into #MyFinalTable
( IndexCode, IndexLevel, ValueDate )
Values ( @.IndexCode, @.IndexLevel, @.TempStartDate + 1)
Set @.TempStartDate = @.TempStartDate + 1 --Increment dates consecutively..can do monthly as well as quarterly
End
--Now do this for every row in Table #MonthTable
--END PROCESS:
Set @.MonthlyStartDate = DATEADD(Month, 1, dbo.GetMonthEndDate(@.MonthlyStartDate)) --add a month as increment
END
Drop Table #MonthlyTable
Drop Table #FullMonthlyTable
Drop Table #MyFinalTable
--Select * from #MyFinalTable order by ValueDate
--This is how the outer loop should behave...(proof of concept)
Declare @.MonthlyStartDate Datetime, @.MonthlyEndDate Datetime, @.myint int
Set @.MonthlyStartDate = '20060131'
Set @.MonthlyEndDate = '20061231'
Set @.myint = 1
--print DATEADD(Month, 2, dbo.GetMonthEndDate(@.MonthlyStartDate))
--If ( @.MonthlyStartDate < DATEADD(Month, 2, dbo.GetMonthEndDate(@.MonthlyStartDate)) )
While ( @.MonthlyStartDate < @.MonthlyEndDate)
Begin
print 'execute stuff for the month'
print @.myint
Set @.myint = @.myint + 1
Set @.MonthlyStartDate = DATEADD(Month, 1, dbo.GetMonthEndDate(@.MonthlyStartDate))
End
|||Thanks Dave, yep thats what i normally do, i just wanted to get the code sketch the ground first and then go through it later, well if i can do it using set operations..
没有评论:
发表评论