Solved

# Combining Select with Update

Posted on 2006-06-02

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