I too need help to write T-SQL based on data

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)

I too need help to write T-SQL based on data

Author

Topic

wkm1925
Posting Yak Master

207 Posts

Posted - 2012-04-25 : 02:15:05

I've tables and data as following,


declare @tQuota table (idx int, iptIdx int, kursusIdx int, mQuota int, fQuota int)
insert into @tQuota values(100, 22, 155, 1, 0);
insert into @tQuota values(101, 23, 155, 0, 0);
insert into @tQuota values(102, 34, 17, 1, 1);
insert into @tQuota values(103, 22, 155, 1, 1);

/*
idx is primary key
iptIdx, and kursusIdx is a unique

iptIdx, and kursusIdx is a program's choice
mQuota is quota for male
fQuota is quota for female

@tQuota save how many places the program is offered
*/


declare @tApplyProgram table (applicantIdx int,gender char(1), iptIdx int, kursusIdx int, mySelection int, myRanking int)
insert into @tApplyProgram values(1925, 'm', 23, 155, 1, 1);
insert into @tApplyProgram values(4474, 'f', 22, 155, 1, 1);
insert into @tApplyProgram values(4474, 'f', 34, 17, 2, 1);
insert into @tApplyProgram values(1925, 'f', 23, 155, 2, 1);
insert into @tApplyProgram values(1993, 'm', 22, 155, 1, 2);
insert into @tApplyProgram values(1493, 'm', 22, 155, 1, 4);
/*
applicantIdx, iptIdx, and kursusIdx is a unique
gender is only m and f
iptIdx, and kursusIdx is a program's choice
1 applicantIdx can have many program's choice

@tApplyProgram(iptIdx,kursusIdx) is refer to @tQuota(iptIdx,kursusIdx)
@tApplyProgram save the student and the program of their choice
*/

declare @tSelectedApplicant table (applicantIdx int, iptIdx int, kursusIdx int, tQuotaIdx int)
/*
applicantIdx, iptIdx, and kursusIdx is a unique
*/

