CODEWITHSUNDEEP
sqloracle-database
fikos
fikos

Reputation: 201

How to select next value

The task is to see the date of payment of the loan. If it falls on a date where there is no such number, it does not show data but should show the first date from the next month Sql work good if i enter date 15.01.2019 But if i can enter date 31.01.2019 i have problem . I can not see correct result sql request.

    With days as ( 
    Select rownum As Day from All_Objects where Rownum<=31
    ),
    a as (Select 'WHWWHHWWWWWHHWWWWWHHWWWWWHHWWWW' as hl ,1 as Mnth,2019 as Yr from Dual
         Union All
          Select 'WHHWWWWWHHWWWWWHHWWWWWHHWWWW' as hl ,2 as Mnth,2019 as Yr from Dual
         Union All
          Select 'WHHWWWWHHHWWWWWHHWWHHHHHHHWWWHH' as hl ,3 as Mnth,2019 as Yr from Dual
         Union All
          Select 'WWWWWHHWWWWWHHWWWWWHHWWWWWHHWW' as hl ,4 as Mnth,2019 as Yr from Dual
         Union All
          Select 'WWWHHWWWHWHHWWWWWHHWWWWWHHWHWWW' as hl ,5 as Mnth,2019 as Yr from Dual
         Union All
          Select 'HHWWHHWHHWWWWWHHHWWWWHHWWHWWHH' as hl ,6 as Mnth,2019 as Yr from Dual
         Union All
          Select 'WWWWWHHWWWWWHHWWWWWHHWWWWWHHWWW' as hl ,7 as Mnth,2019 as Yr from Dual

         )
,

Alll as 
(Select TO_Date(Yr|| substr('0'||Mnth,-2,2)||substr('0'||Day,-2,2),'YYYYMMDD') as Dt,a.Yr,a.Mnth,Days.Day,substr(a.Hl,Days.Day,1) as Daytype from Days,a Where Days.Day<=Length(a.Hl)
),
Taksit as 
(
Select To_Date('31.01.2019') as TDate, 1000 as Amount ,3 as Tcount from Dual
),
PD as (
Select 
A.Dt,A.DayType , Case when A.DayType='H' then Min(W.Dt) else A.Dt end As PayableDate
From Alll A inner Join Alll W on W.DT>=A.DT and W.DayType='W'
Group by A.Dt, A.Daytype
Order by 1
),
PreResult as 
(
Select PD.PayableDate,Amount,TCount,Max(PD.PayableDate) over (Partition by 'Contract') as MPD
From PD inner join Taksit T on PD.DT between add_months(T.TDate,1) and Add_Months(T.TDate,TCount)
and TO_Char(PD.DT,'DD')=TO_Char(T.TDate,'DD')
)
Select 
    PayableDate, Case when PayableDate=MPD then Amount-(Round(Amount/TCount,2)*(TCount-1)) else Round(Amount/TCount,2) end PayAmount
from PreResult

Upvotes: 0

Views: 64

Answers (1)

Popeye
Popeye

Reputation: 35900

You have used TO_CHAR(PD.DT, 'DD') = TO_CHAR(T.TDATE, 'DD') but I don't think that Feb month has any date which will match with it.

Ideally, you should use add_month function as following in PRERESULT (I believe you need only 3 months data)

PRERESULT AS (
    SELECT
        PD.PAYABLEDATE,
        AMOUNT,
        TCOUNT,
        MAX(PD.PAYABLEDATE) OVER(
            PARTITION BY 'Contract'
        ) AS MPD
    FROM
        PD
        INNER JOIN TAKSIT T ON PD.DT BETWEEN ADD_MONTHS(T.TDATE, 1) AND ADD_MONTHS(T.TDATE, TCOUNT) 
                              AND PD.DT IN (ADD_MONTHS(T.TDATE, 1), ADD_MONTHS(T.TDATE, 2), ADD_MONTHS(T.TDATE, 3)) 
                              -- AND TO_CHAR(PD.DT, 'DD') = TO_CHAR(T.TDATE, 'DD')
)

It is giving 3 dates with 31.01.2019 and also it is working as expected in the case of 15.01.2019 also.

I think you should check if it is giving an expected result with 31.01.2019 as you have not mentioned the expected result. see this db<>fiddle demo

Cheers!!

Upvotes: 1

Related Questions