Show valid row for date

[mike_spada]

Hi to all,

I cannot find a solution to this scenario.

I have a table with product code, supplier code, date, percentual 1, percentual 2, like this:

|code|supplier|date|perc1|perc2|

|1|A001|20090303|100|0|

|1|B001|20090303|0|100|

|2|C001|20160501|0|95|

|2|C001|20160501|100|5|

I want to have a straight (or pivot) table with dimensions code and supplier, then a variable (vMyDate, for example 20160630) where I can specify a date.

perc1 is the percentual in use in the period previous to date (-1), and perc2 is the percentual in use in the period after the date.

So I must see:

with vMyDate = 20160412:

|1|B001|20090303|0|100|

|2|C001|20160501|100|5|

with vMyDate = 20160630:

|1|B001|20090303|0|100|

|2|C001|20160501|0|95|

Anyone can help me to understand how I can build it?

Thanks!

Mike 🙂

[swuehl]

Not 100% sure I understand your setting.

Which record is to take if you have multiple records per code on the same date, both showing values for e.g. perc2?

Why are you chosing |2|C001|20160501|0|95| in your example for vMyDate = 20160630?

Maybe have a look at

Creating a Date Interval from a Single Date

and try frist to create distinct intervals per code with the percentage value you want to apply to that interval.