Having Clause? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Having Clause?

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-01-30 : 10:26:03

I have a table that is made up of the sum of medical, mental health and pharmacy claims. I would like to query that to find instances when the sum of the three claims types are greater than a pre-determined threshold.

For example:
Patient 1 Medical = 10,000 (could be 10 records at 1,000 each)
Patient 1 Mental Health = 5,000
Patient 1 Pharmacy = 15,000
Patient 2 Medical = 1,000
Patient 2 Mental Health = 0
Patient 2 Pharmacy = 500

Threshold is 25,000

If I queried the above sample table I would get one record:
Patient 1 30,000 - because 10,000+5,000+15,000 = 30,000 and is greater than the threshold.

I am not sure that a having clause would work though. Can anyone please help me?

jleitao
Posting Yak Master

100 Posts

Posted - 2015-01-30 : 12:08:22

SELECT
patient,
SUM(value) as value
FROM TABLE1
GROUP BY patient
HAVING SUM(value) > 25,000

------------------------
PS - Sorry my bad english

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-02-05 : 08:19:28

Thank you!

Sorry for the delay in answering you. The weather here (Boston) is terrible and I have missed several days of work!

That will return patient one for me at 30,000? Awesome, thankyou so much. What if I add other items to the select clause, nothing that would need to be added to the group by clause? They are just place holders like '' as PatientId??

Thanks again, this is a huge help!!

quote:
Originally posted by jleitao

SELECT
patient,
SUM(value) as value
FROM TABLE1
GROUP BY patient
HAVING SUM(value) > 25,000

------------------------
PS - Sorry my bad english

jleitao
Posting Yak Master

100 Posts

Posted - 2015-02-05 : 09:33:41

The weather here is a little bit better, we complain us for being 8 degrees celsius, its to cold for me :P

Not sure if i understand your question :(

however, if is not a column from a table you can add directly in the SELECT everything you want. it will be equal in all rows.

for example:
SELECT
'xpto' as column,
patient,
SUM(value) as value
FROM TABLE1
GROUP BY patient
HAVING SUM(value) > 25,000

it will return something like this:

xpto Patient 1 30,000
xpto Patient 7 50,000
xpto Patient 9 26,000

is that you need?

------------------------
PS - Sorry my bad english

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-02-05 : 09:51:47

Yes, that is exactly what I am looking for, thanks very much.

do you have any tricks to export data from SQL to excel easily? I built a reporting system for my company, the front end is in excel so it would be easy for the users to be able to export directly to excel.

Thanks again!

John

jleitao
Posting Yak Master

100 Posts

Posted - 2015-02-05 : 13:18:13

that depends what you really need.

Will be the user that will get the data or will be the "SQL expert" (you :p) that will send him the information?

you have a few possibilities:
1 - use the integration service (SSIS) to send the information to excel
2 - use SQL command (with OPENROWSET ) to send the information to excel
3 - Connect the excel to the SQL Server and the user just have to click on refresh data to get the latest information in the database

See what is the better approach to your case

------------------------
PS - Sorry my bad english

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources