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.

Since you're "trying" and "having difficulties", please post the code you tried and the error you got. It's impossible to tell what you're trying to do from what you've posted so far. — Tab Alleman
– Tab Alleman, Commented Dec 13, 2018 at 18:12
Thanks @TabAlleman! I updated my post above to reflect my failed attempts. My apologies for not following the posting etiquette! Appreciate your time and response! — Raven
– Raven, Commented Dec 13, 2018 at 18:30
Your last case doesn't have an "END" for starters.... it just has "ND". — pmbAustin
– pmbAustin, Commented Dec 13, 2018 at 19:03
@pmbAustin, sorry, that was an error in copying and pasting. — Raven
– Raven, Commented Dec 13, 2018 at 19:11
For what you commented on JaninCode's answer, this actually seems to be an XY Problem; why don't you reword your question asking what you're trying to do, what is your source data and desired results, and then your code as an attempr to solve it? — Josh Part
– Josh Part, Commented Dec 13, 2018 at 19:17

JenInCode · Accepted Answer · 2018-12-13 18:18:09Z

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.

answered Dec 13, 2018 at 18:18

JenInCode

2502 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

pmbAustin Over a year ago

Why not use a CTE instead of a temp table?

JenInCode Over a year ago

Feel free to provide that solution. That is kind of what is happening initially, in Query B, so I felt it was useful to provide another way to break things down. Sometimes having a ton of nested queries is very complicated and hard to debug, whereas creating individual data sets and joining them is more straightforward and easier to validate step by step.

Raven Over a year ago

Thanks @JenInCode, your solution is definitely a step in the right direction however, due to my lack-luster job explaining the situation things are more complicated. The QUERY A code contains multiple records with the same [Financial Number]. Query B concatenates the multiple rows into one row. For example, someone with [Financial Number] 1 containing 4 records is concatenated into one row. Anyway with the code you kindly provided, I'm see multiple records for the [Financial Number] variable. In the results, I'm also missing the CPT CPT_MAX, etc variables.

Collectives™ on Stack Overflow

Subquery Structures SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related