Applying a rule through querys?

[Guest]

Hi,

I've got a list with people who would be buying a house.

The people who lives in the same town off the project gets a priority-rule.

The rule is that these people can shift 50% forwards but the other ones can
move backward only ones.

Example:

Nr Name Priority
1 Pers1 yes
2 Pers2 no
3 Pers3 no
4 Pers4 yes
5 Pers5 yes


After applying the rule:

Nr Name Priority
1 1 Pers1 yes
2 4 Pers4 yes
3 2 Pers2 no
4 5 Pers5 yes
5 3 Pers3 no

Who can help me with this.

 

[Jamie Collins]

I've got a list with people who would be buying a house.

The people who lives in the same town off the project gets a priority-rule.

The rule is that these people can shift 50% forwards but the other ones can
move backward only ones.

Example:

Nr Name Priority
1 Pers1 yes
2 Pers2 no
3 Pers3 no
4 Pers4 yes
5 Pers5 yes


After applying the rule:

Nr Name Priority
1 1 Pers1 yes
2 4 Pers4 yes
3 2 Pers2 no
4 5 Pers5 yes
5 3 Pers3 no

A quick stab (the really small DECIMAL value is to break ties in favour
of Priority = 'Y'):

SELECT H1.Nr, H1.Name, H1.Priority,
IIF(H1.Priority = 'Y',
H1.Nr * 0.5,
H1.Nr + 0.00000000000000000001) AS new_rank
FROM Housing AS H1;

To get the new Nr i.e. an INTEGER rank rather than a DECIMAL, just plug
the resultset back in (oh for CTE's in Jet <g>):

SELECT DT1.Nr, DT1.Name, DT1.Priority,
(
SELECT COUNT(*)
FROM
(
SELECT H1.Nr, H1.Name, H1.Priority,
IIF(H1.Priority = 'Y',
H1.Nr * 0.5,
H1.Nr + 0.00000000000000000001) AS new_rank
FROM Housing AS H1
) AS DT2
WHERE DT2.new_rank <= DT1.new_rank
) AS new_Nr
FROM
(
SELECT H1.Nr, H1.Name, H1.Priority,
IIF(H1.Priority = 'Y',
H1.Nr * 0.5,
H1.Nr + 0.00000000000000000001) AS new_rank
FROM Housing AS H1
) AS DT1;

Jamie.

--

 

[Guest]

A quick stab (the really small DECIMAL value is to break ties in favour
of Priority = 'Y'):

SELECT H1.Nr, H1.Name, H1.Priority,
IIF(H1.Priority = 'Y',
H1.Nr * 0.5,
H1.Nr + 0.00000000000000000001) AS new_rank
FROM Housing AS H1;

Thanks,

This code works fine.
Monday i can compare this result with the manual result of my boss. On a
first glimp it looks great.