0

I'm sure I've done this type of operation a 1000 times before but for some reason this is not working for me. I'm doing a report to determine if a patient receive medication on a day. So regardless if they get 1 does or 5 doses in a day the value should be 1. Staff also do corrections on the system, that come in as negative values. So I need to sum all of the dose value for each day, if it is a + value then its 1, otherwise its a 0.

All i want to accomplish at this point is to have 1 row for each date as either 1 or 0.

Here is my SQL Query to sum the values:

SELECT DIM_DRUG_NAME_SHORT.Drug_Name_Short AS 'Med_Name_Short' , SUM(Baseline.Doses) as 'DOT' , Day(Baseline.Dispense_Date) as 'd_Date' FROM FACT_AMS_Baseline_Report Baseline INNER JOIN DIM_DRUG_NAME_SHORT ON Baseline.Med_Name_ID = DIM_DRUG_NAME_SHORT.Drug_Name_Long INNER JOIN DIM_Date tDate ON Baseline.Dispense_Date = tDate.Date WHERE Baseline.Encounter = '00000001/01' GROUP BY DIM_DRUG_NAME_SHORT.Drug_Name_Short , Baseline.Dispense_Date , Doses Order By Drug_Name_Short

For time being I'm just pulling one encounter out of the data set to test with. This is the output i'm getting. I also included the Day in the select just to show that the same day is coming through twice and they are not getting Summed.

Here is a sample of the output I get:

Med_Name_Short DOT day of month CEFTRIAXONE 1 15 CEFTRIAXONE 1 16 CEFTRIAXONE 4 16 CEFTRIAXONE 1 17 CEFTRIAXONE 1 18 CEFTRIAXONE 1 20 CEFTRIAXONE -3 21 CEFTRIAXONE 1 21 CEFTRIAXONE -1 23 PROPRANOLOL -1 24 PROPRANOLOL 3 24 PROPRANOLOL 1 25 PROPRANOLOL 2 26 PROPRANOLOL 2 27

What I was hoping to see in this was that Day 16 would be a 5, day 21 would be -2 and day 24 would be -2.

Any assistance would be greatly appreciated. Thanks

Improve this question

3 Answers 3

Reset to default
2

Remove Doses from your Group By list. You are using an aggregate function on it (SUM) which is correct, so it should not be in the GROUP BY.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

I don't think you should be grouping by doses. Without seeing your data, I can only guess that, for example, there are two doses of quantity 2 on the 16th.

So try:

SELECT DIM_DRUG_NAME_SHORT.Drug_Name_Short AS 'Med_Name_Short' , SUM(Baseline.Doses) as 'DOT' , Day(Baseline.Dispense_Date) as 'd_Date' FROM FACT_AMS_Baseline_Report Baseline INNER JOIN DIM_DRUG_NAME_SHORT ON Baseline.Med_Name_ID = DIM_DRUG_NAME_SHORT.Drug_Name_Long INNER JOIN DIM_Date tDate ON Baseline.Dispense_Date = tDate.Date WHERE Baseline.Encounter = '00000001/01' GROUP BY DIM_DRUG_NAME_SHORT.Drug_Name_Short , Baseline.Dispense_Date Order By Drug_Name_Short
Improve this answer

3 Comments

should the grouping be on day(dispense_date)?
Thank you so much Jeff Hornby. What a silly mistake on my part - analysis paralysis on my part i think. Thanks again!
@JasonR So the part about wanting to have 1 row for each date as either 1 or 0. wasn't important? Then why did you include it in the question? Just curious.
0

Since you're aggregating on doses you should remove it from the group by, and to get either 1 or 0 for doses use a case expression:

SELECT DIM_DRUG_NAME_SHORT.Drug_Name_Short AS 'Med_Name_Short' , CASE WHEN SUM(Baseline.Doses) >= 1 THEN 1 ELSE 0 END AS 'DOT' , Day(Baseline.Dispense_Date) as 'd_Date' FROM FACT_AMS_Baseline_Report Baseline INNER JOIN DIM_DRUG_NAME_SHORT ON Baseline.Med_Name_ID = DIM_DRUG_NAME_SHORT.Drug_Name_Long INNER JOIN DIM_Date tDate ON Baseline.Dispense_Date = tDate.Date WHERE Baseline.Encounter = '00000001/01' GROUP BY DIM_DRUG_NAME_SHORT.Drug_Name_Short , Baseline.Dispense_Date Order By Drug_Name_Short

If the dispense_date is a datetime value you should probably use Day(Baseline.Dispense_Date) in the group by or remove the time part. If you group by day and your data spans over more than one month you should either limit the range or include year and month as well so that data from different months/years don't get summed up.

With your sample data you should get a result like:

Med_Name_Short DOT day of month CEFTRIAXONE 1 15 CEFTRIAXONE 1 16 CEFTRIAXONE 1 17 CEFTRIAXONE 1 18 CEFTRIAXONE 1 20 CEFTRIAXONE 0 21 CEFTRIAXONE 0 23 PROPRANOLOL 1 24 PROPRANOLOL 1 25 PROPRANOLOL 1 26 PROPRANOLOL 1 27
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.