I'm trying to calculate based on an alias column.

SELECT 
    Aged, NotAged, Aging
FROM
    (SELECT 
         DATEDIFF(DAY, CASE WHEN Stat = 'HOLD' THEN Created ELSE Opened END,
                      CASE WHEN Stat = 'Closed' THEN Closed ELSE GETDATE() END) AS Aged,
         DATEDIFF(DAY, CASE WHEN Aged <= 25 THEN GETDATE() AS NotAged ELSE GETDATE() END) AS Aging
     FROM 
         DM.Claim 
     INNER JOIN
         DM.LDesc ON LDescKey = LDescKey) data

How do I go about calculating based on an alias column to get NotAged and Aging?

Expected output would be Aged {1 2 35} NotAged {1 2} Aging {35}

CodePudding user response：

Without sample data and expected results it's hard to say for sure what you want (aggregation?), but you can use CROSS APPLY (VALUES to create a calculated column, and you can even chain them by referring to a previous one. For example.

SELECT 
    v1.Aged,
    v1.NotAged,
    v2.Aging
FROM
    DM.Claim AS c
INNER JOIN
    DM.LDesc AS ld ON ld.LDescKey = c.LDescKey
CROSS APPLY (VALUES (
    DATEDIFF(DAY, CASE WHEN Stat = 'HOLD' THEN Created ELSE Opened END,
                  CASE WHEN Stat = 'Closed' THEN Closed ELSE GETDATE() END,
    CASE WHEN Aged <= 25 THEN GETDATE() END
) ) AS v1(Aged, NotAged)
CROSS APPLY (VALUES (
    DATEDIFF(DAY, v1.NotAged ELSE GETDATE() END, v1.Aged)
) ) AS v2(Aging);

CodePudding user response：

Charlieface's suggestion to use CROSS APPLY does the trick.
Another option is using CTE (common table expressions), so calculated (and aliased) columns can be used in expressions downstream. Here is an example with some made up data.

with somedata as (
    SELECT
        *
    FROM ( values
        ('HOLD', '2022-06-01', '2022-06-02', '2022-07-15'),
        ('HOLD', '2022-07-01', '2022-07-02', '2022-07-15'),
        ('Closed', '2022-06-01', '2022-06-02', '2022-07-15'),
        ('Closed', '2022-07-01', '2022-07-02', '2022-07-15')
    ) vals (Stat, Created, Opened, Closed)
)
,
precalc as (
    select
        *,
        Aged = DATEDIFF(
            DAY,
            CASE WHEN Stat = 'HOLD' THEN Created ELSE Opened END,
            CASE WHEN Stat = 'Closed' THEN Closed ELSE GETDATE() END
        )
    from somedata
)
SELECT 
    Aged,
    NotAged = case when Aged <25 then Aged end,
    Aging = case when Aged >=25 then Aged end,
    *
FROM precalc

Output (20220721)