Query brainteaser

[arichins]

Let's say you have an employee table that contains their hire date. Would it be possible to, in one query, get a count of all the employees that have been working 12 months or less, between 12 and 36 months, between 36 and 60 months etc etc.....

I could use this query to count all the employees who were hired less than 12 months ago, but how could I join other derived queries

select count(EmpID) as LT_One
from payroll_employees
where hired > = dateadd(mm, -12, getdate())

There is a field in the table that happens to be the same for every employee because it's not used, so I used that as the basis to join the drived tables, but it got me to wondering if there was a more elegant way. I couldn't think of any.

 

[ajetrumpet]

you might want to try a CT query. i think you can get this done with that wizard if i'm not mistaken.,

 

[Banana]

Hmm, getdate() is a SQL Server function, intelligible to JET/ACE... is this being done on SQL Server or do you intend to do this on Access as a passthrough query?

One solution is to do something like this:

(SQL Server dialect pseudo-code)

SELECT id,
   CASE WHEN hired-1 year THEN '0-12 months' 
        WHEN hired BETWEEN 1 year AND 3 year THEN '12-36 months'
        WHEN hired BETWEEN 3 year AND 5 year THEN '36-60 months'
        ELSE '60+ months'
   END CASE MonthGroup
FROM payroll_employees

JET SQL dialect pseudo-code

SELECT id,
   Switch(
     hired<1 year, "0-12 months",
     hired<3 year AND hired>1 year, "12-36 months",
     hired<5 year AND hired>3 year, "36-60 months",
     hired>5 year, "60+ months"
   ) MonthGroups
FROM payroll_employee;

You can then use either as a subquery to the count query using GROUP BY MonthGroup.

(Note: both are untested, and may need consulting with documentation to verify the syntax)
HTH.

 

[HiTechCoach]

I handle it like this:

Cookbook example

 

[Banana]

Boyd:

Partition function is awesome.

 

[HiTechCoach]

Boyd:

Partition function is awesome.

Yes it is. Great for age ranges!