[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

Combining Select with Update

Hello Experts,

I run the following query to strip of the description and calculate the total minutes for specific groups of Treatments based on a specific Encounter_code and populate a datagrid.

SELECT Encounter_code,
rtrim(left(Treatment, charindex('-', Treatment)-1)) As Treatment, sum(ActualMins)As Actual,
sum(ContactMins)As Contact, sum(Units)As Units
FROM tblTreatmentsGiven
WHERE tblTreatmentsGiven.Encounter_code = '75015'
GROUP BY Encounter_code, rtrim(left(Treatment, charindex('-', Treatment)-1))

I now need to run an Update query (see example below) against the results of the above query to show the total units based on the total minutes for each Treatment.

UPDATE tblTreatmentsGiven
SET Units=
CASE       WHEN ContactMins <8 THEN 0
      WHEN ContactMins >=8 AND ContactMins <=22 THEN 1
           WHEN ContactMins >=23 AND ContactMins <=37 THEN 2
      WHEN ContactMins >=38 AND ContactMins <=52 THEN 3
      WHEN ContactMins >=53 AND ContactMins <=67 THEN 4
      WHEN ContactMins >68 THEN 5
END
WHERE tblTreatmentsGiven.Encounter_code = '75015'

Is there a way to combine the 2 queries into 1?

Thanks for any help,

Jack
0
JackW9653
Asked:
JackW9653
  • 4
  • 3
1 Solution
 
imran_fastCommented:

UPDATE tblTreatmentsGiven
SET Units=
CASE      WHEN Actual <8 THEN 0
     WHEN Actual >=8 AND Actual <=22 THEN 1
          WHEN Actual >=23 AND Actual <=37 THEN 2
     WHEN Actual >=38 AND Actual <=52 THEN 3
     WHEN Actual >=53 AND Actual <=67 THEN 4
     WHEN Actual >68 THEN 5
END
from

(SELECT Encounter_code,
rtrim(left(Treatment, charindex('-', Treatment)-1)) As Treatment, sum(ActualMins)As Actual,
sum(ContactMins)As Contact, sum(Units)As Units
FROM tblTreatmentsGiven

GROUP BY Encounter_code, rtrim(left(Treatment, charindex('-', Treatment)-1))) b

WHERE tblTreatmentsGiven.Encounter_code = '75015'and  tblTreatmentsGiven.Encounter_code = b.Encounter_code
0
 
LowfatspreadCommented:
its not clear to me what you require...

can you give some before and after data examples...

it may also help if you state the requirement in business terms...

0
 
JackW9653Author Commented:
The current system captures the contact minutes for each Treatment as the clinician enters it. For example:

Encounter_Code      Treatment                  ActualMins                             ContactMins      Units
75015            97110 - Knee Flex – Sit            10            8      0
75015            97110 - Hip Abd - S-lie            10            8      0
75015            97110 - Gastroc Strch – Step            10            7      0
75015            97010 - Hot Pack                  15            8      0
75015            97014 - E-STIM (UNAT)             15            10      0
75015            97012 - Txn - Cervical - 45lbs                      15            8      0

The first query removes the description from the treatment and totals the times for each Treatment group. So the returned data looks like:

Encounter_Code      Treatment             ActualMins      ContactMins   Units
75015            97110                 30                  23             0
75015            97010                 10                   8             0
75015            97014                 15                  10              0
75015            97012                 15                    8             0

I need the second query to evaluate the total ContactMins for each Treatment and Update the Units accordingly. So the data would look like:

Encounter_Code      Treatment             ActualMins      ContactMins    Units
75015            97110            30            23                   2
75015            97010            10             8             1
75015            97014            15            10             1
75015            97012            15             8             1

It then inserts the data into a tblTreatmentSummary where the actual charges are applied based on the Treatment code and the number of units . Hope that makes more sense.

Imran - your solution was one i had tried myself (using ContactMins as opposed to your use of ActualMins) but the problem is the calculations are done before the groupings are made. So for each Treatment that has more than 1 row and the ContactMins are less than 8 the Unit calculation is skewed. It often occurs where the are several exercises/treatments that are done that are 2-5 minutes in duration. If you had 3 - 5 minute treatments, in your solution the total units would be 0 instead of the 1 for 15 minutes of Treatment it should return. So the query has to total each Treatment grouping before calculating the Units.


0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LowfatspreadCommented:
like this then ...

Insert into tblTreatmentSummary
     (encounter_Code,Treatment,actualMins,contactmins,units)
Select Encounter_code
      ,Treatment
      ,Actual
      ,Contact
      ,CASE WHEN ContactMins <8 THEN 0
            WHEN ContactMins Between 8 AND 22 THEN 1
            WHEN ContactMins between 23 AND 37 THEN 2
            WHEN ContactMins between 38 AND 52 THEN 3
            WHEN ContactMins between 53 AND 67 THEN 4
            WHEN ContactMins >68 THEN 5
            END  
  From (
SELECT Encounter_code      
      .T.Treatment      
      ,sum(ActualMins)As Actual
      ,sum(ContactMins)As Contact
  FROM (select Encounter_code
             ,ActualMins
             ,ContactMins    
             ,rtrim(left(Treatment, charindex('-', Treatment)-1)) As Treatment
         from  tblTreatmentsGiven
       ) as T
 WHERE t.Encounter_code = '75015'
 GROUP BY Encounter_code, Treatment
       ) as X


it wasn't clear originally which table and row you where trying to update...

hth
0
 
JackW9653Author Commented:
With a couple of syntax tweaks it worked great LFS - thank you very much!

Jack
0
 
JackW9653Author Commented:
The working code:

Insert into tblTreatmentSummary
     (encounter_Code,Treatment,ActualMins,ContactMins,units)
Select Encounter_code
      ,Treatment
      ,Actual
      ,Contact
      ,CASE WHEN Contact <8 THEN 0
            WHEN Contact Between 8 AND 22 THEN 1
            WHEN Contact between 23 AND 37 THEN 2
            WHEN Contact between 38 AND 52 THEN 3
            WHEN Contact between 53 AND 67 THEN 4
            WHEN Contact >68 THEN 5
            END  
  From (
SELECT Encounter_code    
      ,Treatment    
      ,sum(ActualMins)As Actual
      ,sum(ContactMins)As Contact
  FROM (select Encounter_code
             ,ActualMins
             ,ContactMins    
             ,rtrim(left(Treatment, charindex('-', Treatment)-1)) As Treatment
         from  tblTreatmentsGiven
       ) as T
 WHERE t.Encounter_code = '76209'
 GROUP BY Encounter_code, Treatment
       ) as X
0
 
LowfatspreadCommented:
yeah sorry about that just working with notepad at present...
0
 
JackW9653Author Commented:
Then its very impressive - thanks again.

Jack
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now