CODEWITHSUNDEEP
sqlsql-server
Abhishek Kapoor
Abhishek Kapoor

Reputation: 21

I want to use dynamic result in a different query

I have been working on a query that would use the top average from one query and compare it with the value that is already been stored in a different column.

The first query is as follows

SELECT  top 1
        CONVERT(varchar, md_settlementdate, 23) [Day of Year], 
        SUM(MD_KVA)/8 [Average KVA30], 
        SUM(MD_KVA15)/8 [Average KVA15]
                
FROM tblMeter INNER JOIN dbX.dbo.tblMeterData ON M_ID = MD_MeterID
WHERE   M_MeterRef = @NMI
        AND MD_SettlementDate >= @startdate -- 366
        AND MD_SettlementDate <= @enddate
        AND MD_KVA >= 0
        --AND (CONVERT(TIME,md_settlementdate,108) BETWEEN '17:30:00.0000000' AND '21:00:00.0000000') 
GROUP BY CONVERT(varchar, md_settlementdate, 23)
ORDER BY [Average KVA30] desc

This is the result

Day of Year Average KVA30 Average KVA15
2019-12-24 300.785712 314.308450

My second query is

    Select C_Name, M_MeterRef  , 
MI_InvoiceNo,MI_StartDate, 
MI_AccountNo, IC_Description,
ICHT_Description, IC_Qty, IC_AuditQty, IC_Qty - IC_AuditQty As Variance
    from tblMeter
    inner join rest_of_Tables

The result is basically all the columns that I have called for.

What I really want is that the M_MeterRef in table 1 matches with M_MeterRef in the second table and the last column has the Top 1 average for that particular M_MeterRef

I have tried using nested queries, CASE but I have no luck.

My tried query which had no luck

Select C_Name, M_MeterRef m , MI_InvoiceNo,MI_StartDate, MI_AccountNo, IC_Description,ICHT_Description, IC_Qty, IC_AuditQty, IC_Qty - IC_AuditQty As Variance,
 case when N_ID = 1556 then (SELECT  top 1
        
        SUM(MD_KVA15)/8 [Average KVA15]
        
FROM tblMeter INNER JOIN dbCES_MeterUpload.dbo.tblMeterData ON M_ID = MD_MeterID
WHERE   M_MeterRef = @NMI
        AND MD_SettlementDate >= @startdate -- 366
        AND MD_SettlementDate <= @enddate
        AND MD_KVA >= 0
        
        AND (CONVERT(TIME,md_settlementdate,108) BETWEEN '17:30:00.0000000' AND '21:00:00.0000000') /* REST OF SA */
GROUP BY CONVERT(varchar, md_settlementdate, 23)
ORDER BY [Average KVA30] desc) END AS X

and rest being the same. I get a value that is static. I want the query to first match with MeterRef in the second query and the show the value for that MEterRef

Upvotes: 1

Views: 64

Answers (1)

ElapsedSoul
ElapsedSoul

Reputation: 825

Select C_Name, M_MeterRef  , 
MI_InvoiceNo,MI_StartDate, 
MI_AccountNo, IC_Description,
ICHT_Description, IC_Qty, IC_AuditQty, IC_Qty - IC_AuditQty As Variance,a.KVA30
    from tblMeter b ,(SELECT  top 1
        CONVERT(varchar, md_settlementdate, 23) [Day of Year], M_MeterRef ,
        SUM(MD_KVA)/8 [Average KVA30], 
        SUM(MD_KVA15)/8 [Average KVA15]
FROM tblMeter group by M_MeterRef ,CONVERT(varchar, md_settlementdate, 23)) a
where a.M_MeterRef =b.M_MeterRef

I guess this is what you want?

Upvotes: 1

Related Questions