Selecting Case statement in SQL Server

Question

I am trying to add a case statement to my query using SQL Server. I had a very long query that I have basically selected into a temporary table #Step1 giving the following table.

+---+------------+-------------+-----------------+---------------+
|   |     LOB    | TechPrem    |  Label          |  Data         |
+---+------------+-------------+-----------------+---------------+
| 1 | AOP        | Yes         |  ADjAAL         |    40331      |
| 1 | Boiler     | Yes         |  AdjAAL         |     0         |
| 1 | TRIA       | NO          |  AdjAAL         |     0         |
| 1 | AOP        | Yes         |  PureAAL        |    11904      |
| 1 | Boiler     | Yes         |  PureAAL        |     775       |
+---+------------+-------------+---------------- +---------------+ 

My doubt here is, looking at the above table, I want to select a case statement where if the 'TechPrem' is 'Yes' for AOP & Boiler, then my Query 1 should execute, else if 'TechPrem' is 'No' for AOP & Boiler, then Query 2 should execute. Any suggestions or thoughts on this would be helpful

Query 1 :

 SELECT  
     FileID, 
     SUM(CAST(REPLACE(Data,',','.') AS FLOAT)) AS Summed_AAL_Attri
FROM 
    (SELECT 
         *, 
         ROW_NUMBER() OVER (PARTITION BY FileID, "LOB" ORDER BY "Label" ASC) AS rn
     FROM 
         DATA WITH (NOLOCK)  
     WHERE
         Label IN ('AdjAAL') 
         AND LOB IN ('AOP', 'Boiler', 'TRIA')) AS t
WHERE
    t.rn = 1
    AND FileID = 1
GROUP BY 
    FileID

Expected answer if 'Yes' : 403301

Query 2:

SELECT  
    FileID, 
    SUM(CAST(REPLACE(Data,',','.') AS FLOAT)) AS Summed_AAL_Attri
FROM 
    (SELECT 
         *, 
         row_number() over (partition by FileID, "LOB" ORDER BY "Label" ASC) as rn
     FROM 
         DATA WITH (NOLOCK)  
     WHERE 
         Label IN ('AdjAAL','PureAAL') 
         AND LOB IN ('AOP', 'Boiler', 'TRIA')) AS t
WHERE
    t.rn = 1
    AND FileID = 1
GROUP BY 
    FileID

Expected answer if 'No' : 41106

Answer 1

Running different queries based on a case is not something SQL Server supports, however since I only see one difference in these queries in the WHERE clause of the sub-query, you can use the case statement there.

 SELECT  
     FileID, 
     SUM(CAST(REPLACE(Data,',','.') AS FLOAT)) AS Summed_AAL_Attri
FROM 
    (SELECT 
         *, 
         ROW_NUMBER() OVER (PARTITION BY FileID, "LOB" ORDER BY "Label" ASC) AS rn
     FROM 
         DATA WITH (NOLOCK)  
     WHERE
         Label IN ('AdjAAL', CASE TechPrem
            WHEN 'YES' THEN ''
            WHEN 'NO' THEN 'PureAAL'
            END)
         AND LOB IN ('AOP', 'Boiler', 'TRIA')) AS t
WHERE
    t.rn = 1
    AND FileID = 1
GROUP BY 
    FileID