August 31, 2020 at 5:51 pm
I already tried something similar. But, it does not work.
Cheers
August 31, 2020 at 5:53 pm
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 31, 2020 at 6:38 pm
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'),
(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,'309 Des Lilas'),
(5,'294 des Lilas'),
(5,'294 des Lilas'),
(5,'302 Des Lilas'),
(5,'302 Des Lilas'),
(6,'293 des Lilas'),
(6,'268 Des Lilas'),
(6,'268 Des Lilas'),
(6,'272 des Lilas'),
(6,'272 des Lilas'),
(7,'298 Des Peupliers'),
(7,'319 des Lilas'),
(7,'302 des Peupliers'),
(7,'302 des Peupliers'),
(7,'303 Des Peupliers'),
(7,'303 Des Peupliers'),
(7,'306 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,'358 Des érables'),
(9,'378 Des Érables'),
(9,'378 Des Erables'),
(9,'378 Des Erables'),
(10,'322 Des Cèdres'),
(10,'342 Des Érables'),
(10,'342 Des Érables'),
(10,'321 Des Cèdres'),
(10,'321 Des Cèdres'),
(11,'296 des Erables'),
(12,'289 des Bouleaux'),
(12,'291 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
August 31, 2020 at 6:45 pm
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 31, 2020 at 7:03 pm
Thanks Phil for your time.
Cheers
August 31, 2020 at 7:42 pm
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
September 1, 2020 at 9:18 am
ps I think this is some sort of packing or loading algorithm but the details are not clear.
September 1, 2020 at 1:03 pm
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
September 1, 2020 at 1:39 pm
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 1, 2020 at 2:21 pm
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)
September 1, 2020 at 2:36 pm
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.
😎
September 1, 2020 at 2:45 pm
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)
September 1, 2020 at 3:00 pm
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?
September 4, 2020 at 12:43 pm
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.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply