CODEWITHSUNDEEP
postgresql
Preston G
Preston G

Reputation: 69

Window Function To Calculate Closing Percentage

I am working with a query in PSQL and I am trying to use a window function to divide two other window function counts. This is what I currently have:

WITH month_cte as ( Select generate_series(date_trunc('month', current_date) - interval '12' month, date_trunc('month', current_date), interval '1' month) as month_year
)
select DISTINCT ON (month_year, q.rep_name) month_cte.*, q.*
FROM month_CTE
LEFT JOIN (
    select *,
        CASE
        WHEN date_quoted IS NOT NULL THEN COUNT(*) OVER (PARTITION BY rep_name, date_trunc('month', date_quoted))
        ELSE NULL
        END as month_quotes,
        CASE WHEN edocs_signed_date IS NOT NULL THEN COUNT(*) OVER (PARTITION BY rep_name, date_trunc('month', edocs_signed_date))
        ELSE NULL
        END as month_sales,
        CASE
        WHEN date_quoted IS NOT NULL And edocs_signed_date IS NOT NULL THEN CAST(COUNT(*) OVER (PARTITION BY rep_name, date_trunc('month', edocs_signed_date))/
    COUNT(*) OVER (PARTITION BY rep_name, date_trunc('month', date_quoted))* 100.0 AS numeric) 
        ELSE NULL
    END as month_closing
    FROM quote_report_view
    ORDER BY rep_name, edocs_signed_date, date_quoted
    ) q
    ON (date_trunc('month', q.date_quoted) = month_cte.month_year OR date_trunc('month', q.edocs_signed_date) = month_cte.month_year)
ORDER BY month_year, rep_name, month_quotes, month_sales

The line that I am trying to get to work is the 3rd Case:

CASE WHEN date_quoted IS NOT NULL And edocs_signed_date IS NOT NULL THEN CAST(COUNT(*) OVER (PARTITION BY rep_name, date_trunc('month', edocs_signed_date))/
        COUNT(*) OVER (PARTITION BY rep_name, date_trunc('month', date_quoted))* 100.0 AS numeric) 
            ELSE NULL
        END as month_closing

I am basically trying to divide the 2nd count window function by the 1st count window function and get a percentage for month_closing.

These are my current results:

"2020-08-01 00:00:00-04"    869272  "2020-08-04 00:00:00"   "2020-08-04 00:00:00"   "Jesus" 1   1   100.0
"2020-08-01 00:00:00-04"    875518  "2020-08-19 00:00:00"   "2020-09-01 00:00:00"   "Jim"   36  1   0.0
"2020-08-01 00:00:00-04"    876462  "2020-08-04 00:00:00"   "2020-08-04 00:00:00"   "Nick"  39  12  0.0
"2020-08-01 00:00:00-04"    873572  "2020-08-04 00:00:00"   "2020-08-04 00:00:00"   "Piero" 63  36  0.0

I am only getting either 0.00 or 1.00 in my last column where I am trying to calculate the closing percentage. How can I make this work to get a true percentage?

Thanks!

Upvotes: 0

Views: 40

Answers (1)

eshirvana
eshirvana

Reputation: 24568

what is happening is that for ex using your data db engine first calculate division integer / integer ( ex 1/ 36 which result is 0 with data type of integer) then it does the multiply 0 * 100.0 ( integer * numeric which output data type is numeric but the result is 0.00

  • so either cast first count(*) to numeric
  • or multiply it by 1.0
  • or if you are calculating percentage multiply first count(*) by 100.00 first like so:
WITH month_cte as ( Select generate_series(date_trunc('month', current_date) - interval '12' month, date_trunc('month', current_date), interval '1' month) as month_year
)
select DISTINCT ON (month_year, q.rep_name) month_cte.*, q.*
FROM month_CTE
LEFT JOIN (
    select *,
        CASE
        WHEN date_quoted IS NOT NULL 
            THEN COUNT(*) OVER (PARTITION BY rep_name, date_trunc('month', date_quoted))
            ELSE NULL
        END as month_quotes,
        CASE WHEN edocs_signed_date IS NOT NULL 
            THEN COUNT(*) OVER (PARTITION BY rep_name, date_trunc('month', edocs_signed_date))
            ELSE NULL
        END as month_sales,
        CASE WHEN date_quoted IS NOT NULL And edocs_signed_date IS NOT NULL 
            THEN CAST(COUNT(*) OVER (PARTITION BY rep_name, date_trunc('month', edocs_signed_date)) * 100.0
            / COUNT(*) OVER (PARTITION BY rep_name, date_trunc('month', date_quoted)) AS numeric) 
            ELSE NULL
    END as month_closing
    FROM quote_report_view
    ORDER BY rep_name, edocs_signed_date, date_quoted
    ) q
    ON (date_trunc('month', q.date_quoted) = month_cte.month_year OR date_trunc('month', q.edocs_signed_date) = month_cte.month_year)
ORDER BY month_year, rep_name, month_quotes, month_sales

Upvotes: 2

Related Questions