Ar-jay Alaon Ar-jay Alaon - 1 month ago 7
SQL Question

SQL move date schedule by account number

I have payment schedule table and I want to move the date 1 payment prior for every account. It only cycles for 15 and (30 or 28 or 29 for february).

accountno | inst_no | date(datetime) | amount
0001 | 1 | 8-15-2017 | 100
0001 | 2 | 8-30-2017 | 100
0001 | 3 | 9-15-2017 | 100
---------------------------------------------
0002 | 1 | 6-15-2017 | 100
0002 | 2 | 6-30-2017 | 100
0002 | 3 | 7-15-2017 | 100
--------------------------------------------
0003 | 1 | 8-15-2017 | 100
0003 | 2 | 8-30-2017 | 100


Result

accountno | inst_no | date(datetime) | amount
0001 | 1 | 8-30-2017 | 100
0001 | 2 | 9-15-2017 | 100
0001 | 3 | 9-30-2017 | 100
---------------------------------------------
0002 | 1 | 6-30-2017 | 100
0002 | 2 | 7-15-2017 | 100
0002 | 3 | 7-30-2017 | 100
--------------------------------------------
0003 | 1 | 8-30-2017 | 100
0003 | 2 | 9-15-2017 | 100


Can this be done on query?

Answer Source

What about this? You can easily rewrite into SELECT.

UPDATE PaymentScheduleTable 
SET d = CASE WHEN DAY(d) = 15 
             THEN 
               CASE WHEN MONTH(d) = 2
                    THEN DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,d)+1,0)) -- last day of month
                    ELSE DATEADD(DAY, 15, d) 
               END 
               ELSE DATEADD(DAY, 15, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,d)+1,0)))
        END