[jelanier]

What I was trying to accomplish was, to gather statistics about revenue generated by a new office. We are a group of Orthopedic Surgeons & our administrator lamented that he had reports that gave him an accurate picture of what our cost were from this new facility but the reporting system did not provide him with how much revenue had been generated.

Our system is a unix based system and the database is a collection of flat files that are synchronized with an MSQL Database.

I wanted to present by doctor revenue columns Office, In Patient, Out Patient, Emgergency Room, ie location codes.

Thank you for your help, I will try to absorb your suggestions, but at present I only have rights to inquire into the database, not add to it. I haven't tried to build temporary tables but I may be able to do that.

peterlaursen wrote on Sep 27 2007, 11:20 PM:

select case

when

(select location_id from patient_transactions where location_id) = 7

then

select (sum(account_tot)) as office from patient_transactions where location_id = 7

when

(select location_id from patient_transactions …. ) = 46

then

select (sum(account_tot)) as inpt from patient_transactions where location_id = 46

when

…

then

[else …]

end

I think it must be something like this above you are looking for. But I do not fully understand!

In particular I am not able to understand what is the idea with the GROUP BY!

And basically I doubt that the CASE solution is the solution to your problem.

Why not simply

select (sum(account_tot)) from patient_transactions group by location_id;

— to me it seems as if the only reason for the CASE is that you want a different alias for every sum depending on the location_id.

Why not store this alias in the database itself for very value of 'location_id'?

Using a 'parent table' and including this with a JOIN type query seems to me a more logical design! It seems that you are trying to solve IN CODE what should be solved in DATABASE DESIGN.

But I do not claim to understand everything!

[Author]

Posts