Inserting records between two dates

[JSMITH242B]

Hi Group,
I have to insert/create records in another table based on start and end dates in a table. I also have to insert the Financial period and a year based on this.

So what I could have is:
Start Date 01/01/2003
End Date 01/01/2007

what I want to insert into the other table is:
Year Financial Period
1 2003/2004
2 2004/2005
3 2005/2006
4 2006/2007

How can I achieve this?

Regards

 

[vbkris]

insert into TargetTable(Columns) select Columns from sourceTable where date <= End date and date>=Start Date

Known is handfull, Unknown is worldfull

 

[JSMITH242B]

seems like you are an all rounder.
No, what I what is to insert into the new table a record for each of the years between the start and end dates.

 

[gmmastros]

Here is some code to get you started. You should be able to copy/paste in to the Query Analyzer so you can run it and see how it works.

Declare @Start DateTime
Declare @End DateTime

Set @Start = '01/01/2003'
Set @End = '01/01/2007'


Declare @Temp Integer

Select @Temp = Year(@Start)

Declare @Data Table (Year Integer Identity(1,1), FinancialPeriod VarChar(100))

While @Temp < Year(@End)
	Begin
		Insert Into @Data(FinancialPeriod) Values(Convert(VarChar(4), @Temp) + '/' + Convert(VarChar(4), @Temp + 1))
		Set @Temp = @Temp + 1
	End

Select * From @Data

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[JSMITH242B]

Hi George,
Thanks for this.

What does this line do?
Declare @Data Table (Year Integer Identity(1,1), FinancialPeriod VarChar(100))

my table name is called leaseyeardet and the fields I need to insert are Year_Number and Financial_Year for each year between the start and end dates.

Regards

 

[vbkris]

>>No, what I what is to insert into the new table a record for each of the years between the start and end dates.

confused, isnt that handled in the where clause???

Known is handfull, Unknown is worldfull

 

[gmmastros]

This line...
Declare @Data Table (Year Integer Identity(1,1), FinancialPeriod VarChar(100))
Set up a table that I could insert into. @Data is a table variable, which is similar to a temporary table, but completely stored in memory instead of being written to the harddrive. The first field (year) is defined as an integer identity field because in your desired output, you showed a year field starting with 1 and incrementing for each record.

If the data in the table variable is EXACTLY the way you want it, you can simply insert it in to your actual target table, like this...

Insert Into leaseyeardet(Year_Number, Financial_Year)
Select Year, FinancialPeriod From @Data

This assumes that Year_Number is an integer field and Financial_Year is a VarChar field.

If I did not explain myself well enough, please let me know, and I will help more.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[JSMITH242B]

Hi George Thanks.

Background info:
There are two table:
_SMDBA_.LEASEAGRMNT AND
_SMDBA_.LEASEYEARDET

I get the start and end dates from the _SMDBA_.LEASEAGRMNT table for the @LeaseSequenceNo passed in as a parameter.

What I need to do is for every matching seq_lease_agreement number(@LeaseSequenceNo)in _SMDBA_.LEASEYEARDET create a record between the start and end dates retrieved from the _SMDBA_.LEASEAGRMNT table.

I've included a SQL statement at the bottom of the Procedure to show how the 2 tables are joined.

CREATE PROCEDURE LBHF_AddAnnualLeaseValue

@LeaseSequenceNo int=0

AS

declare @rows int
declare @error int
declare @SEQ int

begin
declare @StartDate DATETIME
declare @EndDate DATETIME

set @StartDate = "select start_date from _SMDBA_.LEASEAGRMNT where sequence = @LeaseSequenceNo"
set @EndDate = "select end_date from _SMDBA_.LEASEAGRMNT where sequence = @LeaseSequenceNo"


declare @YearNo integer

select @YearNo = Year(@StartDate)

--declare @data leaseyeardet (Year_number integer identity(1,1), FinancialPeriod varchar(100))

WHILE @YearNo < Year(@EndDate)
begin


EXEC SMSYSGETNEXTRECNUMDB 'LEASEYEARDET', @SEQ OUTPUT

insert into _smdba_.leaseyeardet
(sequence,
lastmodified,
lastuser,
_group_,
[_inactive_:],
year_number,
financial_year,
note,
seq_lease_agreement,
annual_value,
lease_open,
costperthousandperyear,
tot_item_value,
ann_payment)

values
(@SEQ,
getdate(),
'MAGIC',
1,
0,
0,
0,
'',
@LeaseSequenceNo,
0,
1,
0,
0,
0)

--where _smdba_.leaseyeardet.seq_lease_agreement = @LeaseSequenceNo)

set @YearNo =@YearNo+1
end
END
GO

I know I have the WHERE claues in the wrong place. I'm not sure where it goes.

Thanks for your help.

Here's the SQL statement:
SELECT _SMDBA_.LEASEYEARDET.SEQUENCE, _SMDBA_.LEASEYEARDET.LASTMODIFIED, _SMDBA_.LEASEYEARDET.LASTUSER,
_SMDBA_.LEASEYEARDET._GROUP_, _SMDBA_.LEASEYEARDET.[_INACTIVE_:], _SMDBA_.LEASEYEARDET.YEAR_NUMBER,
_SMDBA_.LEASEYEARDET.FINANCIAL_YEAR, _SMDBA_.LEASEYEARDET.NOTE, _SMDBA_.LEASEYEARDET.SEQ_LEASE_AGREEMENT,
_SMDBA_.LEASEYEARDET.ANNUAL_VALUE, _SMDBA_.LEASEYEARDET.LEASE_OPEN, _SMDBA_.LEASEYEARDET.COSTPERTHOUSANDPERYEAR,
_SMDBA_.LEASEYEARDET.TOT_ITEM_VALUE, _SMDBA_.LEASEYEARDET.ANN_PAYMENT
FROM _SMDBA_.LEASEAGRMNT INNER JOIN
_SMDBA_.LEASEYEARDET ON _SMDBA_.LEASEAGRMNT.SEQUENCE = _SMDBA_.LEASEYEARDET.SEQ_LEASE_AGREEMENT
WHERE (_SMDBA_.LEASEYEARDET.[_INACTIVE_:] = 0)AND (_SMDBA_.LEASEAGRMNT.sequence = @LeaseSequenceNo )