calculation payment date (sql date calculation minus w-e & holidays)
848501Mar 24 2011 — edited Mar 24 2011Dear,
Can someone help me to write an sql code based upon following info :
I would need to create an sql query that verifies the correct calculation of the payment date;
logic :
- if period = M and payon = 1 then take start date + 1 month + 1 day (increment by one month if id contains different lines - see example)
- if period = M and payon = 5 then take start date + 1 month + 5 days (increment by one month if id contains different lines - see example)
- if period = M and payon = 20 then take start date + 1 month + 20 days (increment by one month if id contains different lines - see example)
- if period = T and payon = 1 then take trade date + 1 day (only one payment date possible if period = T, see example)
- if period = T and payon = 5 then take trade date + 5 days(only one payment date possible if period = T, see example)
- if period = T and payon = 20 then take trade date + 20 days(only one payment date possible if period = T, see example)
- if period = U and payon = 1 then start date + 1 day (increment by one month if id contains different lines - see example)
- if period = U and payon = 5 then take start date + 5 days (increment by one month if id contains different lines - see example)
- if period = U and payon = 20 then take start date + 20 days (increment by one month if id contains different lines - see example)
IMPORTANT
weekends and holidays do not count when calculatiing the payment day; the payment is thus always on business day.
I have stored all holidays in a table HDAY with column HDAY_DATE and HDAY_DESCRIPTION
(for example: friday +1 would give Monday if not a holiday, else Tuesday)
Row # ID PERIOD PAYDATE STARTDATE ENDDATE TRADE_DATE PAYON
----- ---------- -------------- ----------- -------------- ------------ ----------- ----------
1 ABC001 M 7/08/2009 1/07/2009 30/09/2011 15/01/2009 5
2 ABC001 M 7/09/2009 1/07/2009 30/09/2011 15/01/2009 5
3 ABC001 M 7/10/2009 1/07/2009 30/09/2011 15/01/2009 5
4 ABC001 M 6/11/2009 1/07/2009 30/09/2011 15/01/2009 5
5 ABC001 M 7/12/2009 1/07/2009 30/09/2011 15/01/2009 5
6 ABC001 M 8/01/2010 1/07/2009 30/09/2011 15/01/2009 5
7 ABC001 M 5/02/2010 1/07/2009 30/09/2011 15/01/2009 5
8 ABC001 M 5/03/2010 1/07/2009 30/09/2011 15/01/2009 5
9 ABC001 M 7/04/2010 1/07/2009 30/09/2011 15/01/2009 5
10 ABC001 M 7/05/2010 1/07/2009 30/09/2011 15/01/2009 5
11 ABC001 M 7/06/2010 1/07/2009 30/09/2011 15/01/2009 5
12 ABC001 M 7/07/2010 1/07/2009 30/09/2011 15/01/2009 5
13 ABC001 M 6/08/2010 1/07/2009 30/09/2011 15/01/2009 5
14 ABC001 M 7/09/2010 1/07/2009 30/09/2011 15/01/2009 5
15 ABC001 M 7/10/2010 1/07/2009 30/09/2011 15/01/2009 5
16 ABC001 M 5/11/2010 1/07/2009 30/09/2011 15/01/2009 5
17 ABC001 M 7/12/2010 1/07/2009 30/09/2011 15/01/2009 5
18 ABC001 M 7/01/2011 1/07/2009 30/09/2011 15/01/2009 5
19 ABC001 M 7/02/2011 1/07/2009 30/09/2011 15/01/2009 5
20 ABC001 M 7/03/2011 1/07/2009 30/09/2011 15/01/2009 5
21 ABC001 M 7/04/2011 1/07/2009 30/09/2011 15/01/2009 5
22 ABC001 M 6/05/2011 1/07/2009 30/09/2011 15/01/2009 5
23 ABC001 M 7/06/2011 1/07/2009 30/09/2011 15/01/2009 5
24 ABC001 M 7/07/2011 1/07/2009 30/09/2011 15/01/2009 5
25 ABC001 M 5/08/2011 1/07/2009 30/09/2011 15/01/2009 5
26 ABC001 M 7/09/2011 1/07/2009 30/09/2011 15/01/2009 5
27 ABC001 M 7/10/2011 1/07/2009 30/09/2011 15/01/2009 5
1 DEF001 T 26/01/2011 1/12/2011 31/12/2011 19/01/2011 5
2 GHI001 T 27/01/2011 1/01/2012 31/03/2012 20/01/2011 5
3 JKL001 T 18/01/2011 1/03/2011 31/03/2011 11/01/2011 5
4 MNO001 T 17/01/2011 1/03/2011 31/03/2011 10/01/2011 5
5 PQR001 T 26/01/2011 1/07/2011 31/07/2011 19/01/2011 5
1 XYZ001 U 2/01/2012 1/01/2012 31/12/2012 5/02/2009 1
2 XYZ001 U 1/02/2012 1/01/2012 31/12/2012 5/02/2009 1
3 XYZ001 U 1/03/2012 1/01/2012 31/12/2012 5/02/2009 1
4 XYZ001 U 2/04/2012 1/01/2012 31/12/2012 5/02/2009 1
5 XYZ001 U 1/05/2012 1/01/2012 31/12/2012 5/02/2009 1
6 XYZ001 U 1/06/2012 1/01/2012 31/12/2012 5/02/2009 1
7 XYZ001 U 2/07/2012 1/01/2012 31/12/2012 5/02/2009 1
8 XYZ001 U 1/08/2012 1/01/2012 31/12/2012 5/02/2009 1
9 XYZ001 U 3/09/2012 1/01/2012 31/12/2012 5/02/2009 1
10 XYZ001 U 1/10/2012 1/01/2012 31/12/2012 5/02/2009 1
11 XYZ001 U 1/11/2012 1/01/2012 31/12/2012 5/02/2009 1
12 XYZ001 U 3/12/2012 1/01/2012 31/12/2012 5/02/2009 1