What I want to do?
1. Each data in @tApplyProgram need to be processed based on the data in @tQuota
2. Each data in @tApplyProgram will be processed by priority. The priority based on @tApplyProgram(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. Each data in @tApplyProgram meet the requirement based on the data in @tQuota, will be inserted into @tSelectedApplicant

The result explanation as following --- order by mySelection, myRanking,



1925    | 23 | 155 | 101 --- cannot be inserted because current mQuota=0

4474    | 22 | 155 | 100 --- This rows will be processed first compare than 1993,f,22,155 because of the rules of mySelection, and myRanking --- order by mySelection, myRanking. But it's cannot be inserted because current fQuota=0

1993    | 22 | 155 | 100 --- this rows will be inserted. Then, the current mQuota=1-1

1493    | 22 | 155 | 100 --- this rows cannot be inserted. It's because current mQuota=0 (has been taken by 1993    | 22 | 155 | 100)

4474    | 34 | 17  | 102 --- this rows will be inserted. Then, the current fQuota=1-1

1925    | 23 | 155 | 101 --- this rows cannot be inserted. Then, the current fQuota=0

The expected inserted rows in @tSelectedApplicant as following,



applicantIdx | iptIdx | kursusIdx |tQuotaIdx
----------------------------------------------------
1993    | 22 | 155 | 100
4474    | 34 | 17  | 102

I really need help

*If my expected result looks weird and impossible will happened, please let me know

Sachin.Nand

2937 Posts

Posted - 2012-04-25 : 03:15:29

Ok I am getting this result using the query below but it is no complete.So just wanted to cross check before moving further

select Q.*,P.applicantIdx,P.myRanking,P.mySelection from @tQuota Q 
inner join @tApplyProgram P on Q.iptIdx=P.iptIdx and Q.kursusIdx=P.kursusIdx
where NULLIF(Q.mQuota,Q.fQuota) is null

idx	iptIdx	kursusIdx	mQuota	fQuota	applicantIdx	myRanking	mySelection
             101	23	155	            0	0	1925	               1	1
             101	23	155	            0	0	1925	               1	2
             102	34	17	            1	1	4474	               1	2
             103	22	155	            1	1	4474	               1	1
             103	22	155	            1	1	1993	               2	1
             103	22	155	            1	1	1493	               4	1

After Monday and Tuesday even the calendar says W T F ....

wkm1925
Posting Yak Master

207 Posts

Posted - 2012-04-25 : 07:45:10

I re-write,

I've tables and data as following,


declare @tQuota table (idx int, iptIdx int, kursusIdx int, mQuota int, fQuota int)
insert into @tQuota values(100, 22, 155, 1, 0);
insert into @tQuota values(101, 23, 155, 0, 0);
insert into @tQuota values(102, 34, 17, 1, 1);
insert into @tQuota values(103, 22, 155, 1, 1);

/*
idx is primary key
iptIdx, and kursusIdx is a unique

iptIdx, and kursusIdx is a program's choice
mQuota is quota for male
fQuota is quota for female

@tQuota save how many places the program is offered
*/


declare @tApplyProgram table (applicantIdx int,gender char(1), iptIdx int, kursusIdx int, mySelection int, myRanking int)
insert into @tApplyProgram values(1925, 'm', 23, 155, 1, 1);
insert into @tApplyProgram values(4474, 'f', 22, 155, 1, 1);
insert into @tApplyProgram values(4474, 'f', 34, 17, 2, 1);
insert into @tApplyProgram values(1925, 'f', 23, 155, 2, 1);
insert into @tApplyProgram values(1993, 'm', 22, 155, 1, 2);
insert into @tApplyProgram values(1493, 'm', 22, 155, 1, 4);
/*
applicantIdx, iptIdx, and kursusIdx is a unique
gender is only m and f
iptIdx, and kursusIdx is a program's choice
1 applicantIdx can have many program's choice

@tApplyProgram(iptIdx,kursusIdx) is refer to @tQuota(iptIdx,kursusIdx)
@tApplyProgram save the student and the program of their choice
*/

declare @tSelectedApplicant table (applicantIdx int, iptIdx int, kursusIdx int, tQuotaIdx int)
/*
applicantIdx, iptIdx, and kursusIdx is a unique
*/



1925    | 23 | 155 | 101 --- cannot be inserted because current mQuota=0

4474    | 22 | 155 | 100 --- This rows will be processed first compare than 1993,f,22,155 because of the rules of mySelection, and myRanking --- order by mySelection, myRanking. But it's cannot be inserted because current fQuota=0

1993    | 22 | 155 | 100 --- this rows will be inserted. Then, the current mQuota=1-1

1493    | 22 | 155 | 100 --- this rows cannot be inserted. It's because current mQuota=0 (has been taken by 1993    | 22 | 155 | 100)

4474    | 34 | 17  | 102 --- this rows will be inserted. Then, the current fQuota=1-1

1925    | 23 | 155 | 101 --- this rows cannot be inserted. Then, the current fQuota=0

The expected inserted rows in @tSelectedApplicant as following,



applicantIdx | iptIdx | kursusIdx |tQuotaIdx
----------------------------------------------------
1993    | 22 | 155 | 100
4474    | 34 | 17  | 102

I really need help

*If my expected result looks weird and impossible will happened, please let me know

Sachin.Nand

2937 Posts

Posted - 2012-04-25 : 07:53:02

Ok.Your new requirement is quite different than what you had posted earlier.

After Monday and Tuesday even the calendar says W T F ....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-25 : 08:01:05

iptIdx, and kursusIdx is a unique ? Your data does not looks like it

quote:

declare @tQuota table (idx int, iptIdx int, kursusIdx int, mQuota int, fQuota int)
insert into @tQuota values(100, 22, 155, 1, 0);
insert into @tQuota values(101, 23, 155, 0, 0);
insert into @tQuota values(102, 34, 17, 1, 1);
insert into @tQuota values(103, 22, 155, 1, 1);

/*
idx is primary key
iptIdx, and kursusIdx is a unique

iptIdx, and kursusIdx is a program's choice
mQuota is quota for male
fQuota is quota for female

@tQuota save how many places the program is offered
*/

KH
[spoiler]Time is always against us[/spoiler]

wkm1925
Posting Yak Master

207 Posts

Posted - 2012-04-25 : 08:12:36

Tq khtan.

I re-write,

I've tables and data as following,


declare @tQuota table (idx int, iptIdx int, kursusIdx int, mQuota int, fQuota int)
insert into @tQuota values(100, 22, 155, 1, 0);
insert into @tQuota values(101, 23, 155, 0, 0);
insert into @tQuota values(102, 34, 17, 1, 1);
insert into @tQuota values(103, 22, 156, 1, 1);

/*
idx is primary key
iptIdx, and kursusIdx is a unique

iptIdx, and kursusIdx is a program's choice
mQuota is quota for male
fQuota is quota for female

@tQuota save how many places the program is offered
*/


declare @tApplyProgram table (applicantIdx int,gender char(1), iptIdx int, kursusIdx int, mySelection int, myRanking int)
insert into @tApplyProgram values(1925, 'm', 23, 155, 1, 1);
insert into @tApplyProgram values(4474, 'f', 22, 155, 1, 1);
insert into @tApplyProgram values(4474, 'f', 34, 17, 2, 1);
insert into @tApplyProgram values(1925, 'f', 23, 155, 2, 1);
insert into @tApplyProgram values(1993, 'm', 22, 155, 1, 2);
insert into @tApplyProgram values(1493, 'm', 22, 155, 1, 4);
/*
applicantIdx, iptIdx, and kursusIdx is a unique
gender is only m and f
iptIdx, and kursusIdx is a program's choice
1 applicantIdx can have many program's choice

@tApplyProgram(iptIdx,kursusIdx) is refer to @tQuota(iptIdx,kursusIdx)
@tApplyProgram save the student and the program of their choice
*/

declare @tSelectedApplicant table (applicantIdx int, iptIdx int, kursusIdx int, tQuotaIdx int)
/*
applicantIdx, iptIdx, and kursusIdx is a unique
*/



1925    | 23 | 155 | 101 --- cannot be inserted because current mQuota=0

4474    | 22 | 155 | 100 --- This rows will be processed first compare than 1993,f,22,155 because of the rules of mySelection, and myRanking --- order by mySelection, myRanking. But it's cannot be inserted because current fQuota=0

1993    | 22 | 155 | 100 --- this rows will be inserted. Then, the current mQuota=1-1

1493    | 22 | 155 | 100 --- this rows cannot be inserted. It's because current mQuota=0 (has been taken by 1993    | 22 | 155 | 100)

4474    | 34 | 17  | 102 --- this rows will be inserted. Then, the current fQuota=1-1

1925    | 23 | 155 | 101 --- this rows cannot be inserted. Then, the current fQuota=0

The expected inserted rows in @tSelectedApplicant as following,



applicantIdx | iptIdx | kursusIdx |tQuotaIdx
----------------------------------------------------
1993    | 22 | 155 | 100
4474    | 34 | 17  | 102

I really need help

*If my expected result looks weird and impossible will happened, please let me know

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-25 : 08:27:35

[code]
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.applicantIdx, ap.mySelection, ap.myRanking)
from @tApplyProgram ap
inner join @tQuota 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)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

