April 27, 2012 at 4:31 am
Ok sir. I'll be waiting for you
April 27, 2012 at 5:56 am
No CURSOR. No Loop. I was able to deliver to my mantra!
Couldn't do it in a single statement though.
-- Give everyone their first choice unless the program doesn't allow anyone of their gender
;WITH CTE AS (
SELECT applicantIdx, gender, iptidx, kursusidx, mySelection, myRanking, mQuota, fQuota
,ROW_NUMBER() OVER (
PARTITION BY applicantIdx
ORDER BY applicantIdx, mySelection, myRanking) AS rk
FROM (
SELECT applicantIdx, gender, ap.iptidx, ap.kursusidx, mySelection, myRanking, mQuota, fQuota
FROM applyProgram ap
INNER JOIN programQuota pq ON pq.iptidx = ap.iptidx and pq.kursusidx = ap.kursusidx) ap
WHERE (gender = 'm' and mQuota > 0) or (gender = 'f' and fQuota > 0)
)
INSERT INTO selectedApplicant
SELECT applicantIdx, iptidx, kursusidx
,CASE gender WHEN 'm' THEN mQuota ELSE fQuota END
FROM CTE
WHERE rk = 1
-- Remove the lowest ranked applicants above the course quota
;WITH CTE AS (
SELECT sa.applicantIdx, gender, ap.iptidx, ap.kursusidx, mySelection, myRanking, mQuota, fQuota
,ROW_NUMBER() OVER (PARTITION BY ap.kursusidx, ap.iptidx, gender
ORDER BY ap.kursusidx, ap.iptidx, gender, mySelection, myRanking) As rk
FROM selectedApplicant sa
INNER JOIN applyProgram ap ON ap.iptidx = sa.iptidx and ap.kursusidx = sa.kursusidx and
ap.applicantidx = sa.applicantidx
INNER JOIN programQuota pq ON ap.iptidx = pq.iptidx and ap.kursusidx = pq.kursusidx)
DELETE ap
FROM selectedApplicant ap
INNER JOIN CTE lr ON ap.iptidx = lr.iptidx and ap.kursusidx = lr.kursusidx and
ap.applicantidx = lr.applicantidx
WHERE (lr.gender = 'm' and rk > mQuota) or (lr.gender = 'f' and rk > fQuota)
SELECT * FROM selectedApplicant
DELETE FROM SelectedApplicant
Warning! I make no guarantees on this. It may be possible that somewhere along the line someone may get excluded, even though they may have applied first because there is no timestamp on their application, or possibly because they were given their first choice only and got bumped out by someone with a higher ranking, even though they might have won out on their second choice. SQL doesn't guarantee ordering remember, and with the sorts necessary to do assignments to programs, the original record ordering may be changed.
Give it a try though and see if it works out for you. I strongly recommend trying this with larger record sets where you manually map the assignments for a check. If it doesn't you're probably going to need to do it with a CURSOR and since that isn't my area of expertise (LOL!) I may have to leave that to you.
If you end up writing this as a CURSOR, I think you're going to need to make a choice between one of these scenarios:
1. Do you want to maximize the likelihood that an applicant gets his/her first choice (at the expense of not getting into a program at all).
2. Do you want to maximize the likelihood that an applicant gets a slot, even if it is not their first choice.
3. Do you want to maximize the likelihood that all class quotas are met.
I chose #1. You may be able to construct 1, 2 or 3 SQL statement solutions for #2 and #3 without a CURSOR also. I don't think you can do all 3 at once.
Anyway. Good luck with this. Maybe someone who's a bit more of an expert will come up with something exotic that beats my solution.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 27, 2012 at 8:11 am
Are we limited to using SQL 2008 for this solution? I think a sliding window will work for this... but that requires SQL 2012. (see my article[/url] for how this works!) Possible one of the new SQL 2012 analytic functions (see my other article[/url] for the LAG/LEAD/FIRST_VALUE/LAST_VALUE functions), again with a sliding window.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 27, 2012 at 7:26 pm
dwain.c (4/27/2012)
Couldn't do it in a single statement though.
Heh... you say that as if there should be something to be ashamed about. "Set-based" doesn't mean "all in one query". In fact, the "Divide'n'Conquer" set-based methods are frequently much better for performance and resource usage than larger single query code.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 28, 2012 at 12:22 am
Jeff Moden (4/27/2012)
dwain.c (4/27/2012)
Couldn't do it in a single statement though.Heh... you say that as if there should be something to be ashamed about. "Set-based" doesn't mean "all in one query". In fact, the "Divide'n'Conquer" set-based methods are frequently much better for performance and resource usage than larger single query code.
Not ashamed. Actually I thought mine was a pretty good attempt at a tough problem. I only said it because most of my solutions usually are (one statement).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 28, 2012 at 12:32 am
Kindest dwain.c,
Whatever people said about your solutions, I dont care. For me, you are the best. You have helped me. they do not help but criticize only
For me, you're my hero. If you come to Indonesia, I want to take your picture and be a source of inspiration in my life
April 28, 2012 at 12:43 am
idyana (4/28/2012)
Kindest dwain.c,Whatever people said about your solutions, I dont care. For me, you are the best. You have helped me. they do not help but criticize only
For me, you're my hero. If you come to Indonesia, I want to take your picture and be a source of inspiration in my life
Wow thanks! I'm not far away (Bangkok).
Because this problem was a bit more difficult that I expected it to be, it's been sticking in my mind. Can't seem to stop thinking about actually.
Recall that I said it may cause some people who didn't get their first choice to miss getting assigned to a program entirely? I have an idea that I might try on that case. I would like to know a little more about the business scenario though.
This appears to me to be something like assigning students to university courses. I think the mySelection column represents the student's choice. The myRanking column represents a score of some kind that ranks the candidate for that subject area. Is that correct?
And what is the kursusid vs. the other id in the programs table? Is that like faculty/course number?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 28, 2012 at 12:52 am
Recall that I said it may cause some people who didn't get their first choice to miss getting assigned to a program entirely? I have an idea that I might try on that case. I would like to know a little more about the business scenario though.
1. This appears to me to be something like assigning students to university courses. I think the mySelection column represents the student's choice. The myRanking column represents a score of some kind that ranks the candidate for that subject area. Is that correct?
2. And what is the kursusid vs. the other id in the programs table? Is that like faculty/course number?
1. Absolutely yes. But, one thing you have to remember. If 1st selection is not qualify, we have to move 2nd selection, 3rd selection and so on
2. kursusIdx is course number. This course number can be available at more than 1 iptsIdx. That's why, applyProgram(applicantIdx, iptsIdx, kursusIdx) is a unique. prograQuota(iptsIdx, kursusIdx) is a unique
April 28, 2012 at 9:37 am
idyana (4/28/2012)
Kindest dwain.c,Whatever people said about your solutions, I dont care. For me, you are the best. You have helped me. they do not help but criticize only
For me, you're my hero. If you come to Indonesia, I want to take your picture and be a source of inspiration in my life
Heh... no... I wasn't criticizing Dwain at all. In fact, I'm hoping he took my comments as encouragement especially in the area of Divide'n'Conquer.
Besides, Dwain also knows how to fish. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
April 28, 2012 at 9:36 pm
Jeff Moden (4/28/2012)
idyana (4/28/2012)
Kindest dwain.c,Whatever people said about your solutions, I dont care. For me, you are the best. You have helped me. they do not help but criticize only
For me, you're my hero. If you come to Indonesia, I want to take your picture and be a source of inspiration in my life
Heh... no... I wasn't criticizing Dwain at all. In fact, I'm hoping he took my comments as encouragement especially in the area of Divide'n'Conquer.
Besides, Dwain also knows how to fish. 🙂
Cool Mr Jeff Moden. You are also my inspiration.
By the way, me also need your help.
I re-write,
I've tables and data as following,
-1st case-
USE [myTest]
GO
/****** Object: Table [dbo].[selectedApplicant] Script Date: 04/26/2012 18:58:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[selectedApplicant](
[applicantIdx] [int] NOT NULL,
[iptIdx] [int] NOT NULL,
[kursusIdx] [int] NOT NULL,
[programQuotaIdx] [int] NOT NULL,
CONSTRAINT [PK_selectedApplicant] PRIMARY KEY CLUSTERED
(
[applicantIdx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[programQuota] Script Date: 04/26/2012 18:58:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[programQuota](
[idx] [int] NOT NULL,
[iptIdx] [int] NOT NULL,
[kursusIdx] [int] NOT NULL,
[mQuota] [int] NOT NULL,
[fQuota] [int] NOT NULL,
CONSTRAINT [PK_programQuota] PRIMARY KEY CLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [programQuota_UQ1] UNIQUE NONCLUSTERED
(
[iptIdx] ASC,
[kursusIdx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[programQuota] ([idx], [iptIdx], [kursusIdx], [mQuota], [fQuota]) VALUES (100, 22, 155, 2, 0)
INSERT [dbo].[programQuota] ([idx], [iptIdx], [kursusIdx], [mQuota], [fQuota]) VALUES (101, 23, 155, 0, 0)
INSERT [dbo].[programQuota] ([idx], [iptIdx], [kursusIdx], [mQuota], [fQuota]) VALUES (102, 34, 17, 2, 1)
INSERT [dbo].[programQuota] ([idx], [iptIdx], [kursusIdx], [mQuota], [fQuota]) VALUES (103, 22, 156, 1, 1)
/****** Object: Table [dbo].[applyProgram] Script Date: 04/26/2012 18:58:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[applyProgram](
[applicantIdx] [int] NOT NULL,
[gender] [char](1) NOT NULL,
[iptIdx] [int] NOT NULL,
[kursusIdx] [int] NOT NULL,
[mySelection] [int] NOT NULL,
[myRanking] [int] NOT NULL,
CONSTRAINT [applyProgram_UQ1] UNIQUE NONCLUSTERED
(
[applicantIdx] ASC,
[iptIdx] ASC,
[kursusIdx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[applyProgram] ([applicantIdx], [gender], [iptIdx], [kursusIdx], [mySelection], [myRanking]) VALUES (1925, N'm', 22, 155, 1, 1)
INSERT [dbo].[applyProgram] ([applicantIdx], [gender], [iptIdx], [kursusIdx], [mySelection], [myRanking]) VALUES (4474, N'f', 22, 155, 1, 1)
INSERT [dbo].[applyProgram] ([applicantIdx], [gender], [iptIdx], [kursusIdx], [mySelection], [myRanking]) VALUES (4474, N'f', 34, 17, 2, 1)
INSERT [dbo].[applyProgram] ([applicantIdx], [gender], [iptIdx], [kursusIdx], [mySelection], [myRanking]) VALUES (1926, N'f', 23, 155, 2, 1)
INSERT [dbo].[applyProgram] ([applicantIdx], [gender], [iptIdx], [kursusIdx], [mySelection], [myRanking]) VALUES (1993, N'm', 22, 155, 1, 2)
INSERT [dbo].[applyProgram] ([applicantIdx], [gender], [iptIdx], [kursusIdx], [mySelection], [myRanking]) VALUES (1493, N'm', 22, 155, 1, 4)
INSERT [dbo].[applyProgram] ([applicantIdx], [gender], [iptIdx], [kursusIdx], [mySelection], [myRanking]) VALUES (1925, N'm', 34, 17, 3, 5)
INSERT [dbo].[applyProgram] ([applicantIdx], [gender], [iptIdx], [kursusIdx], [mySelection], [myRanking]) VALUES (2900, N'm', 34, 17, 3, 6)
INSERT [dbo].[applyProgram] ([applicantIdx], [gender], [iptIdx], [kursusIdx], [mySelection], [myRanking]) VALUES (2980, N'm', 34, 17, 3, 7)
/****** Object: Check [CK_applyProgram1] Script Date: 04/26/2012 18:58:13 ******/
ALTER TABLE [dbo].[applyProgram] WITH CHECK ADD CONSTRAINT [CK_applyProgram1] CHECK (([gender]='f' OR [gender]='m'))
GO
ALTER TABLE [dbo].[applyProgram] CHECK CONSTRAINT [CK_applyProgram1]
GO
What I want to do?
1. Each data in applyProgram need to be processed based on the data in programQuota
2. Each data in applyProgram will be processed by priority. The priority based on applyProgram(mySelection, myRanking) --- order by mySelection, myRanking
3. The lowest mySelection, and myRanking is the strongest priority
4. If their condition is the same, the formula is first come first serve
5. Any data in applyProgram those meet the requirement based on the data in programQuota, it will be inserted into selectedApplicant
6. If any applicantIdx HAS GAINED A PROGRAM, others data in applyProgram do not need to be further processed. So, the quota is NOT TAKEN and WILL GIVE TO OTHERS
7. If 1st selection is not qualify or no Quota, we have to move 2nd selection, 3rd selection and so on
Based on above data, I've Mr Dwain T-SQL as following,
use myTest
-- Give everyone their first choice unless the program doesn't allow anyone of their gender
;WITH CTE AS (
SELECT programQuotaIdx,applicantIdx, gender, iptidx, kursusidx, mySelection, myRanking, mQuota, fQuota
,ROW_NUMBER() OVER (
PARTITION BY applicantIdx
ORDER BY applicantIdx, mySelection, myRanking) AS rk
FROM (
SELECT pq.idx as programQuotaIdx, applicantIdx, gender, ap.iptidx, ap.kursusidx, mySelection, myRanking, mQuota, fQuota
FROM applyProgram ap
INNER JOIN programQuota pq ON pq.iptidx = ap.iptidx and pq.kursusidx = ap.kursusidx) ap
WHERE (gender = 'm' and mQuota > 0) or (gender = 'f' and fQuota > 0)
)
INSERT INTO selectedApplicant
SELECT applicantIdx, iptidx, kursusidx
,programQuotaIdx/*CASE gender WHEN 'm' THEN mQuota ELSE fQuota END*/
FROM CTE
WHERE rk = 1
-- Remove the lowest ranked applicants above the course quota
;WITH CTE AS (
SELECT sa.applicantIdx, gender, ap.iptidx, ap.kursusidx, mySelection, myRanking, mQuota, fQuota
,ROW_NUMBER() OVER (PARTITION BY ap.kursusidx, ap.iptidx, gender
ORDER BY ap.kursusidx, ap.iptidx, gender, mySelection, myRanking) As rk
FROM selectedApplicant sa
INNER JOIN applyProgram ap ON ap.iptidx = sa.iptidx and ap.kursusidx = sa.kursusidx and
ap.applicantidx = sa.applicantidx
INNER JOIN programQuota pq ON ap.iptidx = pq.iptidx and ap.kursusidx = pq.kursusidx)
DELETE ap
FROM selectedApplicant ap
INNER JOIN CTE lr ON ap.iptidx = lr.iptidx and ap.kursusidx = lr.kursusidx and
ap.applicantidx = lr.applicantidx
WHERE (lr.gender = 'm' and rk > mQuota) or (lr.gender = 'f' and rk > fQuota)
SELECT * FROM selectedApplicant
DELETE FROM SelectedApplicant
Then, the result is CORRECT
I've tables and data as following,
-2nd case-
USE [myTest]
GO
/****** Object: Table [dbo].[selectedApplicant] Script Date: 04/29/2012 12:21:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[selectedApplicant](
[applicantIdx] [int] NOT NULL,
[iptIdx] [int] NOT NULL,
[kursusIdx] [int] NOT NULL,
[programQuotaIdx] [int] NOT NULL,
CONSTRAINT [PK_selectedApplicant] PRIMARY KEY CLUSTERED
(
[applicantIdx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[programQuota] Script Date: 04/29/2012 12:21:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[programQuota](
[idx] [int] NOT NULL,
[iptIdx] [int] NOT NULL,
[kursusIdx] [int] NOT NULL,
[mQuota] [int] NOT NULL,
[fQuota] [int] NOT NULL,
CONSTRAINT [PK_programQuota] PRIMARY KEY CLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [programQuota_UQ1] UNIQUE NONCLUSTERED
(
[iptIdx] ASC,
[kursusIdx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[programQuota] ([idx], [iptIdx], [kursusIdx], [mQuota], [fQuota]) VALUES (100, 22, 155, 2, 0)
INSERT [dbo].[programQuota] ([idx], [iptIdx], [kursusIdx], [mQuota], [fQuota]) VALUES (101, 23, 155, 0, 0)
INSERT [dbo].[programQuota] ([idx], [iptIdx], [kursusIdx], [mQuota], [fQuota]) VALUES (102, 34, 17, 2, 1)
INSERT [dbo].[programQuota] ([idx], [iptIdx], [kursusIdx], [mQuota], [fQuota]) VALUES (103, 22, 156, 1, 1)
/****** Object: Table [dbo].[applyProgram] Script Date: 04/29/2012 12:21:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[applyProgram](
[applicantIdx] [int] NOT NULL,
[gender] [char](1) NOT NULL,
[iptIdx] [int] NOT NULL,
[kursusIdx] [int] NOT NULL,
[mySelection] [int] NOT NULL,
[myRanking] [int] NOT NULL,
CONSTRAINT [applyProgram_UQ1] UNIQUE NONCLUSTERED
(
[applicantIdx] ASC,
[iptIdx] ASC,
[kursusIdx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[applyProgram] ([applicantIdx], [gender], [iptIdx], [kursusIdx], [mySelection], [myRanking]) VALUES (1925, N'm', 22, 155, 1, 5)
INSERT [dbo].[applyProgram] ([applicantIdx], [gender], [iptIdx], [kursusIdx], [mySelection], [myRanking]) VALUES (4474, N'f', 22, 155, 1, 1)
INSERT [dbo].[applyProgram] ([applicantIdx], [gender], [iptIdx], [kursusIdx], [mySelection], [myRanking]) VALUES (4474, N'f', 34, 17, 2, 1)
INSERT [dbo].[applyProgram] ([applicantIdx], [gender], [iptIdx], [kursusIdx], [mySelection], [myRanking]) VALUES (1926, N'f', 23, 155, 2, 1)
INSERT [dbo].[applyProgram] ([applicantIdx], [gender], [iptIdx], [kursusIdx], [mySelection], [myRanking]) VALUES (1993, N'm', 22, 155, 1, 2)
INSERT [dbo].[applyProgram] ([applicantIdx], [gender], [iptIdx], [kursusIdx], [mySelection], [myRanking]) VALUES (1493, N'm', 22, 155, 1, 4)
INSERT [dbo].[applyProgram] ([applicantIdx], [gender], [iptIdx], [kursusIdx], [mySelection], [myRanking]) VALUES (1925, N'm', 34, 17, 3, 5)
INSERT [dbo].[applyProgram] ([applicantIdx], [gender], [iptIdx], [kursusIdx], [mySelection], [myRanking]) VALUES (2900, N'm', 34, 17, 3, 6)
INSERT [dbo].[applyProgram] ([applicantIdx], [gender], [iptIdx], [kursusIdx], [mySelection], [myRanking]) VALUES (2980, N'm', 34, 17, 3, 7)
INSERT [dbo].[applyProgram] ([applicantIdx], [gender], [iptIdx], [kursusIdx], [mySelection], [myRanking]) VALUES (3890, N'm', 22, 155, 1, 2)
/****** Object: Check [CK_applyProgram1] Script Date: 04/29/2012 12:21:43 ******/
ALTER TABLE [dbo].[applyProgram] WITH CHECK ADD CONSTRAINT [CK_applyProgram1] CHECK (([gender]='f' OR [gender]='m'))
GO
ALTER TABLE [dbo].[applyProgram] CHECK CONSTRAINT [CK_applyProgram1]
GO
Based on above data, I've Mr Dwain T-SQL as following,
use myTest
-- Give everyone their first choice unless the program doesn't allow anyone of their gender
;WITH CTE AS (
SELECT programQuotaIdx,applicantIdx, gender, iptidx, kursusidx, mySelection, myRanking, mQuota, fQuota
,ROW_NUMBER() OVER (
PARTITION BY applicantIdx
ORDER BY applicantIdx, mySelection, myRanking) AS rk
FROM (
SELECT pq.idx as programQuotaIdx, applicantIdx, gender, ap.iptidx, ap.kursusidx, mySelection, myRanking, mQuota, fQuota
FROM applyProgram ap
INNER JOIN programQuota pq ON pq.iptidx = ap.iptidx and pq.kursusidx = ap.kursusidx) ap
WHERE (gender = 'm' and mQuota > 0) or (gender = 'f' and fQuota > 0)
)
INSERT INTO selectedApplicant
SELECT applicantIdx, iptidx, kursusidx
,programQuotaIdx/*CASE gender WHEN 'm' THEN mQuota ELSE fQuota END*/
FROM CTE
WHERE rk = 1
-- Remove the lowest ranked applicants above the course quota
;WITH CTE AS (
SELECT sa.applicantIdx, gender, ap.iptidx, ap.kursusidx, mySelection, myRanking, mQuota, fQuota
,ROW_NUMBER() OVER (PARTITION BY ap.kursusidx, ap.iptidx, gender
ORDER BY ap.kursusidx, ap.iptidx, gender, mySelection, myRanking) As rk
FROM selectedApplicant sa
INNER JOIN applyProgram ap ON ap.iptidx = sa.iptidx and ap.kursusidx = sa.kursusidx and
ap.applicantidx = sa.applicantidx
INNER JOIN programQuota pq ON ap.iptidx = pq.iptidx and ap.kursusidx = pq.kursusidx)
DELETE ap
FROM selectedApplicant ap
INNER JOIN CTE lr ON ap.iptidx = lr.iptidx and ap.kursusidx = lr.kursusidx and
ap.applicantidx = lr.applicantidx
WHERE (lr.gender = 'm' and rk > mQuota) or (lr.gender = 'f' and rk > fQuota)
SELECT * FROM selectedApplicant
DELETE FROM SelectedApplicant
Then, the result is NOT CORRECT
The result suppose to be as following,
1993 | 22 | 155 | 100
3890 | 22 | 155 | 100 -- till here, mQuota is enough, 1493, and 1925 did not get the place
4474 | 34 | 17 | 102 -- till here, fQuota is enough
1925 | 34 | 17 | 102
2900 | 34 | 17 | 102 -- till here, mQuota is enough
But it return,
1993 | 22| 155 | 100
2900 | 34| 17 | 102
2980 | 34| 17 | 102 -- based on mySelection, myRanking it's suppose to give place to 1925 | m | 34 | 17 | 3 | 5
3890 | 22| 155 | 100
4474 | 34| 17 | 102
I've some concern,
1. If 1st selection is not qualify or no Quota, it did not move to 2nd selection, 3rd selection and so on to do the checking
I need help to make it Mr Dwain T-SQL, cover that
April 29, 2012 at 6:44 pm
Sorry for not getting back sooner. It's not because I was struggling with the new approach. The weekend had other things in store for me though. 🙂
So let's add the following case. We have a new applicant that we'll insert as follows:
INSERT [dbo].[applyProgram] ([applicantIdx], [gender], [iptIdx], [kursusIdx], [mySelection], [myRanking]) VALUES (3333, N'm', 22, 155, 1, 7)
INSERT [dbo].[applyProgram] ([applicantIdx], [gender], [iptIdx], [kursusIdx], [mySelection], [myRanking]) VALUES (3333, N'm', 34, 17, 2, 8)
The original code I posted generates this result. Note that 3333 has not been assigned to a program.
applicantIdxiptIdxkursusIdx programQuotaIdx
1925 22155 2
1993 22155 2
2900 3417 2
2980 3417 2
4474 3417 1
Applicant 3333 has selected as a first choice a program that is full and his selection/ranking is lower than the other applied candidates. Since the code does one pass only on his first choice (22/155) it never tries to assign his second choice (34/17) which has an open slot.
So we'll try to remedy this by introducing a loop on selection, to ensure that every selection of a candidate is considered.
DECLARE @choices INT, @selection INT
SELECT @choices = (SELECT MAX(mc)
FROM (SELECT COUNT(applicantidx) FROM ApplyProgram GROUP BY applicantidx) x(mc))
,@selection = 1
WHILE @selection <= @choices and
EXISTS (SELECT applicantidx FROM ApplyProgram EXCEPT SELECT applicantidx FROM selectedApplicant)
BEGIN
-- Give everyone their nth selection (rk) unless
-- 1) the program doesn't allow anyone of their gender
-- 2) they've already been promoted to a program
;WITH CTE AS (
SELECT applicantIdx, gender, iptidx, kursusidx, mySelection, myRanking, mQuota, fQuota
,ROW_NUMBER() OVER (
PARTITION BY applicantIdx
ORDER BY applicantIdx, mySelection, myRanking) AS rk
FROM (
SELECT applicantIdx, gender, ap.iptidx, ap.kursusidx, mySelection, myRanking, mQuota, fQuota
FROM applyProgram ap
INNER JOIN programQuota pq ON pq.iptidx = ap.iptidx and pq.kursusidx = ap.kursusidx) ap
WHERE (gender = 'm' and mQuota > 0) or (gender = 'f' and fQuota > 0)
)
INSERT INTO selectedApplicant
SELECT x.applicantIdx, x.iptidx, x.kursusidx
,CASE gender WHEN 'm' THEN mQuota ELSE fQuota END
FROM CTE x
LEFT OUTER JOIN selectedApplicant sa ON sa.applicantidx = x.applicantidx
WHERE rk = @selection AND sa.applicantidx IS NULL
-- Remove the lowest ranked applicants above the course quota
;WITH CTE AS (
SELECT sa.applicantIdx, gender, ap.iptidx, ap.kursusidx, mySelection, myRanking, mQuota, fQuota
,ROW_NUMBER() OVER (PARTITION BY ap.kursusidx, ap.iptidx, gender
ORDER BY ap.kursusidx, ap.iptidx, gender, mySelection, myRanking) As rk
FROM selectedApplicant sa
INNER JOIN applyProgram ap ON ap.iptidx = sa.iptidx and ap.kursusidx = sa.kursusidx and
ap.applicantidx = sa.applicantidx
INNER JOIN programQuota pq ON ap.iptidx = pq.iptidx and ap.kursusidx = pq.kursusidx)
DELETE ap
FROM selectedApplicant ap
INNER JOIN CTE lr ON ap.iptidx = lr.iptidx and ap.kursusidx = lr.kursusidx and
ap.applicantidx = lr.applicantidx
WHERE (lr.gender = 'm' and rk > mQuota) or (lr.gender = 'f' and rk > fQuota)
SET @selection = @selection + 1
END
SELECT * FROM selectedApplicant
DELETE FROM SelectedApplicant
Now the results are:
applicantIdxiptIdxkursusIdx programQuotaIdx
1925 221552
1993 221552
2900 34172
3333 34172
4474 34171
From which we see that candidate 3333 has now gotten his second choice and he has bumped 2980 because he had a higher selection/ranking.
I still have come concerns regarding cases where there's a big difference between mySelection and myRanking (i.e., a candidate makes a first choice that is widely disparate from his/her ranking), but I'm afraid that this is the best I can do for the time being. Like I said, test, test, test to make sure that this code fulfills your requirements.
Hope this helps.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 29, 2012 at 8:21 pm
Dear Mr Dwain,
Your new T-SQL, look's ok. I will let you know, if something weird happen
tq so much
May 1, 2012 at 11:07 am
Working on a different solution, but I have some questions.
1. How many programs can an applicant apply for? (All the examples show a maximum of 2 - is 2 the limit?)
2. Is there a particular order that the programs are to be evaluated in? They appear to be happening in the order that they are entered, which happens to coincide with the idx #, but there is no ORDER BY clauses that guarantee this order in any of the code I've seen. And without an ORDER BY clause, you are getting lucky with getting the results in the desired ordering. (Would it be wrong if the programs were evaluated in idx DESC order?)
3. Or does the considering of applicant by mySelection | myRanking make this irrelevant?
4. What constitutes an applicant being "first" in the "If their condition is the same, the formula is first come first serve"? I see ordering by applicantIdx, but it that "first"? It seems to me that "first" would imply a date to order by, or perhaps an identity column.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply