-4

I have a patient with 2 rows that have same admit and discharge dates and their admit flag is 1 and los is 14, 15 and other patient with 3 rows with different admit and discharge dates that have admit flag as 1 for all 3 rows and los 7, 11, 1 respectively for 3 rows. I want to aggregate this data and need one row per patient in the output but for 1st patient I want max(admit) = 1 and max(los) that is 15 and for 2nd patient I need admit as sum(admits) = 3 and sum(los) = 19. How can I achieve this?

Current code:

select a.LOB, a.year, a.key1, a.[age-band], a.prov_name, a.dx_catgy, 
case when (DATEDIFF(DD,B.ADMIT_DT,A.ADMIT_DT) = 0 AND a.prov_billing_tin = b.prov_billing_tin ) THEN max(a.Admits) 
ELSE sum(a.admits) end as Admits, case when (DATEDIFF(DD,B.ADMIT_DT,A.ADMIT_DT) = 0 AND a.prov_billing_tin = b.prov_billing_tin ) 
THEN max(a.LOS) ELSE sum(a.LOS) end as los,  SUM(CAST(a.[30DayREADMIT] AS INT)) as READMIT 
from #admits2    a
INNER JOIN #ADMITS2 b On 
       a.KEY1 = b.key1 and 
  --     (a.rank - b.rank = 1  OR a.rank - b.rank = 0) and
       a.lob = b.lob -- and
group by a.LOB, a.year, a.key1, a.[age-band], a.prov_name, a.dx_catgy, a.admit_dt, b.admit_dt, a.prov_billing_tin, b.prov_billing_tin

enter image description here

3
  • 1
  • 1
    I don't see any pattern to your request sorry. Your problem is a mix of different things and doesn't seem amenable to solving with SQL. Sep 14 at 20:29
  • Hi, and welcome to dba.se! Please go to dbfiddle.uk and input your table structure and data and post the link back here. I think I have an answer but I'll need to test it!
    – Vérace
    Sep 15 at 1:35
0

Your question is very poorly structured but I've had a go and obtained the desired result. I've had to make certain assumptions.

  • Assumption 1: Two (or more) admissions on the same day with the same diagnosis count as 1 admission

  • Assumption 2: There is only one valid diagnosis per admission (and/or per day)

  • Assumption 3: I'm assuming that Schizophrenia and Schizophrenia spectrum... &c. are the same thing. Maybe not medically, but for billing purposes (or at least the purpose of this query!)

  • Assumption 4: 1 and only 1 provider name per admission

  • Assumption 5: (and this is where things are tricky) For the calculation of the total of LOS per patient, I take the LOS for a given admission and add the total of Readmits (for that admission) IIF that number is negative. This one is weird but the only way that the numbers will add up!

The table and data are on the fiddle. All of the SQL below is available on the fiddle here.

The CRUX of solving this problem is not to try to do much in a single SQL statement - that's what JOINs are for.

Firstly, we want a breakdown of admissions:

SELECT
  pid, r_name, diag, a_year,
  ROW_NUMBER() OVER (PARTITION BY pid ORDER BY pid) AS rn
FROM test
GROUP BY pid, adm_date, r_name, diag, a_year
ORDER BY pid;

Result:

pid     r_name   diag   a_year  rn
0       East O   Schiz  2019    1
0       East O   Schiz  2019    2
0       East O   Schiz  2019    3
1       Bergen   Depr   2019    1
1       Bergen   Depr   2019    2
4       Hoboken  Depr   2020    1
9       Clara    Schiz  2019    1

So far, so good. We have 3 admissions for patient 0, 2 admissions for patient 1 and 1 admission each for patients 4 & 9.

Note the use of the ROW_NUMBER() window function - window functions are very powerful and if you are at the beginning of your career, you should take the time to learn them - they will repay any effort 10 times over!

  • tip of the day! When you dealing with your data, even visually with a spreadsheet or other tool, sort them first (in this case by pid - the logical key) - it does make life easier!

So, now we want to pull out all of the patient details and their number of admissions per year:

SELECT
  pid, r_name, diag, a_year, MAX(rn) AS admits -- << gets the total no. of admissions!
FROM
(
  SELECT
    pid, adm_date, r_name, diag, a_year,
    ROW_NUMBER() OVER (PARTITION BY pid ORDER BY pid) AS rn
  FROM test
  GROUP BY pid, adm_date, r_name, diag, a_year
) AS tab1
GROUP BY pid, r_name, diag, a_year
ORDER BY pid;

Result:

pid     r_name  diag    a_year  admits
0       East O  Schiz   2019    3
1       Bergen  Depr    2019    2
4      Hoboken  Depr    2020    1
9        Clara  Schiz   2019    1

So, we can see that we are nearly there! We have 4 entries, with the correct number of admissions per patient!

Now, we must calculate the LOS and the 30 day Readmits per patient which we do as follows:

SELECT 
  pid,  
  SUM(los) + 
  SUM
  (  
    CASE
      WHEN readmit > 0 THEN 0
      ELSE readmit
    END
  ) AS los,
  SUM(d30_r) AS readm_30
FROM test
GROUP BY pid;

Result:

pid     los     readm_30
  0     19         1
  1     2          1
  4     2          0
  9     15         0

Note the use of the CASE within the SUM() - this can be very useful in cases like this (excuse the pun!).

So now we join our two derived (from the original data) tables to each other based on the pid field as follows (I'll leave the cleaning up of the SQL (field ordering &c. to you):

SELECT 
  tab2.*, tab3.*
FROM
(
  SELECT
    pid, r_name, diag, a_year, MAX(rn) AS admits
  FROM
  (
    SELECT
      pid, adm_date, r_name, diag, a_year,
      ROW_NUMBER() OVER (PARTITION BY pid ORDER BY pid) AS rn
    FROM test
    GROUP BY pid, adm_date, r_name, diag, a_year
  ) AS tab1
  GROUP BY pid, r_name, diag, a_year
) AS tab2
JOIN
(
  SELECT 
    pid,  
    SUM(los) + 
    SUM
    (  
      CASE
        WHEN readmit > 0 THEN 0
        ELSE readmit
      END
    ) AS los,
    SUM(d30_r) AS readm_30
  FROM test
  GROUP BY pid
) AS tab3
ON tab2.pid = tab3.pid;

Result:

pid     r_name   diag   a_year  admits  pid     los     readm_30
  0     East O   Schiz  2019         3    0     19        1
  1     Bergen   Depr   2019         2    1      2        1
  4     Hoboken  Depr   2020         1    4      2        0
  9     Clara    Schiz  2019         1    9     15        0

Which is the desired result!

Included in the fiddle is also a solution which makes use of a Common Table Expression (or CTE). These, like window functions, are powerful tools and make for simpler, more readable SQL.

A few points:

  • you should avoid the use of images for the reasons explained in this link,

  • you should always include your server version, either as a tag or in the text of the question. I used the oldest version available on dbfiddle - but as far as I know, window functions - the only non-SQL 1992 that I used works from at least SQL Server 2005 - see here.

  • and finally, you should always provide a fiddle (dbfiddle.uk) with your tables and data for two reasons - 1) it provides a single source of truth and 2) it eliminates duplication of effort on the part of those trying to answer the question - help us to help you!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.