February 21, 2005 at 11:18 am
Hello, I've got a question about how to form a SQL statement. I have a table which contains the following fields: year, quarter, region_code, industry_code, empl, nd.
I need to find for each year, quarter, and region_code, the industry_code with the smallest empl value (an int field) where nd (a bit field) = 0.
I was able to come up with the below statement, but it doesn't work quite right, since, for any given year, quarter, and region_code, multiple industry_codes can have the same empl value.
SELECT r.year, r.qtr, r.industry_code
FROM rpt_calc_data r INNER JOIN
(select year, qtr, min(empl) as empl
from rpt_calc_data r
where r.region_code = @region_code and r.nd = 0
group by year, qtr) AS Z ON r.year = z.year and r.qtr = z.qtr and r.empl = z.empl
WHERE r.region_code = @region_code AND r.nd = 0
Any help will be much appreciated! Adrianne
February 21, 2005 at 11:23 am
You seem to be close.. can you send the ddl with some sample data. Also what results you are expecting also with what you are getting. It'll be very to help you without that info.
February 21, 2005 at 11:24 am
What are your business rules for deciding a tie-break if 2 industry codes emerge with the same min(empl) ? The solution depends on those rules.
February 21, 2005 at 11:38 am
If there are 2 industry codes with the same employment in a region, then the smallest industry code should be selected. The ddl for this table is below:
CREATE TABLE rpt_calc_data ([year] int, qtr int, region_code int, industry_code int, empl float, nd bit)
Some sample insert statements:
INSERT INTO rpt_calc_data (region_code, year, qtr, nd, industry_code, empl) VALUES (775,1991,2,1,454,739.6667)
INSERT INTO rpt_calc_data (region_code, year, qtr, nd, industry_code, empl) VALUES (775,1991,2,0,451,28)
INSERT INTO rpt_calc_data (region_code, year, qtr, nd, industry_code, empl) VALUES (775,1991,2,0,453,136.33)
INSERT INTO rpt_calc_data (region_code, year, qtr, nd, industry_code, empl) VALUES (775,1998,3,1,454,115.6667)
INSERT INTO rpt_calc_data (region_code, year, qtr, nd, industry_code, empl) VALUES (775,1998,3,0,453,115.6667)
INSERT INTO rpt_calc_data (region_code, year, qtr, nd, industry_code, empl) VALUES (775,1998,3,0,451,158.6667)
Thanks, Adrianne
February 21, 2005 at 1:35 pm
I took out your variable for my testing so you will need to add it back in... Anyway, I think this does what you need - eventhough it is extremely ugly. I would keep looking for a better way to do it. I don't think this will be very effecient.
SELECT r.year, r.qtr, r.industry_code
FROM rpt_calc_data r INNER JOIN
(select year, qtr, min(empl) as empl
from rpt_calc_data r
where r.nd = 0
group by year, qtr) AS Z ON r.year = z.year and r.qtr = z.qtr and r.empl = z.empl
WHERE r.nd = 0
AND NOT EXISTS
(SELECT * FROM (
select r.year, r.qtr, v1.empl, min(industry_code) industry_code
FROM
(
select year, qtr, min(empl) as empl, count(*) as count
from rpt_calc_data r
where r.nd = 0
group by year, qtr
having count(*)>1
) v1
INNER JOIN rpt_calc_data r
ON r.year = v1.year and r.qtr = v1.qtr and r.empl = v1.empl
WHERE r.nd = 0
GROUP BY r.year, r.qtr, v1.empl
) vt WHERE vt.empl=r.empl)
UNION ALL
select year, qtr, industry_code FROM
(
select r.year, r.qtr, v1.empl, min(industry_code) industry_code
FROM
(
select year, qtr, min(empl) as empl, count(*) as count
from rpt_calc_data r
where r.nd = 0
group by year, qtr
having count(*)>1
) v1
INNER JOIN rpt_calc_data r
ON r.year = v1.year and r.qtr = v1.qtr and r.empl = v1.empl
WHERE r.nd = 0
GROUP BY r.year, r.qtr, v1.empl
) vt
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply