0

I am trying to run a query to pull the total amount of qualifying events grouped by event type and month. For example I need to know that there were 25 "LOSS_OF_COVERAGE_NON_PAYMENT" qualifying events in January, and 10 "LEAVE_OF_ABSENCE" qualifying events in January. I have tried various SQL select queries. I am not sure why the query below is returning an error that states

Invalid column name 'EventMonth'

My query:

SELECT 
    a.GroupID, c.employername, 
    MONTH(b.QualifyingEventDate) AS EventMonth, 
    b.QualifyingEventType
FROM 
    MST_MEMBER_DEMOGRAPHICS AS a 
JOIN
    DET_ENROLLMENT_DETAILS AS b ON a.BFUNIQUEID = b.BFUNIQUEID 
JOIN
    MST_EMPLOYER_GROUP_MASTER AS c ON a.groupid = c.groupid
WHERE 
    a.groupid = 'TESTClient' 
    AND QualifyingEventType <> 'NULL' 
    AND QualifyingEventDate BETWEEN '01/01/2019' AND '08/13/2019' 
GROUP BY 
    b.QualifyingEventType, EventMonth
ORDER BY 
    EventMonth, a.groupid, b.QualifyingEventType
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Chuck0185
  • 531
  • 3
  • 15
  • 36
  • Change `GROUP BY b.QualifyingEventType, EventMonth` to `GROUP BY b.QualifyingEventType, Month(b.QualifyingEventDate)` to resolve your error. Though, this probably still isn't the results you want since you aren't doing any aggregations but you state you want the "total amount" which usually implies using `SUM()` – S3S Aug 14 '19 at 21:01
  • 2
    You can't group by the alias, you would need to group by the derived value. Also, you can't group by only some of the non-aggregated columns. You have a few other issues here though. Your date literals are not ANSI compliant, they should be YYYYMMDD. Also, table aliases should have some context. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3 And be careful with 'NULL'. That is a string literal. You should instead put an actual NULL in the table, not a string that says 'NULL'. – Sean Lange Aug 14 '19 at 21:03
  • Thank you both for your responses. As you can probably tell I'ma bit lost here. Unfortunately I don't have control over the data in the DB, I just need to query it. So if I am looking to show the amount of each EventType per month how would I accomplish this? How would I incorporate SUM() into my query? I also need to create another query that does the same thing but also groups by EmployerName (removing the WHERE a.groupid = 'TESTClient' ). What would that SQL statement look like? Thank you again for the help! – Chuck0185 Aug 14 '19 at 21:20

0 Answers0