londonpublish
New Member
- Joined
- Nov 23, 2012
- Messages
- 1
Hello all,
First post on here so apologies if I miss any protocol. I'd greatly appreciate help with the following, which will save a lot manual checking!
So I have two data sets - an extract of the first as follows, demonstrating the periods for which products were promoted and the applicable discount in that promotional period. Note that any one product does not have overlapping promotional periods, although different product codes may have overlapping dates:
Set A:
<tbody>
</tbody>
I then have a listing of product codes and invoice dates, as follows:
Set B:
<tbody>
</tbody>
I would like to bring back the appropriate discount for the invoice date, so results as follows:
Set B:
<tbody>
</tbody>
I'm guessing that the first step to resolve this is to concatenate the codes with the VALUE of the DATES, so I have a range, as follows:
Set A:
<tbody>
</tbody>
And then do the same with my other set:
Set B:
<tbody>
</tbody>
But how do I now look back from Set B to Set A to say, IF CODE-INV DATE is greater than a value in CODE-START DATE column and less than a value in CODE-END DATE column, return the discount value against that range?
Many thanks in advance.
First post on here so apologies if I miss any protocol. I'd greatly appreciate help with the following, which will save a lot manual checking!
So I have two data sets - an extract of the first as follows, demonstrating the periods for which products were promoted and the applicable discount in that promotional period. Note that any one product does not have overlapping promotional periods, although different product codes may have overlapping dates:
Set A:
Product Code | Promotion Start Date | Promotion End Date | Discount |
101 | 01/01/2012 | 31/03/2012 | 5% |
101 | 01/05/2012 | 30/06/2012 | 20% |
110 | 15/01/2012 | 31/03/2012 | 8% |
110 | 10/05/2012 | 30/06/2012 | 10% |
110 | 01/09/2012 | 31/10/2012 | 12% |
150 | 01/01/2012 | 31/03/2012 | 11% |
150 | 01/06/2012 | 30/06/2012 | 15% |
<tbody>
</tbody>
I then have a listing of product codes and invoice dates, as follows:
Set B:
Product Code | Invoice Date |
101 | 28/02/2012 |
101 | 25/03/2012 |
101 | 06/05/2012 |
110 | 25/01/2012 |
110 | 01/10/2012 |
150 | 10/01/2012 |
<tbody>
</tbody>
I would like to bring back the appropriate discount for the invoice date, so results as follows:
Set B:
Product Code | Invoice Date | Discount |
101 | 28/02/2012 | 5% |
101 | 25/03/2012 | 5% |
101 | 06/05/2012 | 20% |
110 | 25/01/2012 | 8% |
110 | 01/10/2012 | 12% |
150 | 10/01/2012 | 11% |
<tbody>
</tbody>
I'm guessing that the first step to resolve this is to concatenate the codes with the VALUE of the DATES, so I have a range, as follows:
Set A:
Code-Start Date | Code-End Date | Discount |
10140909 | 10140999 | 5% |
10141030 | 10141090 | 20% |
11040923 | 11040999 | 8% |
11041039 | 11041090 | 10% |
11041153 | 11041213 | 12% |
15040909 | 15040999 | 11% |
15041061 | 15041090 | 15% |
<tbody>
</tbody>
And then do the same with my other set:
Set B:
Code-Inv Date | Invoice Date |
10140967 | 28/02/2012 |
10140993 | 25/03/2012 |
10141035 | 06/05/2012 |
11040933 | 25/01/2012 |
11041183 | 01/10/2012 |
15040918 | 10/01/2012 |
<tbody>
</tbody>
But how do I now look back from Set B to Set A to say, IF CODE-INV DATE is greater than a value in CODE-START DATE column and less than a value in CODE-END DATE column, return the discount value against that range?
Many thanks in advance.