0

I currently have tried multiple ways (Execute SQL script, Table input, Database joins) to accomplish what I am trying to do. I need the sum and count respectively that my sql scripts below create to be put into a brand new field, or have these value be set as constants. I am trying to use each result in a calculator step later on in my transformation. Currently I have been trying to define fields using add constants and then played with the settings of the Execute SQL script (turning on or off variable substitution, putting the field name in the "Field to Contain Read stats"). But nothing has worked. If anyone knows of a good way to accomplish this it would greatly help! 

Select sum(days_in_hospital) days_in_hospital_sum from hospital_days
where unique_stay_id in
(select unique_stay_id from hospital_days group by unique_stay_id having COUNT(unique_stay_id)=1);



SELECT COUNT(DISTINCT member_id) as "Total_Employees" from Hospital_days
Tanner Taylor
  • 11
  • 2

1 Answers1

0

There are two handsome Group by step in the Statistic folder. I usually use the Memory Group by.

For your second question: Import data with an Input table with SELECT * FROM Hospital_day and direct the stream into a Memory Group By with no Group field, and one Aggregate named Total-Employees with an type of Number of Distinct Values, on the subject member_id.

enter image description here

For the first question: you ask to filter the unique_stay which appears once in hospital_days, and get the sum of days for those.

SELECT * FROM Hospital-day which you (Memory) Group by unique stay to get the Number_of_unique_stay. Then Filter out the row you want, that is the rows with a Filter Number_of_unique_stay=1. Then sum the days_in_hospital with a second (Memory) Group by.

At that point, you discover that this column was removed by the first Group by. Bogger! Open again this step! And try to add this column somewhere. You can add it on the Group field or to the Aggregates. But adding it to the group field will damage the count based on the unique_stay only. So you have to add day_in_hospital to the aggregates. And the aggregation is obviously a sum, since the sum of a sum is the sum (answer would be different for an average).

enter image description here

AlainD
  • 6,187
  • 3
  • 17
  • 31
  • This was so close to working for me but I can't seem to get the days step working. Using memory group by for the total employees works fabulously. I set it up exactly as you did. The problem is, there are sometimes 20+ rows for the same unique_Stay_Id field (multiple claims for the same person on that same day). So if that person stayed at the hospital for 4 days, the first sum will give the days column a value of 80, this is making my days_in_hospital at the end 231000, instead of the 21,000 that is expected from my original script :( Any ideas around that? – Tanner Taylor Jul 04 '17 at 14:45
  • Then there is something I do not understand in what you want to achieve. Maybe the `days_in_hospital` is already a sum. Then you should add the "4"s instead of adding the "80"s. In that case replace the aggregator `Sum` by the `First value` or `First non null value`. – AlainD Jul 05 '17 at 13:43