Make inner query to delete duplicated code

Question

I have redshift script, where I have duplicated code

Here is script

    SELECT  
       CASE
              WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >120 THEN '120 + Days'
              WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >90 THEN '90 - 120 Days'
              WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >60 THEN '60 - 90 Days'
              WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >30 THEN '30 - 60 Days'
              WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) IS NULL THEN '90 + Days'
              ELSE '0 - 30 Days'
       END AS OpenedAging,
       be.ClientId,
       cc.FullName AS ClientName,
       SUM(CASE
              WHEN (bes.UseAgreedView) = 1 THEN a.ChargeTotalAgreed
              ELSE a.ChargeTotal
       END - a.PaymentAdjustment - a.PaymentAmount) AS Owed
FROM    public.billing_entries be
LEFT JOIN
       (
       SELECT  bed.BillingEntryId,
               bed.PaymentCount,
               bed.PaymentRegular,
               bed.Copay,
               bed.ChargeTotal,
               bed.ChargeTotalAgreed,
               bed.PaymentAdjustment,
               bed.PaymentRegular + bed.PaymentCopay - bed.PaymentAdjustment AS PaymentAmount
       FROM    public.billing_entry_dimensions bed
       ) a
ON      a.BillingEntryId = be.Id
LEFT JOIN
       public.billing_settings bes
ON     bes.OrganizationId = be.OrganizationId
LEFT JOIN
       public.contact_insurances cic
ON     cic.Id = be.insuranceId
LEFT JOIN
       public.insurance ic
ON     ic.Id = cic.InsuranceCompanyId
LEFT JOIN
       public.contact_addresses ca
ON     ca.Id = be.ClientLocationId

LEFT JOIN       
       public.contacts cc
ON     cc.Id = be.ClientId

WHERE   be.OrganizationId = 65277
       AND be.IsVoid IS NULL
       AND be.IsDeleted IS NULL

GROUP BY        
       CASE
       WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >120 THEN '120 + Days'
       WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >90 THEN '90 - 120 Days'
       WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >60 THEN '60 - 90 Days'
       WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >30 THEN '30 - 60 Days'
              WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) IS NULL THEN '90 + Days'
              ELSE '0 - 30 Days'
       END,
       be.ClientId,
       cc.FullName

As you see I have this code duplicated at SELECT and in GROUP BY

CASE WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >120 THEN '120 + Days' WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >90 THEN '90 - 120 Days' WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >60 THEN '60 - 90 Days' WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >30 THEN '30 - 60 Days' WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) IS NULL THEN '90 + Days' ELSE '0 - 30 Days' END

How I can make it inner query and alias it in SELECT and GROUP BY correctly?

Have you tried using an additional table with ranges instead of hardcoding the case expression? You can likely create that table as CTE like with OpenedAgingRanges (from,to,display) as (select 0,30,'' union all select 30,60,'30-60 Days') union all ... ) select ..., r.display from ... left join OpenedAgingRanges r on DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >= r.from and DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) < r.to — Corion

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2018-10-18T08:01:51

Unfortunately, an alias defined in a SELECT clause isn't visible to a GROUP BY clause at the same level. The only option which comes to mind would be to wrap your current query in a CTE, and then subquery it with aggregation, reusing the alias in the CASE expression:

WITH cte AS (
    SELECT  
        CASE
            WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >120 THEN '120 + Days'
            WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >90 THEN '90 - 120 Days'
            WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >60 THEN '60 - 90 Days'
            WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >30 THEN '30 - 60 Days'
            WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) IS NULL THEN '90 + Days'
            ELSE '0 - 30 Days'
        END AS OpenedAging,
        be.ClientId,
        cc.FullName AS ClientName,
        bes.UseAgreedView,
        a.ChargeTotalAgreed,
        a.ChargeTotal,
        a.PaymentAdjustment,
        a.PaymentAmount
    FROM public.billing_entries be
    LEFT JOIN
    (
        SELECT
            bed.BillingEntryId,
            bed.PaymentCount,
            bed.PaymentRegular,
            bed.Copay,
            bed.ChargeTotal,
            bed.ChargeTotalAgreed,
            bed.PaymentAdjustment,
            bed.PaymentRegular + bed.PaymentCopay - bed.PaymentAdjustment AS PaymentAmount
        FROM public.billing_entry_dimensions bed
    ) a
        ON a.BillingEntryId = be.Id
    LEFT JOIN public.billing_settings bes
        ON bes.OrganizationId = be.OrganizationId
    LEFT JOIN public.contact_insurances cic
        ON cic.Id = be.insuranceId
    LEFT JOIN public.insurance ic
        ON ic.Id = cic.InsuranceCompanyId
    LEFT JOIN public.contact_addresses ca
        ON ca.Id = be.ClientLocationId
    LEFT JOIN public.contacts cc
        ON cc.Id = be.ClientId
    WHERE
        be.OrganizationId = 65277 AND
        be.IsVoid IS NULL         AND
        be.IsDeleted IS NULL
)

And here is the actual query using the above CTE:

SELECT  
    OpenedAging,
    ClientId,
    ClientName,
    SUM(CASE WHEN UseAgreedView = 1
             THEN ChargeTotalAgreed
             ELSE a.ChargeTotal END - PaymentAdjustment - PaymentAmount) AS Owed
FROM cte
GROUP BY
    OpenedAging,
    ClientId,
    ClientName;

Make inner query to delete duplicated code

2 Answers