Calculate count of credit decision (One or multiple custom sql)

Question

Calculate count of credit decision (One or multiple custom sql)

pradeep_as400

Valued Member

Points: 63
More actions
October 6, 2021 at 6:55 am

#3936976

Hello All,
Hope you guys are doing good.
I have a requirement to take a count of decisions in the below format from the “credit decision” table.
Please find the credit decision table and expected report format. I am going to use this custom sql in tableau to generate the report/dashboard.
The business user triggers a credit decision and the response get saved in the table. One or multiple credit decision can trigger for a deal. The possible response was approved, declined or refer.
Credit decision Table:
Report Format:
Note:
Since this is going to be in Tableau, we can have separate sql for each count (i.e. approved, declined, refer etc.). Tableau has the flexibility to create separate report for each count and merge them all together in a single dashboard.
Approved: Deal 10, 13 & 14.
Declined: Deal 11, 12 & 15.
Refer: 16, 17, 18 & 19.
Refer then Approved: 16
Refer then Declined: 18
Scenario 1:
Approved or Declined: (Direct)
Ex: Deal 10 & 11. For these types of scenario, only one decision triggered during a given period and the outcome is either approved or declined.
Note: We should ignore “Refer” in this scenario. (Ex: Deal 19)
Scenario 2:
Approved or Declined: (Multiple)
Ex: Deal 12, 13, 14 & 15. For these types of scenario, multiple decisions triggered during a given period and the final outcome is either approved or declined.
Note: We should ignore “Refer” in this scenario. (Ex: Deal 16, 17 & 18)
Scenario 3:
Refer: (Single or Multiple)
We should consider deals with at least one “Refer”.
Ex: Deal 16, 17, 18 & 19. For these types of scenario, single or multiple decisions triggered during a given period and the final outcome is either approved, declined or refer.
Note: we should ignore deals without any refer. (Ex: 10, 11, 12, 13, 14 & 15)
Appreciate if you could suggest a custom sql to generate this report?

Thanks in advance.
Pradeep.
Note:
Since this is going to be in Tableau, we can have separate sql for each count (i.e. approved, declined, refer etc.). Tableau has the flexibility to create separate report for each count and merge them all together in a single dashboard.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

It's not a good logical model. What is the maximum number of rows per Deal? If it's 2 you could join the table to itself

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

pradeep_as400 Valued Member Points: 63 More actions · Answer 2

Hi, Its not restricted to 2, the deal can have any number of decisions.

Steve Collins SSCrazy Points: 2157 More actions · Answer 3

Imo you could maybe start with a query like this. The 'final_cte' CTE selects the row(s) with the final status for each Deal. Then OUTER APPLY is used to count the number of times the Deal was referred prior to the final status

;with final_cte as (
    select top 1 with ties *
    from CreditDecisionTable
    order by row_number() over (partition by Deal order by [Activity Date] desc))
select fc.Deal, fc.[Activity Date], 
       case when fc.Comments like '%Approved%' then 'Approved'
            when fc.Comments like '%Declined%' then 'Declined'
            when fc.Comments like '%Refer%' then 'Refer' else null end final_status,
       isnull(prev.ref_count, 0) prev_refer_count
from final_cte fc
     outer apply (select count(*) 
                  from CreditDecisionTable cdt
                  where fc.Deal=cdt.Deal
                        and fc.[Activity Date]<>cdt.[Activity Date]
                        and cdt.Comments like '%Refer%') prev(ref_count);

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können