Returning refund value over two different rates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Returning refund value over two different rates

Author

Topic

NickC
Yak Posting Veteran

68 Posts

Posted - 2010-11-26 : 10:37:17

A little bit a of a complex one

We have to create a report which reports on the refund someone is due when they cancel their policy.

Basically if someones policy runs from 01/12/2010 to 31/01/2011, we need to caculate the daily rate over these two months, the problem is the rate changes depending on the number of days in the month so for december it may be 0.2 per day and in january it could be 0.24 per day.

Policies can also run longer than two months so it needs to be over x amount of months with each month having a different daily rate.

In this example a policy starts on 01/01/2010 and ends on 15/02/2010 however they cancelled on 16/01/2010 so are due a refund of £1.05

so our table in excel looks like this

1 1 01/01/2010 31 0.032258065
2 1 02/01/2010 31 0.032258065
3 1 03/01/2010 31 0.032258065
4 1 04/01/2010 31 0.032258065
5 1 05/01/2010 31 0.032258065
6 1 06/01/2010 31 0.032258065
7 1 07/01/2010 31 0.032258065
8 1 08/01/2010 31 0.032258065
9 1 09/01/2010 31 0.032258065
10 1 10/01/2010 31 0.032258065
11 1 11/01/2010 31 0.032258065
12 1 12/01/2010 31 0.032258065
13 1 13/01/2010 31 0.032258065
14 1 14/01/2010 31 0.032258065
15 1 15/01/2010 31 0.032258065
16 1 16/01/2010 31 0.032258065
17 1 17/01/2010 31 0.032258065
18 1 18/01/2010 31 0.032258065
19 1 19/01/2010 31 0.032258065
20 1 20/01/2010 31 0.032258065
21 1 21/01/2010 31 0.032258065
22 1 22/01/2010 31 0.032258065
23 1 23/01/2010 31 0.032258065
24 1 24/01/2010 31 0.032258065
25 1 25/01/2010 31 0.032258065
26 1 26/01/2010 31 0.032258065
27 1 27/01/2010 31 0.032258065
28 1 28/01/2010 31 0.032258065
29 1 29/01/2010 31 0.032258065
30 1 30/01/2010 31 0.032258065
31 1 31/01/2010 31 0.032258065
32 2 01/02/2010 28 0.035714286
33 2 02/02/2010 28 0.035714286
34 2 03/02/2010 28 0.035714286
35 2 04/02/2010 28 0.035714286
36 2 05/02/2010 28 0.035714286
37 2 06/02/2010 28 0.035714286
38 2 07/02/2010 28 0.035714286
39 2 08/02/2010 28 0.035714286
40 2 09/02/2010 28 0.035714286
41 2 10/02/2010 28 0.035714286
42 2 11/02/2010 28 0.035714286
43 2 12/02/2010 28 0.035714286
44 2 13/02/2010 28 0.035714286
45 2 14/02/2010 28 0.035714286
46 2 15/02/2010 28 0.035714286

Sachin.Nand

2937 Posts

Posted - 2010-11-26 : 13:50:48

So do you have a table which stores rates for a particular month?

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-27 : 02:12:39

i see only one date field in table. is this policy date or cancelled date?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources