Solved

# Combining Select with Update

Posted on 2006-06-02
242 Views
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
Question by:JackW9653

LVL 28

Expert Comment

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

LVL 50

Expert Comment

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

Author Comment

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

LVL 50

Accepted Solution

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

Author Comment

With a couple of syntax tweaks it worked great LFS - thank you very much!

Jack
0

Author Comment

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

LVL 50

Expert Comment

0

Author Comment

Then its very impressive - thanks again.

Jack
0

## Featured Post

### Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.