Subquery Structures SQL

Question

New to SQL and I'm having using with query structures.

Let's try asking this again.

I'm attempting to nest two separate codes so the final output looks like:

FIN   Level1 Level2 Level 3             FCT_MAX   FCT_MAX_Charges    CPT CPT_Charges  DELTA 
1     1        0       1                9284       200                9283    100        100
2     0        1       1                9284       200                9281     50         150     

The first code (call it code X) produces the following table. The purpose of this code is to concatenate the records with the same FIN into one row, and to create the DELTA variable and CHARGES variable.

USE EMTCQIData

SELECT FIN, Clinical, FCT_MAX, FCT_MAX_CHARGES, CPT, CPT_CHARGES, 
(FCT_MAX_CHARGES - CPT_CHARGES) AS DELTA

FROM
(
SELECT CONC.[Financial Number] as FIN, CONC.Clinical, 

   CASE
          WHEN CONC.Clinical like '%level 5%' THEN '99285' 

   END FCT_MAX,

   CASE
          WHEN CONC.Clinical like '%level 5%' THEN CAST($3768 AS int) 
   END FCT_MAX_CHARGES,

   CPT.CPT,

   CASE
          WHEN CPT.CPT like '99285' THEN CAST($3768 AS int) 
   END CPT_CHARGES
   FROM CPT_MASTER as CPT

 INNER JOIN 

(
SELECT DISTINCT ST2.[Financial Number], 
SUBSTRING(
    (
        SELECT ','+ST1.[Clinical Event Result]  AS [text()]
        FROM ED_FCT_Q1_FY19 ST1
        WHERE ST1.[Financial Number] = ST2.[Financial Number] AND ST1.[Clinical Event Result]<> 'No'
              Order BY [Financial Number] 
        FOR XML PATH ('')
    ), 2, 1000) [Clinical]
FROM ED_FCT_Q1_FY19 ST2
)
as CONC ON CONC.[Financial Number]=CPT.FIN
WHERE CPT.CPT LIKE '9928%'
)
AS COMPLETE

ORDER BY FIN

TABLE FROM CODE X

FIN   Clinical                              FCT_MAX    FCT_MAX_Charges    CPT   CPT_Charges    DELTA 
1     Discharge[Level1] Transfer[Leve3]         9284       200           9283     100          100

The only change from this table I want is for the Clinical column to be parsed out by levels. So for example, any clinical Level 1 event would have its own column, likewise for level 2, level3 all the way up to level 5 events.

To parse out the events, I developed another code (CODE Z)see below, this code parses out the CLINICAL EVENT COLUMN; however, it does not concatenate the columns, below is example output:

FIN          Level1     Level 2     Level3   ...                                      
1            1            0            1
1            0            0            1
2            1            1            1
3            0            0            1
3            1            1            1

CODE Z

SELECT [Financial Number], [Clinical Event Result],

CASE
   WHEN [Clinical Event Result] like '%1:1 Nursing care%' Then '1'
      ELSE '0'
End [1:1 Nursing Care]
...

FROM ED_FCT_Q1_FY19

MY SOURCE DATA: ED_FCT_Q1_FY19 has the following columns- Financial Number, Clinical Event Result, Charge code This data contains the same Financial Number in multiple rows

CPT_MASTER- FIN, CPT This data contains the same FIN in multiple rows

As aforementioned, the final desired table will contain unique FIN/Financial ID only. The FIN can be linked to the Financial ID.

Answer 1

If I'm understanding what you're asking... you could put query B into a temp table, and join it to Query A which would limit the results, provided there is a common key that would allow a join.

SELECT FIN, Clinical, FCT_MAX, FCT_MAX_CHARGES, CPT, CPT_CHARGES, 
(FCT_MAX_CHARGES - CPT_CHARGES) AS DELTA
into #QueryB
FROM
(
SELECT CONC.[Financial Number] as FIN, CONC.Clinical, 

   CASE
          WHEN CONC.Clinical like '%level 5%' THEN '99285' 
   END FCT_MAX,

   CASE
          WHEN CONC.Clinical like '%level 5%' THEN CAST($3768 AS int) 
   END FCT_MAX_CHARGES,

   CPT.CPT,

   CASE
          WHEN CPT.CPT like '99285' THEN CAST($3768 AS int) 
   ND CPT_CHARGES


 FROM CPT_MASTER as CPT

INNER JOIN 

(
SELECT DISTINCT ST2.[Financial Number], 
SUBSTRING(
    (
        SELECT ','+ST1.[Clinical Event Result]  AS [text()]
        FROM ED_FCT_Q1_FY19 ST1
        WHERE ST1.[Financial Number] = ST2.[Financial Number] AND ST1. 
[Clinical Event Result]<> 'No'--removing No's from Clinical that appeared in 
the begining of the rows
              Order BY [Financial Number] 
        FOR XML PATH ('')
    ), 2, 1000) [Clinical]
FROM ED_FCT_Q1_FY19 ST2
)
as CONC ON CONC.[Financial Number]=CPT.FIN
WHERE CPT.CPT LIKE '9928%'
)
as complete 

Then Join it to Query A to limit the results of A to those in B

SELECT [Financial Number], [Clinical Event Result],

CASE
   WHEN [Clinical Event Result] like '%1:1 Nursing care%' Then '1'
      ELSE '0'
End [1:1 Nursing Care],
....
FROM ED_FCT_Q1_FY19 a
join #QueryB b on a.[some criteria] = b.[some criteria]

In fact, you can create temp tables of all of those nested queries and then join them, just like it's done with Query B. I find that temp tables are much faster when pulling from larger data sets multiple times as you are doing, because it grabs the data and stores it, rather than running a nested select every time.