Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a database with a load of rent charges in. I have
start date
end date
amount
I have applied Yearname([str-dte],0, 4) as RentYear to get the financial year to which the rent relates.
I am having an issue with the following scenario
Start Date 10/07/2012
End Date 09/07/2014
Amount £100
Start Date 10/07/2014
End Date 09/07/2015
Amount £110
This gives me
Rent Year 2012-2013 Amount £100
Rent Year 2014-2015 Amount £110
Because the start date doesn't fall into 2013-2014 it isn't generating an amount for that year.
How Do i get the following (correct) result?
Rent Year 2012-2013 Amount £100
Rent Year 2013-2014 Amount £100
Rent Year 2014-2015 Amount £110
Many thanks.
Table:
Load YearName(StartDate,0,4) As RentYear,*;
Load Date#(StartDate,'DD/MM/YYYY') As StartDate,Date#(EndDate,'DD/MM/YYYY') As EndDate,Amount Inline [
StartDate,EndDate,Amount
10/07/2012,09/07/2014,£100
10/07/2014,09/07/2015,£110 ];
Concatenate
Load StartDate,EndDate,Amount,YearName(AddYears(StartDate,IterNo()),0,4) As RentYear Resident Table While Year(EndDate) - Year(StartDate) -IterNo() >= 1;
Table:
Load YearName(StartDate,0,4) As RentYear,*;
Load Date#(StartDate,'DD/MM/YYYY') As StartDate,Date#(EndDate,'DD/MM/YYYY') As EndDate,Amount Inline [
StartDate,EndDate,Amount
10/07/2012,09/07/2014,£100
10/07/2014,09/07/2015,£110 ];
Concatenate
Load StartDate,EndDate,Amount,YearName(AddYears(StartDate,IterNo()),0,4) As RentYear Resident Table While Year(EndDate) - Year(StartDate) -IterNo() >= 1;
Thanks for that anbu,
That works great for the example given, but how do i replace the inline bit in your script when I have a full database of data to go in there?
Table:
Sql Select * From Table;
Concatenate
Load StartDate,EndDate,Amount,YearName(AddYears(StartDate,IterNo()),0,4) As RentYear Resident Table While Year(EndDate) - Year(StartDate) -IterNo() >= 1;