Reorder rank upon condition

Question

Post reply

Reorder rank upon condition

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply

Matrix_you SSC Enthusiast Points: 189 More actions · Answer 1

I already tried something similar. But, it does not work.

Cheers

Phil Parkin SSC Guru Points: 246988 More actions · Answer 2

Matrix_you wrote:

I already tried something similar. But, it does not work.
Cheers

Not very helpful.

It provides the output you requested, so what's the problem?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Matrix_you SSC Enthusiast Points: 189 More actions · Answer 3

For explanation, run the following:

DROP TABLE IF EXISTS #Custom;

CREATE TABLE #Custom

(

Noseqtrt SMALLINT NULL

,CUST_ADDRESS NVARCHAR(100) NULL

);

INSERT #Custom

(

Noseqtrt

,CUST_ADDRESS

)

VALUES

(1,'21 Beauchamps'),

(1,'29 Beauchamps'),

(1,'33 Beauchamps'),

(2,'272 de la Presquîle'),

(3,'288 De La Presquile'),

(4,'311 De La Presquile'),

(5,'288 Des Lilas'),

(5,'305 Des Lilas'),

(5,'308 Des Lilas'),

(5,'311 Des Lilas'),

(5,'309 Des Lilas'),

(5,'294 des Lilas'),

(5,'302 Des Lilas'),

(6,'293 des Lilas'),

(6,'268 Des Lilas'),

(6,'272 des Lilas'),

(7,'298 Des Peupliers'),

(7,'319 des Lilas'),

(7,'302 des Peupliers'),

(7,'303 Des Peupliers'),

(7,'306 Des peupliers'),

(8,'322 de la Presquîle'),

(9,'359 des Érables'),

(9,'384 Des Érables'),

(9,'392 Des Érables'),

(9,'392 des Érables'),

(9,'358 Des érables'),

(9,'378 Des Érables'),

(9,'378 Des Erables'),

(10,'322 Des Cèdres'),

(10,'342 Des Érables'),

(10,'321 Des Cèdres'),

(11,'296 des Erables'),

(12,'289 des Bouleaux'),

(12,'291 Des Bouleaux'),

(13,'273 des Bouleaux'),

(13,'278 des Bouleaux'),

(13,'279 des Chênes')

WITH Grouped

AS (SELECT c.Noseqtrt

,c.CUST_ADDRESS

,ct = COUNT(1)

FROM #Custom c

GROUP BY c.Noseqtrt

,c.CUST_ADDRESS)

SELECT Grouped.Noseqtrt

,Grouped.CUST_ADDRESS

,Rank2 = ROW_NUMBER() OVER (ORDER BY Grouped.Noseqtrt, Grouped.ct)

FROM Grouped

JOIN #Custom c

ON c.Noseqtrt = Grouped.Noseqtrt

AND c.CUST_ADDRESS = Grouped.CUST_ADDRESS;

Basically, its more data but it show what i meant.

Thanks for your input

Cheers

Phil Parkin SSC Guru Points: 246988 More actions · Answer 4

I am going to let someone else try to help you. I do not understand.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Matrix_you SSC Enthusiast Points: 189 More actions · Answer 5

Matrix_you

SSC Enthusiast

Points: 189

August 31, 2020 at 7:03 pm

#3784994

Thanks Phil for your time.

Cheers

Ken McKelvey SSCoach Points: 18859 More actions · Answer 6

The logic seems to be that, for each Noseqtrt, the rank for an address with an even count has to start with an odd number.

This sort of logic is best done in the middle tier.

With the original test data this is easy as there happens to be an even count before Noseqtrt 4 so just rank the even ADDRESS_COUNTs first.

Things get complicated in tsql as soon as start to allow for odd counts before, Noseqtrt with only even ADDRESS_COUNTs etc

This reply was modified 4 years, 4 months ago by Ken McKelvey.

Ken McKelvey SSCoach Points: 18859 More actions · Answer 7

ps I think this is some sort of packing or loading algorithm but the details are not clear.

Matrix_you SSC Enthusiast Points: 189 More actions · Answer 8

I was hoping for a quick resolution. But i guess tou ar right i need to develop an algo.

Thanks for all the help

Cheers

Phil Parkin SSC Guru Points: 246988 More actions · Answer 9

Matrix_you wrote:

I was hoping for a quick resolution. But i guess tou ar right i need to develop an algo.
Thanks for all the help
Cheers

