How do you do a where clause with a select statement as a value

user_5734793 0
Viewed 33

I'm trying to get the total amount from the next month's record.

SELECT TOP (1000) 
    [DEMAND_POINT_ID],
    [DPM_XREF_ID],
    [UTIL_TYPE],
    [ACCOUNT_ID],
    [REV_YR_MNTH],
    [TOTAL_AMOUNT],
    (SELECT CAST(LEFT((SELECT CONVERT(varchar, DATEADD(month, 1, LEFT(REV_YR_MNTH, 4) + '-' + RIGHT(REV_YR_MNTH, 2) + '-' + '01'), 112)), 6) AS int)) AS nextmonth,
    (SELECT de2.[TOTAL_AMOUNT]
     FROM [DEQEnergyUsage].[dbo].[DST_ENERGY_USAGE_AGGR] de2
     WHERE de2.ACCOUNT_ID = de.ACCOUNT_ID 
       AND de2.REV_YR_MNTH = (SELECT CAST(LEFT((SELECT CONVERT(varchar, DATEADD(month, 1, LEFT(REV_YR_MNTH, 4) + '-' + RIGHT(REV_YR_MNTH, 2) + '-' + '01'), 112)), 6) AS int))) AS nextamount
FROM 
    [DEQEnergyUsage].[dbo].[DST_ENERGY_USAGE_AGGR] de
WHERE
    ACCOUNT_ID =1 
ORDER BY 
    ACCOUNT_ID, de.REV_YR_MNTH

The value nextmonth creates the next month's rev_yr_mnth correctly however when I try to use this created value in the where clause it brings back nothing in the nextamount field.

enter image description here

It's like it does not recognize the created value as a true value. What do I need to do to have the where clause recognize the value?

where-clause sql
1 Answers

Using CTE you can add condition on nextmonth.

With CTE As (
SELECT TOP (1000) 
    [DEMAND_POINT_ID],
    [DPM_XREF_ID],
    [UTIL_TYPE],
    [ACCOUNT_ID],
    [REV_YR_MNTH],
    [TOTAL_AMOUNT],
    (SELECT CAST(LEFT((SELECT CONVERT(varchar, DATEADD(month, 1, LEFT(REV_YR_MNTH, 4) + '-' + RIGHT(REV_YR_MNTH, 2) + '-' + '01'), 112)), 6) AS int)) AS nextmonth,
    (SELECT de2.[TOTAL_AMOUNT]
     FROM [DEQEnergyUsage].[dbo].[DST_ENERGY_USAGE_AGGR] de2
     WHERE de2.ACCOUNT_ID = de.ACCOUNT_ID 
       AND de2.REV_YR_MNTH = (SELECT CAST(LEFT((SELECT CONVERT(varchar, DATEADD(month, 1, LEFT(REV_YR_MNTH, 4) + '-' + RIGHT(REV_YR_MNTH, 2) + '-' + '01'), 112)), 6) AS int))) AS nextamount
FROM 
    [DEQEnergyUsage].[dbo].[DST_ENERGY_USAGE_AGGR] de
WHERE
    ACCOUNT_ID =1 
ORDER BY 
    ACCOUNT_ID, de.REV_YR_MNTH
)
select * from CTE
-- Where nextmonth condition
user_62032110
Related