Reorder rank upon condition
-
What i like to do is to rank that when a customer address come more than once ( like 1390 des Rosiers) the first of the group should be a uneven number while respecting the order in NoSeqTrt ?
Hoping that my explication is clear.
Thanks in advance for your advice.
Cheers
-
Not for me. Can you show at least a partial expected result from the query using the data you provided?
-
Welcome to the forum Matrix_you!
Can you please post the DDL (create table) scripts, the test data as an insert statement, what you have tried so far and the desired results?
😎
-
ROW_NUMBER() ???
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Here is the DDL:
CREATE TABLE [dbo].[CUSTOM](
[Noseqtrt] [smallint] NULL,
[CUST_ADDRESS] [nvarchar](100) NULL,
[ADDRESS_COUNT] smallint NULL,
[RANK] [int] NULL
) ON [PRIMARY]
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (2,'133 Du Croissant-du-Bourg',1,1)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (2,'1440 des Roses',1,2)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (2,'1483 de Orchidée',1,3)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (2,'164 Du Croissant-du-Bourg',1,4)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (3,'1427 De Orchidée',1, 5)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (3,'1454 de Orchidee',1,6)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (4,'1328 de Orchidée',1,7)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (4,'1340 de Azalée',1,8)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (4,'1370 de Azalée',1,9)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (4,'1390 des Rosiers',1,10)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (4,'1390 des Rosiers',2,11)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (4,'1411 de Azalée',1,12)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (4,'1411 de Azalée',2,13)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (4,'68 du Camélia',1,14)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (6,'1361 de Ange-Gardien',1,15)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (7,'7 Montmarquet',1,16)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (8,'487 de la Seugne',1,17)
Here is the desired result:
So far, i don't have an idea of what to do.
Thanks for your reply. It is the first time for me on your forum.
Cheers
-
Could you explain the logic behind how your desired ranking works?
- Matrix_you wrote:
Here is the DDL:
Thank you for the DDL and the desired results. Maybe it is me not having enough coffee this morning, but I struggle with defining the logic.
😎
Can you please explain the logic as you would to a ten year old?
-
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
-
What does the Number Address column represent, exactly?
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 -
That column can be ignore i guess.
Cheers
-
The customers are students boarding a bus so if at one stop i have more than one of the same 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.
Just to be more specific.
Cheers
-
Taking the case where NoSeqTrt = 4, would it be correct to say that there are eight students at that stop, including two sets of siblings?
And your desired ranking logic is to load students with no siblings before students with siblings?
What is the ranking logic if there are more than two siblings?
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 -
Excuse my ignorance, but is this a mixture of a stack (first in first out) and a variable-sized bucket filling problem?
😎
As an example, a bus does A - Z route, A being the furthest away from the school, and then does Z - A. Obviously the Z entries should be at the back, so not to mix with other entries, hence the stack. Now if entires for the same household enter at different stops, then what is most important, the order of entry, the households or the density? If it is the order of entry, then it is a simple stack type problem, if it is the household (address), then it is a mixture of a stack and a fill problem. If the bus's occupational density is a factor, then one has to weight that in with the other two factors. My suggestion for the last one is, that rather than trying to do this on the fly, pre-assign seats according to the requirements, that is doing the calculation once and use it many times. There are hardly any variations in the passenger's population during each term anyway.
-
To Phil,
The order on the same stop is not important except for the siblings.
the goal is to put them on the same seat if they are more like 3 then assign the next place on the next seat if four take the next 2 place on the following seat.
I imagine that sometime the order of stop cannot be followed due to the presence of siblings because of the limit of student per bus.
Thanks for your response
Cheers
-
This provides the desired results (I think), but I'm not convinced it's the best solution.
DROP TABLE IF EXISTS #Custom;
CREATE TABLE #Custom
(
Noseqtrt SMALLINT NULL
,CUST_ADDRESS NVARCHAR(100) NULL
,ADDRESS_COUNT SMALLINT NULL
,RANK INT NULL
);
INSERT #Custom
(
Noseqtrt
,CUST_ADDRESS
,ADDRESS_COUNT
,RANK
)
VALUES
(2, '133 Du Croissant-du-Bourg', 1, 1)
,(2, '1440 des Roses', 1, 2)
,(2, '1483 de Orchidée', 1, 3)
,(2, '164 Du Croissant-du-Bourg', 1, 4)
,(3, '1427 De Orchidée', 1, 5)
,(3, '1454 de Orchidee', 1, 6)
,(4, '1328 de Orchidée', 1, 7)
,(4, '1340 de Azalée', 1, 8)
,(4, '1370 de Azalée', 1, 9)
,(4, '1390 des Rosiers', 1, 10)
,(4, '1390 des Rosiers', 2, 11)
,(4, '1411 de Azalée', 1, 12)
,(4, '1411 de Azalée', 2, 13)
,(4, '68 du Camélia', 1, 14)
,(6, '1361 de Ange-Gardien', 1, 15)
,(7, '7 Montmarquet', 1, 16)
,(8, '487 de la Seugne', 1, 17);
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;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
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply