onebite2
asked on
Sql query
Write a query that returns a list of the Regions and the number of employees in each region. The employee only gets added to the count if they had total sales greater than $50,000 last month. Sort the information by the number of employees per region who fit this criteria.
I have written the query already but i have entered the dates manually which needs to be automated and then whole would be fixed.
I have written the query already but i have entered the dates manually which needs to be automated and then whole would be fixed.
select r.regiod_id, r.region_name,count(*) as emlopyee_count_with_sal_gt_50k from
employee e, region_id r
where e.employee_id in
(
(select employee_id from sales where month(sale_date) >= 12/01/2009 and sale_date <= 12/31/2009= month
and sum(sales_amount) > 50000 group by employee_id)
)
and e.region_id =r.region_id
group by r.region_id
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
you know the function "getdate()" to get the current date?
along with datepart, and dateadd functions, you can dynamically come to the first day of the month, and from there remove 1 more month ...
along with datepart, and dateadd functions, you can dynamically come to the first day of the month, and from there remove 1 more month ...
try
select r.regiod_id, r.region_name,count(*) as emlopyee_count_with_sal_gt_50k
from employee e
INNER JOIN region_id r ON e.region_id =r.region_id
INNER JOIN ( select employee_id
from sales
where slae_date between '12/01/2009' and '12/31/2009'
group by employee_id
having sum(sales_amount) > 50000 ) s ON e.employee_id = s.employee_id
group by r.region_id, r.region_name
also date values paramatrized
declare @startDate datetime;
declare @endDate datetime;
select r.regiod_id, r.region_name,count(*) as emlopyee_count_with_sal_gt_50k
from employee e
INNER JOIN region_id r ON e.region_id =r.region_id
INNER JOIN ( select employee_id
from sales
where slae_date between @startDate and @endDate
group by employee_id
having sum(sales_amount) > 50000 ) s ON e.employee_id = s.employee_id
group by r.region_id, r.region_name
Not tested but should point to the right direction...
select r.regiod_id, r.region_name,count(*) as emlopyee_count_with_sal_gt
from
employee where employee_id in
(
select i.employee_id
from employee i
where month(i.sale_date) = 12
group by i.employee_id
having sum(i.sales_amount) > 50000
)
group by region_id, region_name
order by count(*)
select r.regiod_id, r.region_name,count(*) as emlopyee_count_with_sal_gt
from
employee where employee_id in
(
select i.employee_id
from employee i
where month(i.sale_date) = 12
group by i.employee_id
having sum(i.sales_amount) > 50000
)
group by region_id, region_name
order by count(*)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you can add "order by 3 desc" or "order by emlopyee_count_with_sal_gt
ASKER
Perfect answer to my question
ASKER