asked
Gender count for employees based on experience in incremental order
i have a table name 'users' with columns username ,userid,gender ,joiningdate i want report for male female gender count for based on joining date 0-6months,6-12months,1-2 years,2-3 years,4-5 years----------etc years should be incremental no limit for that result should be experience male female ------------ ----------- ----------- 0-6 1 2 6-12 5 3 1-2 2 3 2-3 4 4 3-4 8 2 ---- ---- ---- ---- ---- ----
This question looks like a homework. I should probably inquire what has been done so far to solve such a primitive puzzle, but today is Friday, so why not just type the whole solution :) The idea is to calculate the experience by calculating the total number of months since joining date and divide such number by 12 (using integer division). The only twist is that the first bucket (0 to 1 year) needs to be further split into two 6-months long buckets. This explains the case statement. Also, the bucket values end up being varchars, so it is cumbersome to properly order them. To help with sorting, the same total number of months since joining date divided by 12 is used a separate column. Here is the script (the results should be as expected, including correct sorting of the experience buckets): -- The only reason to use the case statement is due to the requirement to split -- the first bucket (0-1 year) into 2, namely 0-6 months and 6-12 months). -- Please note that integer division is used, so 15 / 12 = 1 and 25 / 12 = 2 ;with bucketized as ( select gender, datediff(month, joiningdate, getdate()) / 12 sort, case when datediff(month, joiningdate, getdate()) < 7 then '0-6' when datediff(month, joiningdate, getdate()) < 13 then '6-12' else cast(datediff(month, joiningdate, getdate()) / 12 as varchar) + '-' + cast((datediff(month, joiningdate, getdate()) / 12) + 1 as varchar) end experience from users ) select experience, sum(case when gender = 'M' then 1 else 0 end) male, sum(case when gender = 'M' then 0 else 1 end) female from bucketized group by sort, experience order by sort, experience; Hope this helps. Oleg
@Usman Butt Thank you. If history is any indication of future results then I am afraid I will not get a luxury of finding out whether my script works as expected. Not with this particular OP. Even when I wheeled out the pattern matching [solution using R script][1] two days ago, I still did not receive any response. Thus, it is probably OK that the discretization logic is somewhat questionable and may be improved should the buckets assignment need to be more precise. [1]:
https://ask.sqlservercentral.com/questions/146080/find-the-stored-procedures-which-has-table-variabl.html
@Oleg I would have inquired, especially when I see that the OP is not responding to quite a few previous answers :) I do have a concern which I am sure we both know when we do such kind of calculations, but I am not going to mention it. Let us wait and see what are the OP's observations. But +1 for posting an elegant answer as usual.
You will have to pre-define the buckets you want, so you'll end up putting a limit on the years simply by how long you want to write code, however what you want is a crosstab or pivot. See Jeff Moden's excellent articles on these: [Cross Tabs and Pivots, Part 1 – Converting Rows to Columns][1] [Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs][2] [1]:
http://www.sqlservercentral.com/articles/T-SQL/63681/ [2]:
http://www.sqlservercentral.com/articles/Crosstab/65048/
@Jon Crawford I believe that the data in question is way too simple to worry about any limits, the discretization buckets are already dynamic, done via static script and will accommodate all date ranges automatically. Sure, it looks like it would make sense for OP to go through the referenced articles by @Jeff Moden (all his articles are great learning resources) in order to learn and understand how pivots work, but with just one column (gender) to pivot, this part can be done "old school", with conditional aggregation like it is done in my script.
Yeah, I was thinking to sum() using the experience and gender, while you took the better approach of sum() on the gender. You're still creating a crosstab, by using SUM(CASE...END), but made it simpler for sure.
@Jon Crawford I am just old enough to still remember and like the "old school" pivot via conditional aggregation. This works in any version of SQL Server, starting from SQL Server 7, maybe even earlier, but 7 is the earliest with which I worked. It works in very old versions of Oracle as well, after CASE is substituted with DECODE. I posted the answer to your [question about multiple patterns matching][1], please let me know if it works. Thank you. [1]:
https://ask.sqlservercentral.com/questions/146178/how-to-use-like-when-multiple-patterns-need-matche.html
19 People are following this question.
