Yearname Issue

[Karl_Hart]

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.

[anbu1984 · Accepted solution]

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;

[Karl_Hart]

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?

[anbu1984]

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;