Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 need help to fix my SQL

Author  Topic 

wkm1925
Posting Yak Master

207 Posts
Posted - 2012-04-26 : 10:59:31
I've tables and data as following,
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

My query as following,
select	applicantIdx, iptIdx, kursusIdx, tQuotaIdx
from
(
	select	ap.applicantIdx, ap.iptIdx, ap.kursusIdx, ap.gender,
		tQuotaIdx = tq.idx, tq.mQuota, tq.fQuota,
		rank	= row_number() over (partition by ap.iptIdx, ap.kursusIdx, ap.gender order by ap.mySelection, ap.myRanking, ap.applicantIdx)
	from	applyProgram ap
		inner join programQuota tq	on	ap.iptIdx	= tq.iptIdx
					and	ap.kursusIdx	= tq.kursusIdx
	where	(
			(ap.gender	= 'm'	and	tq.mQuota	> 0)
		or	(ap.gender	= 'f'	and	tq.fQuota	> 0)
		)
) s
where	(gender	= 'm'	and	mQuota >= rank)
or	(gender	= 'f'	and	fQuota >= rank)


It return,

1925	22	155	100
1993	22	155	100
4474	34	17	102

1925	34	17	102    /*this row is not accurate. applicantIdx HAS GAINED A PROGRAM before ~ refer 1925| 22| 155| 100*/

2900	34	17	102


It's suppose return,

1925	22	155	100
1993	22	155	100
4474	34	17	102
2900	34	17	102

2980    34      17      102  /*it take the quota for 1925| 34| 17| 102. it's because 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*/




The result will be inserted into selectedApplicant

Hopefully any expert can help me

wkm1925
Posting Yak Master

207 Posts
Posted - 2012-04-26 : 15:09:00
Please help me
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-04-26 : 15:55:45
This seems to me to be one of those cases that does not lend itself easily to a set-based query, at least not one that I can think of.

The nature of the problem requires that it be some sort of recursive query. There are some limitations to recursive queries using CTE's, which makes me think that you cannot use that approach.

I would suggest doing this either using a while loop, or if performance becomes a problem using a CLR stored proc.

Of course, these are my observations - hopefully someone else will post an elegant solution from which all of us can learn.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2012-04-26 : 16:56:09
This part is not clear:
quote:
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
Does that mean an applicant is only selected for 1 program? If so, is that the first program based on the selection and priority values?
Go to Top of Page

wkm1925
Posting Yak Master

207 Posts
Posted - 2012-04-26 : 19:44:16
quote:
Originally posted by robvolk

This part is not clear:
quote:
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
Does that mean an applicant is only selected for 1 program? If so, is that the first program based on the selection and priority values?


Does that mean an applicant is only selected for 1 program?
Yes sir. If 1st program NOT MEET THE REQUIREMENT, it will select the 2nd program and do the requirement checking, and so on

*Please let me know, if the data is not enough / not accurate to perform the T-SQL
Go to Top of Page

wkm1925
Posting Yak Master

207 Posts
Posted - 2012-04-27 : 00:44:29
Looks like I need to using CURSORs. But I dont know, how to do it
Go to Top of Page

wkm1925
Posting Yak Master

207 Posts
Posted - 2012-04-27 : 11:02:00
I got 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


how to make sure there is no any T-SQL that interfere, and give effect the result. Another word, I want to lock the programQuota row
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-04-27 : 11:28:18
That is a nice idea. If I understood you correctly, you are giving everyone everything they asked for, and then deleting anything that exceeds the quotas. Can't see anything obviously wrong with it. Great concept!

As for locking program quota, you could add two more columns to the ProgramQuota table - mRemaing, and fRemaining and insert zero into each, indicating that there aren't any slots remaining. Of course, you will need to check if there are any remaining in your query when you do the allocation.
Go to Top of Page

wkm1925
Posting Yak Master

207 Posts
Posted - 2012-04-28 : 23:44:42
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 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 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 my T-SQL, cover that
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-04-29 : 08:08:21
It may be that you need to delete in a loop - i.e., you have to delete lower ranking assignments for each row in the programQuota table. For example, to account for cases where a person's first choice and second choice are not available.

When you first posted this, I wrote something based on a loop, but then did not proceed with it further when I saw your query which seemed to accomplish the goal in a set-based query. In any case, here is the query I wrote - it gives the right results for your second example, but I have not tested it only minimally:
----------------------------------------------------------------------------
IF (OBJECT_ID('tempdb..#Apply') IS NOT NULL) DROP TABLE #Apply;
IF (OBJECT_ID('tempdb..#Quota') IS NOT NULL) DROP TABLE #Quota;

-- Save [applyProgram] data into temporary tables
;WITH cte AS 
(
	SELECT 
		*, 
		ROW_NUMBER() OVER (ORDER BY mySelection, myRanking) Rnk,
		0 AS Processed
	FROM 
		[applyProgram]
) SELECT * INTO #Apply FROM cte;

-- save [programQuota] data into temporary table.
SELECT 
	*,
	mQuota AS mRemaining, 
	fQuota AS fRemaining 
INTO 
	#Quota 
FROM 
	[programQuota]

-- Run a while loop----------------------------------------
DECLARE @next INT = 1;
DECLARE @idx int, @applicantIdx INT, @gender VARCHAR(1), 
		@IptIdx INT, @kursusIdx INT;

DECLARE @cnt INT; 
SELECT @cnt = COUNT(*) FROM #Apply

WHILE (@cnt > 0)
BEGIN
	SET @cnt = @cnt - 1;
	
	SELECT TOP 1 
		@applicantIdx = applicantIdx,
		@gender = gender,
		@IptIdx = IptIdx,
		@kursusIdx = kursusIdx
	FROM #Apply a
	WHERE 
		processed = 0
		AND EXISTS 
		(
			SELECT * FROM #Quota q 
			WHERE q.iptIdx = a.iptIdx AND q.kursusIdx = a.kursusIdx
			AND ( (a.gender = 'm' AND q.mRemaining > 0) OR (a.gender = 'f' AND q.fRemaining > 0) )
		)
	ORDER BY Rnk;
	SET @next = @@ROWCOUNT;
	IF (@next > 0)
	BEGIN
		
		-- find the id of the Quota from which this slot is dispensed.
		SELECT @idx = Idx FROM #Quota 
		WHERE @IptIdx = IptIdx AND @kursusIdx = kursusIdx

		-- subtract one from quota
		UPDATE #Quota SET
			mRemaining = CASE WHEN @gender = 'm' THEN mRemaining-1 ELSE mRemaining END,
			fRemaining = CASE WHEN @gender = 'f' THEN fRemaining-1 ELSE fRemaining END
		WHERE
			@IptIdx = IptIdx
			AND @kursusIdx = kursusIdx
			AND @idx = idx;
			
		-- mark this applicant as processed
		UPDATE #Apply SET
			Processed = 1
		WHERE
			applicantIdx = @applicantIdx;
		
		-- insert the selection into the table.
		INSERT INTO selectedApplicant
		SELECT
			@applicantIdx,
			@IptIdx,
			@kursusIdx,
			@idx 
			
	END
END


SELECT * FROM selectedApplicant
IF (OBJECT_ID('tempdb..#Apply') IS NOT NULL) DROP TABLE #Apply;
IF (OBJECT_ID('tempdb..#Quota') IS NOT NULL) DROP TABLE #Quota;
Go to Top of Page

wkm1925
Posting Yak Master

207 Posts
Posted - 2012-04-29 : 08:22:50
Kindest Sunita Beck,

Your query look's ok. I will inform if something wrong happen

tq
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site