Windows 10 
Office 365
It's somewhat complex. We have a table of Annual price rises per Network (It's a thing in the UK Broadband and Mobile Phone Market and is very frustrating). They have a start and end date.
We also have a table of price data per Network/Broadband package on a specific date. Some of those prices have promotional periods on them (up to three). (Something like 3 Months free, 9 months half price, 6 months at price 3, and an out of contract price)
So let's say you have a 24 month Contract taken out on the 15th December with 6 months Half price. Initial Promotional cost is £15/month rising to £30/month until end of contract then £35 out of contract.
That network has an annual price rise of £3.00 that is applied on the 1st of April. Some of the Metrics we provide are a total contract cost over 12 months, 18 month and 24 months.
So the sums become a bit of a nightmare centred around the purchase date.
In the example there 106 Days at the initial £15 price then it rises for 44 days to £18 until 15/5/2025. Then the price rises to £33/month for 320 days (until 1st April 2026) when it then goes to £36 per month for the balance of the contract. (until 15/12/2026).
A number of factors arise - the pricing is all quoted monthly, but to get accurate results you need a daily rate, for each period.
The pricing records are recorded daily or weekly so the Total costs will alter for each record, and each package can have no promotions or 1, 2 or 3 promotional periods. (This is fixed there won't ever be a 4th promotion, and most of the time there is only 1, but the calculations need to account for as many as there are.)
I've nearly finished my working on this, but is is not a 3 line query.
It's currently a 250 line stored procedure at the moment whilst I am checking it for accuracy.
I can probably condense it a bit, but I wanted to make it readable/understandable when something changes in 9 months and I need to revisit it, or a colleague has to look at it.
Reply With Quote
Originally Posted by jojowhite