Had you been able/prepared to accurately describe your desired ranking logic, a quick solution may have been forthcoming. But as it is, I believe the shortage of coded responses is due to people's general lack of understanding.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 10

Phil Parkin wrote:

I am going to let someone else try to help you. I do not understand.

Where is JC when you "need" him 😉

😎

sgmunson SSC Guru Points: 110640 More actions · Answer 11

Honestly, I have to wonder what the end objective is here. If anyone thinks that they can just dictate a specific bus seat for a specific child or set of children on a school bus, and all because of COVID-19, and that this is actually going to work AND be enforced by every single bus driver and followed religiously by every child, ... methinks one should have their head examined. Trying to create a numeric rank that has to be bumped up one for every row AFTER a given one that needs to be incremented in order to start with an odd number, just seems like a serious waste of time. It might be easier to just be able to detect the condition elsewhere in your system, or better yet, get on board the "herd immunity" train and stop trying so hard to proscribe every aspect of a person's life in a vain attempt to protect everyone from everything bad that could ever happen. Honestly, what good is any of that when the bus gets rammed by a semi from the front end, and the kids all scramble to get out the back door? Is the severely injured bus driver going to worry about enforcing that policy then?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 12

sgmunson wrote:

Honestly, I have to wonder what the end objective is here. If anyone thinks that they can just dictate a specific bus seat for a specific child or set of children on a school bus, and all because of COVID-19, and that this is actually going to work AND be enforced by every single bus driver and followed religiously by every child, ... methinks one should have their head examined. Trying to create a numeric rank that has to be bumped up one for every row AFTER a given one that needs to be incremented in order to start with an odd number, just seems like a serious waste of time. It might be easier to just be able to detect the condition elsewhere in your system, or better yet, get on board the "herd immunity" train and stop trying so hard to proscribe every aspect of a person's life in a vain attempt to protect everyone from everything bad that could ever happen. Honestly, what good is any of that when the bus gets rammed by a semi from the front end, and the kids all scramble to get out the back door? Is the severely injured bus driver going to worry about enforcing that policy then?

(almost) no pun intended, but this is starting to look like one of UK Gov's COVID-19 plans.

😎

sgmunson SSC Guru Points: 110640 More actions · Answer 13

Eirikur Eiriksson wrote:

sgmunson wrote:
Honestly, I have to wonder what the end objective is here. If anyone thinks that they can just dictate a specific bus seat for a specific child or set of children on a school bus, and all because of COVID-19, and that this is actually going to work AND be enforced by every single bus driver and followed religiously by every child, ... methinks one should have their head examined. Trying to create a numeric rank that has to be bumped up one for every row AFTER a given one that needs to be incremented in order to start with an odd number, just seems like a serious waste of time. It might be easier to just be able to detect the condition elsewhere in your system, or better yet, get on board the "herd immunity" train and stop trying so hard to proscribe every aspect of a person's life in a vain attempt to protect everyone from everything bad that could ever happen. Honestly, what good is any of that when the bus gets rammed by a semi from the front end, and the kids all scramble to get out the back door? Is the severely injured bus driver going to worry about enforcing that policy then?
(almost) no pun intended, but this is starting to look like one of UK Gov's COVID-19 plans.
😎

If it hadn't been for the French street names, I'd have suspected the exact same thing. French government tends toward socialist policies, so it may well be the same kind of thing.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 14

Matrix_you wrote:

OK the thing behind it is COVID-19.
The customers are students boarding a bus so if at one stop i have more than one address then i have siblings and they must sit on the same seat ( they can sit 2 by seat). So when i fill the bus i have to respect the sequence of Noseqtrt ( which represent a stop ) the best that i can.
Thank you for your response.
Cheers

This statement just does not make sense, how can more than one address ever translate to siblings? Do you mean more than one from the same address?

😎

Repeating the question again, can you explain the logic, this time as you would to a five year old?

Chris Wooding SSCarpal Tunnel Points: 4560 More actions · Answer 15

My guess at the logic is that the seats are in pairs. Single children can sit anywhere there is a space, but siblings need to sit together, so their seat numbers must be consecutive and start on an odd number. It would make more sense if there had to be an empty seat between children from different households in order to preserve social distancing and the problem would then be easier because each household of children would be allocated seats starting with the next available odd number - regardless of how many were in the household. As it is, an odd number of siblings will end up with someone from a different household sitting next to them in the spare seat making the whole grouping idea somewhat pointless.