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.
| 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 |
|
|
|
|
|
|
|
|
Having Clause?