Count of credit decision using multiple custom sql

Other SQL Server Topics
1

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:

Capture
Capture749×363 6.14 KB

Report Format:
Approved: 3
Declined: 3
Refer: 4
Refer then Approved: 1
Refer then Declined: 1

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.

2

Welcome. Please post your data not as an image but something we can use to test on our sql server without having to retype it all from scratch. help us help you

--DDL
declare @sample table(dealt in, subject varchar(50), activityDate datetime,
comments varchar(2500))

--DML
insert into @sample
select 10, 'Credit Decision', '2021-01-09 12:00', 'Decision------' union
select 12, 'Credit Decision', '2021-01-09 14:00', 'Decision------'

etc

3

This strikes me as inappropriate, as if you are hoping to have a volunteer do a major development task for you. If I am incorrect, do you have a specific question or problem you are having? What have you tried?