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'

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

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

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!

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

yeah sorry about that just working with notepad at present...

0

JackW9653Author Commented:

Then its very impressive - thanks again.

Jack

0

Featured Post

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.

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.Encount