count of decisions for different scenarios

Question

Hope you are all doing good and safe.

I am working on a reporting requirement to get the count of all approved, declined and referred deals from the system.

i.e. count of all approved, declined and referred deals for a given period.

The table (decision) attached has the data of all decisions.

The expected report for the data attached:

Approved: Deals approved without refer or decline directly.

Declined: Deals declined directly without refer.

Refer: Deals referred at least once.

Refer later approved: Referred deals later approved.

Refer later declined: Referred deals later declined.

The sql which I use to get the data for approved and declined. I fetch the data for both approved and declined and later apply filter in tableau to get the count.

select nt.*
from  (
        SELECT deal, COUNT(deal) AS countOfCt
        FROM decision
        where subject like '%Credit Decision%'
        and comments like '%DecisionCredit%'
        and activity_dt between '2021-06-01' and '2021-06-30'
        GROUP BY deal
        ) as nt2,
    note nt
where nt.deal=nt2.deal
and nt2.countOfCt=1 
and subject like '%Credit Decision%'
and comments like '%DecisionCredit%'
and activity_dt between '2021-06-01' and '2021-06-30'
order by nt.deal, nt.activity_dt

I came across a scenario (deal: 12) where decision triggered more than once and both the time it got approved.

I am struggling to amend the sql to cover this particular scenario. Can you check and suggest?

Note: I am planning to use a separate custom sql to get referred deals.

Thanks in advance.

EDIT: UPDATED:

As there are restrictions in using parameters with common table expressions in tableau, Is there a way to create the custom sql without CTE's?.

I removed the inconsistent database tags. Please tag only with the database you are really using. — Gordon Linoff, Sep 13 '21 at 11:23
[Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — RiggsFolly, Sep 13 '21 at 11:26
Recommendation: Don't operate on strings, prefer having appropriate integral constants instead. This will improve performance. — Aconcagua, Sep 13 '21 at 12:17

score 1 · Answer 1 · answered Sep 13 '21 at 11:31

Hmmm . . . I would start by indicating the status of each row. Then combine that at the deal level and summarize:

with d as (
      select d.*,
             (case when comments like '%Auto Approved%' then 'Approved'
                   when comments like '%Auto Refer%' then 'Refer'
                   when comments like '%Auto Declined%' then 'Declined'
              end) as decision       
      from decision d
      where subject = 'Credit Decision'
     ) 
select sum(case when last_decision = 'approved' and num_rows = 1 then 1 else 0 end) as approved_only,
       sum(case when last_decision = 'decline' and num_refers = 0 then 1 else 0 end) as declined_no_refers,
       sum(case when refer > 0 then 1 else 0 end) as num_refers,
       sum(case when last_decision = 'approved' and num_refers > 0 then 1 else 0 end) as approved_with_refer,
       sum(case when last_decision = 'declined' and num_refers > 0 then 1 else 0 end) as declined_with_refer,
from (select d.deal, count(*) as num_rows,
             max(case when seqnum = 1 then decision end) as last_decision,
             sum(case when decision = 'refer' then 1 else 0 end) as num_refers
      from (select d.*,
                   row_number() over (partition by deal order by activity_date desc) as seqnum
            from d
           ) d
      group by deal
     ) d

Thanks Gordon, Let me test this in detail and update you on the outcome. Appreciate your help. — Prdp, Sep 14 '21 at 05:12
Hi Gordon, As there are restrictions in using parameters with common table expressions in tableau, Is there a way to create the custom sql without CTE's?. — Prdp, Sep 14 '21 at 09:06
@Prdp . . . Parameters can only be used for "constant" values, not identifiers, function names, keywords and so on. You can incorporate the CTE as a subquery. — Gordon Linoff, Sep 14 '21 at 12:20

count of decisions for different scenarios

1 Answers1