April 25, 2012 at 11:25 pm
My tables and data as following,
USE [myTest]
GO
/****** Object: Table [dbo].[selectedApplicant] Script Date: 04/26/2012 13:20:37 ******/
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 13:20:37 ******/
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 13:20:37 ******/
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)
/****** Object: Check [CK_applyProgram1] Script Date: 04/26/2012 13:20:37 ******/
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 will remain for others
So far, I've as following,
selectapplicantIdx, iptIdx, kursusIdx, tQuotaIdx
from
(
selectap.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)
fromapplyProgram ap
inner join programQuota tqonap.iptIdx= tq.iptIdx
andap.kursusIdx= tq.kursusIdx
where(
(ap.gender= 'm'andtq.mQuota> 0)
or(ap.gender= 'f'andtq.fQuota> 0)
)
) s
where(gender= 'm'andmQuota >= rank)
or(gender= 'f'andfQuota >= rank)
My resultset based on above T-SQL,
192522155100
199322155100
44743417102
19253417102
At this level, looks like 1925 HAS GAINED A PROGRAM, and still GAINED ANOTHER PROGRAM
Need expert help to customise my T-SQL
April 26, 2012 at 4:17 am
You just need to number the programs accepted and pick the first one, like this:
WITH CTE AS (
selectapplicantIdx, iptIdx, kursusIdx, tQuotaIdx
,ROW_NUMBER() OVER (PARTITION BY applicantIdx ORDER BY applicantIdx) As PgmNo
from
(
selectap.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)
fromapplyProgram ap
inner join programQuota tqonap.iptIdx= tq.iptIdx
andap.kursusIdx= tq.kursusIdx
where(
(ap.gender= 'm'andtq.mQuota> 0)
or(ap.gender= 'f'andtq.fQuota> 0)
)
) s
where(gender= 'm'andmQuota >= rank)
or(gender= 'f'andfQuota >= rank))
SELECT applicantIdx, iptIdx, kursusIdx, tQuotaIdx
FROM CTE
WHERE PgmNo = 1
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 26, 2012 at 5:05 am
Sir, your query still not cover the requirement as following,
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
I re-write my 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
My query as following,
selectapplicantIdx, iptIdx, kursusIdx, tQuotaIdx
from
(
selectap.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)
fromapplyProgram ap
inner join programQuota tqonap.iptIdx= tq.iptIdx
andap.kursusIdx= tq.kursusIdx
where(
(ap.gender= 'm'andtq.mQuota> 0)
or(ap.gender= 'f'andtq.fQuota> 0)
)
) s
where(gender= 'm'andmQuota >= rank)
or(gender= 'f'andfQuota >= rank)
will return
192522155100
199322155100
44743417102
19253417102 /*this row is not accurate. applicantIdx HAS GAINED A PROGRAM before ~ refer 1925| 22| 155| 100*/
29003417102
it's suppose
192522155100
199322155100
44743417102
29003417102
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*/
Due to my query is return an inaccurate data, your query will effect and also return the inaccurate data
Need expert help
April 26, 2012 at 5:16 am
In truth, I didn't try to go through all of your requirements in detail to see which were being fulfilled. You only stated at the end that the extra program was coming in when it shouldn't. I didn't realize there were other issues to ferret out.
If I have some time, I'll try to take another look.
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 26, 2012 at 1:06 pm
Really looking for help
April 26, 2012 at 1:15 pm
idyana (4/26/2012)
Really looking for help
Please realize that we are volunteers and help when we can. While you are waiting, please feel free to continue working on your problem.
April 26, 2012 at 5:51 pm
Lynn Pettis (4/26/2012)
idyana (4/26/2012)
Really looking for helpPlease realize that we are volunteers and help when we can. While you are waiting, please feel free to continue working on your problem.
Sir,
If your question seems like Does that mean an applicant is only selected for 1 program?
The answer is YES. 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 T-SQL
April 26, 2012 at 6:54 pm
idyana,
As I promised, I am willing to take another shot at this but before I do, what I'm going to need from you is an exact expected results set based on the sample data that you provided. I am not sure that the last one you posted (showing the missing program) is 100% the expected result.
I'd rather not go through multiple iterations trying to get it right. Your post saying my results were "inaccurate" didn't exactly motivate me, as I considered them to consistent with what you said you wanted to remove.
As Lynn said, we are all volunteers here. You can't expect us to spend hours poring over your requirements and checking your results, then telling you where you have erred. You must clearly elaborate where your query seems to be failing and illustrating that point to ensure you get the specific results that you want in the shortest period of time.
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 26, 2012 at 8:26 pm
dwain.c (4/26/2012)
idyana,As I promised, I am willing to take another shot at this but before I do, what I'm going to need from you is an exact expected results set based on the sample data that you provided. I am not sure that the last one you posted (showing the missing program) is 100% the expected result.
I'd rather not go through multiple iterations trying to get it right. Your post saying my results were "inaccurate" didn't exactly motivate me, as I considered them to consistent with what you said you wanted to remove.
As Lynn said, we are all volunteers here. You can't expect us to spend hours poring over your requirements and checking your results, then telling you where you have erred. You must clearly elaborate where your query seems to be failing and illustrating that point to ensure you get the specific results that you want in the shortest period of time.
I re-write my tables and data,
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
So far I've as following,
selectapplicantIdx, iptIdx, kursusIdx, tQuotaIdx
from
(
selectap.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)
fromapplyProgram ap
inner join programQuota tqonap.iptIdx= tq.iptIdx
andap.kursusIdx= tq.kursusIdx
where(
(ap.gender= 'm'andtq.mQuota> 0)
or(ap.gender= 'f'andtq.fQuota> 0)
)
) s
where(gender= 'm'andmQuota >= rank)
or(gender= 'f'andfQuota >= rank)
Above SQL will return,
192522155100
199322155100
44743417102
19253417102 /*this row is not accurate. applicantIdx HAS GAINED A PROGRAM before ~ refer 1925| 22| 155| 100*/
29003417102
But, based on requirement, it's suppose return as following,
192522155100
199322155100
44743417102
29003417102
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*/
If the question 'Does that mean an applicant is only selected for 1 program?' in your mind,
The answer is, yes sir. If 1st program NOT MEET THE REQUIREMENT, it will select the 2nd program and do the requirement checking, and so on
April 26, 2012 at 8:51 pm
No need to reiterate what you've already posted. Most members are capable enough to scroll up and see it.
What I was asking you to confirm is whether the last results set you are showing is finally, exactly what you are looking for. Meaning there's no other issues other than the one missing record.
If you can confirm that I'll look again.
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 26, 2012 at 9:03 pm
It's confirm sir
April 26, 2012 at 9:49 pm
I see the issue now. Correcting it though... that's an entirely different matter.
It is counting 1925 against the quota for the course he was eliminated from, thus excluding 2980 as it thinks the quota for that course was reached.
This seems to imply RBAR thinking in terms of the query you've constructed even though it is a good attempt at a SET-based solution.
There is probably a solution. Just haven't come up with it yet. You may need to give me some time on this as at the moment I have some other things to attend to.
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 26, 2012 at 11:35 pm
This seems to imply RBAR thinking in terms of the query you've constructed even though it is a good attempt at a SET-based solution.
Let me try to better explain my statement above. To work towards a solution to this problem I believe you must think in terms of RBAR.
I believe (not 100% sure mind you but I think so) that this problem falls into that special class of linear programming problems known as integer programming. I do not believe that it falls into the NP-hard class of integer programming problems because a near optimal solution should be resolvable in linear time.
However none of the linear programming solutions I've heard of are deterministic. Specifically Simplex, and all other algorithms that I know of for this, are iterative and that implies row based thinking.
The idea is that you're really trying to maximize enrollment in each course based on a set of constraints which are represented by applications and quotas by sex. Even though finding the maximum is a relaxable requirement, you do need to find a solution that fits the constraints.
I believe that a CURSOR may be required to solve this.
Note all the maybes, and "I thinks," etc. that I'm hedging this description with because I might be completely full of baloney. 🙂
I'm not giving up mind you. Just thinking out loud.
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 12:39 am
If Cursors need to be used, I'm ok with that. Unfortunately, I dont have any experience using Cursors.
April 27, 2012 at 12:45 am
Neither do I ... Hehe
I think it might be doable with a loop that doesn't involve a CURSOR. Working on that but it may take awhile.
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
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply