October 4, 2017 at 11:43 am
This code:SELECT '1) Total non-employees terminated' AS DESCRIPTION,
COUNT(*) AS AUGUST
FROM v_xyz_table
WHERE EMPLOYEE_STATUS_DESCR = 'INACTIVE'
AND update_by = 'E_L_T~dbo'
AND update_on >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 2, 0)
AND update_on < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
Produces the following results:DESCRIPTION AUGUST
---------------------------------- ---------------
1) Total non-employees terminated 496
I would like to put a column called SEPTEMBER with September's count just to the right of the AUGUST column. To get September's count, I would use another query somehow and change the 2 to a 1 in the query. But how do I add the column for it? Do I need to use a JOIN or a PIVOT or something? Then in the future I'd like to have Oct, Nov, Dec, etc.
Thanks a lot!
October 4, 2017 at 11:57 am
Try this out:SELECT '1) Total non-employees terminated' AS [DESCRIPTION],
SUM(CASE MONTH(update_on) WHEN 8 THEN 1 ELSE 0 END) AS AUGUST,
SUM(CASE MONTH(update_on) WHEN 9 THEN 1 ELSE 0 END) AS SEPTEMBER
FROM v_xyz_table
WHERE EMPLOYEE_STATUS_DESCR = 'INACTIVE'
AND update_by = 'E_L_T~dbo'
AND update_on >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 2, 0)
AND update_on < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0);
You may start having trouble when you have a time period that spans a year, though.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 4, 2017 at 11:59 am
Check out the following articles:
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
These give very complete descriptions of what you are trying to do. After you've read them, if you are still having problems, post what you've already tried and we can help you troubleshoot it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 4, 2017 at 12:00 pm
Please note that your existing query was actually counting SEPTEMBER as well as you went back 2 months from the 1st of October, which is August 1st, and then spanned until before October 1st, and you labelled that data as all belonging to AUGUST. With my updated query, you'll see it separated into the respective months.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 4, 2017 at 12:08 pm
Your response times here are phenomenal! I will try the query and read the articles, and then report back with what I find.
Thanks to both of you!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply