Select dc.Patient_ID,dc.Department,count(dc.Amount) as "count",
  sum(case
    when dc.department in ('IMG','PAT','PHY') then (dc.amount * ic.Cost_Per_Unit) else 0
    end) as "Indirect Costs"
from DirectCosts dc
  left join IndirectCosts ic on dc.Department = ic.department
group by dc.Patient_ID,dc.Department
order by Patient_ID

quote:

---

Originally posted by Lumbago

It seems to me like the count inside the case is obsolete:

Select dc.Patient_ID,dc.Department,count(dc.Amount) as "count",
  sum(case
    when dc.department in ('IMG','PAT','PHY') then (dc.amount * ic.Cost_Per_Unit) else 0
    end) as "Indirect Costs"
from DirectCosts dc
  left join IndirectCosts ic on dc.Department = ic.department
group by dc.Patient_ID,dc.Department
order by Patient_ID

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

---

quote:

---

Originally posted by Lumbago

The SUM around the case-statement should handle that...can you try it?

---

quote:

---

Originally posted by Lumbago

I'm feeling awfully slow here but it would be a lot better if you could post some sample data and your expected output. Please create the sample data with full create table/insert statements and your expected output in the exact format your'e looking for...

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

---

quote:

---

Originally posted by Lumbago

I'm sorry but I can't get the expected output you posted to make any sense at all. How do you compute the last two columns? 7.21 is not anywhere in the sample data you posted...

---

SELECT DISTINCT dc.Patient_ID, dc.Episode_ID, Direct_Costs, Indirect_Costs, TotalCost = Direct_Costs + Indirect_Costs
FROM directCosts dc
  INNER JOIN (
    SELECT 
      Episode_ID, 
      Direct_Costs = SUM(Amount), 
      Indirect_Costs = COALESCE(SUM(CASE WHEN b.Department IN ('IMG','PAT','PHY') THEN Cost_Per_unit ELSE 0 END), 0)
    FROM directCosts a
      LEFT OUTER JOIN IndirectCosts b
        ON a.Department = b.Department
    GROUP BY Episode_ID
  ) dt
  ON dt.Episode_ID = dc.Episode_ID
ORDER BY dc.Patient_ID

quote:

---

Originally posted by Lumbago

Ok, I think I got it...took me a good while to understand your logic though:

SELECT DISTINCT dc.Patient_ID, dc.Episode_ID, Direct_Costs, Indirect_Costs, TotalCost = Direct_Costs + Indirect_Costs
FROM directCosts dc
  INNER JOIN (
    SELECT 
      Episode_ID, 
      Direct_Costs = SUM(Amount), 
      Indirect_Costs = COALESCE(SUM(CASE WHEN b.Department IN ('IMG','PAT','PHY') THEN Cost_Per_unit ELSE 0 END), 0)
    FROM directCosts a
      LEFT OUTER JOIN IndirectCosts b
        ON a.Department = b.Department
    GROUP BY Episode_ID
  ) dt
  ON dt.Episode_ID = dc.Episode_ID
ORDER BY dc.Patient_ID

---

quote:

---

Have a good weekend and a pint for me

---