CODEWITHSUNDEEP
sqlsql-serverexcelt-sqlexcel-2010
JasonR
JasonR

Reputation: 409

SQL Server - Suggestions on how to Organize Query Output so that it can be Graphed Nicely

I am working with Medication information for the Hospital I work in. Calculating Days of Therapy (DOT) for Antibiotics. I am trying to develop a series of Queries that will give me the following informations

  1. DOT by Month, DOT by Year
  2. DOT by Unit by Month, by Year
  3. DOT by Drug by Unit by Month, by Year.

I have my star tables giving me this data in a nice way. The issue I'm having is Month to Month and Year to Year I don't always have the same Units and the same Drugs. As a result of this I am having a hard time finding a way of organizing it so that it can be easily graphed. Since some months or years are missing some pieces it doesn't graph nicely/easily. For example I'd like to have Line Graph(s) where each Line is a Year, my X-axis is Months/Units and my Y-axis is DOT.

I have DIMENSION tables with all my Units and All the Drugs I'm tracking. But I somehow have to have my STAR tables (outputs) include the Units and Drugs that are not returned by the Database Query.

Here are the tables I'm working with and the data:

 DIM_DRUG_NAME
 -drug_name_ID, drug_name_long, drug_name_short
 FACT_AMS_DOT_Baseline
 - location_ID, DOT, drug_name_short, dispense_date
 DIM_DATE
 -this table contains a number of ways to convert and match dates
 -I mainly use Year, Num_Month, Month_Short (Month short is a MMM format)
 DIM_LOCATION
 -location_ID, Location_Name, Location_Mnemonic

The field I'd like to have in my output are:

 Med_Name_Short
 Location
 Sum(DOT)
 MonthShort
 Year

I join my Baseline to the Dim_Date table where Baseline.Dispense_Date = Dim_Date.Date

I hope this info helps and makes sense. Any input is greatly appreciated. I have a feeling I need to incorporate a WHERE clause in my output query where I compare all data in the Baseline to all Units/Drugs and then if its not found default that output row to 0 - which would then ensure that each Year-block of data will always have the same number of returned values.

Thanks in advance.

As requested here is my Select Statement

 SELECT
         Baseline.Med_Name_Short ,
         Baseline.Location ,
         SUM(DOT) AS 'DOT' , 
         tdate.MonthShort ,
         tDate.Year

     INTO STAR_AMS_DOT_By_Drug_By_Unit_MTM

     FROM   
         STAR_AMS_DOT_BASELINE Baseline
         INNER JOIN DIM_Date tDate ON Baseline.Dispense_Date = tDate.Date

     GROUP BY
         Med_Name_Short ,
         tDate.Num_Month ,
         tdate.MonthShort ,
         tDate.Year ,
         Baseline.location

     ORDER BY
         Baseline.Med_Name_Short ,
         tDate.Year ,
         tDate.Num_Month ,
         Baseline.Location

Hopefully that helps, Thanks again.

Upvotes: 0

Views: 237

Answers (1)

User15
User15

Reputation: 304

Just change Inner Join with Full Outer Join as:

FROM   
         STAR_AMS_DOT_BASELINE Baseline
         FULL OUTER JOIN DIM_Date tDate ON Baseline.Dispense_Date = tDate.Date

Inner Join only returns records where both tables match. See What is the difference between "INNER JOIN" and "OUTER JOIN"? or http://www.w3schools.com/sql/sql_join_full.asp.

Upvotes: 1

Related Questions