wkm1925
Posting Yak Master

207 Posts

Posted - 2012-04-25 : 12:17:43

quote:

Originally posted by khtan


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.applicantIdx, ap.mySelection, ap.myRanking)
	from	@tApplyProgram ap
		inner join @tQuota 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)

KH
[spoiler]Time is always against us[/spoiler]

Above SQL return,


1493	22	155	100
4474	34	17	102

It's suppose,


1993	22	155	100
4474	34	17	102

1993 have mySelection=1, and myRanking=2, but
1493 have mySelection=1, and myRanking=4

Should be 1993 will be processed first based on priority - mySelection, myRanking

wkm1925
Posting Yak Master

207 Posts

Posted - 2012-04-25 : 22:55:07

Hi Expert,

please help me

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-25 : 23:03:51

change the rank to following. C&P the sequence wrongly


rank	= row_number() over (partition by ap.iptIdx, ap.kursusIdx, ap.gender 
                                 order by ap.mySelection, ap.myRanking, ap.applicantIdx)

KH
[spoiler]Time is always against us[/spoiler]

wkm1925
Posting Yak Master

207 Posts

Posted - 2012-04-25 : 23:10:06

Kindest Mr Khtan,

Your guidance is an inspiration for me

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-25 : 23:25:02

welcome

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources