SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Assign incrementing letters to same value


Assign incrementing letters to same value

Author
Message
Talvin Singh
Talvin Singh
SSC Eights!
SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)

Group: General Forum Members
Points: 918 Visits: 254
hi all,

i have the following table

OrderID Point
Order1 1
Order2 1
Order3 3
Order4 6
Order5 8
Order6 10
Order7 10


i would like the output as

OrderID Point LetterCode
Order1 1 A
Order2 1 A
Order3 3 B
Order4 6 C
Order5 8 D
Order6 10 E
Order7 10 E


as you can see for each order with same point, the same letter gets assigned. Therefore i can group all the letter codes and see what orders belong to a letter code.

if this could be done within a select statement, i can copy the result into another table, or i can create the first table with LetterCode blanked out, and use an update statement. i would prefer the latter, assuming its faster.

many thanks
joujou2006 sha
joujou2006 sha
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1285 Visits: 1538
create table ALPHABET 
(
id int IDENTITY(1,1),
Characters NCHAR(1) PRIMARY KEY )

DECLARE @asciiCode INT= 65 WHILE @asciiCode <= 90 BEGIN

INSERT ALPHABET ( Characters ) SELECT CHAR(@asciiCode)

SELECT @asciiCode = @asciiCode + 1

END
USE [master]
GO
/****** Object: Table [dbo].[order_point] Script Date: 04/02/2018 18:35:33 ******/

CREATE TABLE [dbo].[order_point](
[order1] [varchar](50) NULL,
[point] [int] NULL,
[numero2] [nvarchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[order_point] ([order1], [point]) VALUES (N'Order1', 1)
INSERT [dbo].[order_point] ([order1], [point]) VALUES (N'Order2', 1)
INSERT [dbo].[order_point] ([order1], [point]) VALUES (N'Order3', 3)
INSERT [dbo].[order_point] ([order1], [point]) VALUES (N'Order4', 6)
INSERT [dbo].[order_point] ([order1], [point]) VALUES (N'Order5', 8)
INSERT [dbo].[order_point] ([order1], [point]) VALUES (N'Order6', 10)
INSERT [dbo].[order_point] ([order1], [point]) VALUES (N'Order7', 10)

update C
set C.numero2=A.Characters
from ALPHABET A
INNER JOIN order_point C
ON C.point=A.id


select * from ALPHABET

select * from order_point



Talvin Singh
Talvin Singh
SSC Eights!
SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)

Group: General Forum Members
Points: 918 Visits: 254
not quite.

what the above code does is links the point ON the id in alphabet table.

Point can be any number ie see below.


OrderID Point
Order1 4
Order2 12
Order3 12
Order4 12
Order5 29
Order6 31
Order7 49


I have ordered the list by Point. therefore it starts with A and checks if there is another equal Point, if so then assign A, else B, checks if theres another Point equal to B, and so on, like below:


OrderID Point LetterCode
Order1 4 A
Order2 12 B
Order3 12 B
Order4 12 B
Order5 29 C
Order6 31 D
Order7 49 E


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (882K reputation)SSC Guru (882K reputation)SSC Guru (882K reputation)SSC Guru (882K reputation)SSC Guru (882K reputation)SSC Guru (882K reputation)SSC Guru (882K reputation)SSC Guru (882K reputation)

Group: General Forum Members
Points: 882447 Visits: 47889
Talvin Singh - Sunday, February 4, 2018 11:12 AM
not quite.

what the above code does is links the point ON the id in alphabet table.

Point can be any number ie see below.


OrderID Point
Order1 4
Order2 12
Order3 12
Order4 12
Order5 29
Order6 31
Order7 49


I have ordered the list by Point. therefore it starts with A and checks if there is another equal Point, if so then assign A, else B, checks if theres another Point equal to B, and so on, like below:


OrderID Point LetterCode
Order1 4 A
Order2 12 B
Order3 12 B
Order4 12 B
Order5 29 C
Order6 31 D
Order7 49 E



What do you want done when there are more than 26 different quantities. Also, two way street here... what is the business reason for wanting to do this (and I mean something more than "It was a requirement that someone gave me", if you don't mind).

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Talvin Singh
Talvin Singh
SSC Eights!
SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)

Group: General Forum Members
Points: 918 Visits: 254
it would for sure stay under 26 letters, i think the max ive counting is around 15 letters.
business reason: my university assignment and i want to group these orders by LetterCode. the lettercodes represent a space on a shelf (this is a spare parts business)
Talvin Singh
Talvin Singh
SSC Eights!
SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)

Group: General Forum Members
Points: 918 Visits: 254
to be honest, it could be a numberCode rather than letterCode, i just need a way to group the Points together.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (882K reputation)SSC Guru (882K reputation)SSC Guru (882K reputation)SSC Guru (882K reputation)SSC Guru (882K reputation)SSC Guru (882K reputation)SSC Guru (882K reputation)SSC Guru (882K reputation)

Group: General Forum Members
Points: 882447 Visits: 47889
Talvin Singh - Sunday, February 4, 2018 12:15 PM
to be honest, it could be a numberCode rather than letterCode, i just need a way to group the Points together.


No problem.

Shifting gears a bit, you'll get faster and better answers in the future if you provide your data in a readily consumable format, like this....

SELECT *
INTO #TestTable
FROM (VALUES
('Order1', 4)
,('Order2',12)
,('Order3',12)
,('Order4',12)
,('Order5',29)
,('Order6',31)
,('Order7',49)
)v(OrderID,Point)
;


Try this with that data. I threw in one of the obvious extra columns that I thought you might also be looking for.

SELECT OrderID
,Point
,GroupCode = DENSE_RANK()OVER (ORDER BY Point)
,OrdersInGroup = COUNT(*) OVER (PARTITION BY Point)
FROM #TestTable
ORDER BY GroupCode, OrderID
;


Here are the results.
data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAY4AAADACAYAAAATIYTUAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAAB/DSURBVHhe7Z0PcBXHfcd/ZDLOdMZppzPtpNRIEEaPelKBNYa2IBA4HiYY2SQwEdi1sDVOKyl01CC1UUhszYBm5EwUNZUg6hCJSR2MlQREBxKwZDIUA0LCaeVUBoXBPE0sJFwn4zSTOM5McePQ3b29e3tP78/duzu927vvh1l0f97du9vb29/+9rdvvwv27dt3p2brQ3T85EsEAAAA5MMyHAAAAIATPiD/AgAAAI6A4QAAAOAKGA4AAACumBPj+PP7/kouAZMfv/ZDuQQAAMBmOLjRQCU5l6bmL1Jvz1fkGgAAxBvLcMBoZIcPVd67d69cA25BuQIgWsBwOIAbjvPnz8PrKBB4bABEC1eG48APPyiXMvPqq3IhjcN/91u5pCe5DMfPf/m/4u8777wj/qr84hfv0KqKMrkWX0yPDQ0TAKKB61FVK1euTCX2j22xkm2fTFHn9dfvpg0bNtCWLVtsafXq1TR2+zfyU+DjH/+4XAIA6I5rw/EqcyusxP6xLfZtaUl8JBezR2lnxWoqT0vtl+R+r/DzP3mUZuRqkHCv49X/YbfM0tnpu/LfOwAAaIgrw8H9h76GlVaqv/+O+DveZ6SvPf6utTx6oJzYLkY//y83K1poaOIVmjTTqRZK9vtc2QdsQK5fv07JZJLuvDFON37yK/r1f03JPQAAEC08/QDwN7+xd8WY/fy3b98WiXPXXVfFX1eUlFLiyjTdlKthp595VtxocAfrxz9+nd67dpo+9KHrcm8BmF5Yx2W5gXOLjjzJts2T9wQAANlwZTh4Zchb1uPj43ThwgWxjf81E6e//1X6wQ9+QCdOnBLrBTE7Q8kVS2ixWFa7surpyKz4BM0M1FvdWjsHbs31KGzr0/TcM900caWbquW2Ocd7oGHlShHXaGAuVl1drUh3fewRqq+/n668dy+N/vaDIv3eh/9YHuGAFcup4saMcj+jdOaKXAYAgCLi2uO49957adWqVSIgzOF/zfTWW39Kjz2WEMvlj9Q6b3XzCl1W4iJtmabG5x+lUt7KfuYsbTpldmFtpDPP8Ir/Mj3XVUYHZbcWdQ3QiDxVZpbQU8+2UAXvEhPndXt8bm7dukVvv/22SNzr+M5//kps5/n013/xB8Kw8HjHjh2dLozHRtq07CxdkIaSbk4T1WyjCrkKAADFwpXhuH37Xrk0F941tXDhf4vl5P/9vvjrGFuM4zjtWXGCzvHguGhlX6XOLaZB4V4Dr0xLaCn7zC7uLVxcSy9M7KEq40wO8Xq8nffee4+uXbsm0rvvXqC7f3qRfv0BGePo72epUSx+4xv1rozHhgfK6MxFwxsaOT9Fmx5YIpYFTj2xLNtGOsxjjWQNRrCdlyXTa3PxfQCAaOPKcLz//n+IkVJm19Trd99Np06dsrqnuMfx3e8mafL0gEi3CxqOuoieaNhGg+fN/v1thmdgpUP0RAn7zPPGeuMbNazS6nTpMXg93s7SpUttnhfvtrJoaGCpjzZu/EP67KFDYhM3Ho5YzAzFmVHhYZ27sZE2iL47jhtPLLN3VdUmj5XbjcEI/LzdlOhNbTc8HL88PwBAFHDdVcV/m2FWkLz75ROf+ITVPcW7plZ+eiWVlxt9/O+//z5rjS+XR7pg3XrafvwijZSspU2m98ERrV5WyYu/Rqu3qo17KFM0TWkB9ZvTNCEX55DpeLNLqEDMobiiu4r9teDeRmOjMC4rV77K7AgzJE5/3sLvn93ZzUsXaXBZKbtDiStPLIt3pXoQ/HjzvNRCT63jKwqBeX4AAB1xbThUeNeUOXrK6p6aJrrxO6OPv3CMCuncJe4ZdBM1mRUWb/WyyqnkUfpyK8mKrIbObNrLvJA19FTrlKjExGf7p+zxADFSi8dSmOHJeLz8nAdKf/c2zXxAdkOZv+Fg3gb19QnDsmfjMcNomPvysog2bJqiPnYv2x9YI7eZOPXEMm27TO0iL+WxlmeRiyA8PwCAjrg2HLyLyuyeMrum+Agq3jXF4V1Zvxz/tkiOYJX4CyJgrWJUSHtFy3cN7bVVVuIDVFp7SG57hV6oXTRn2+Tzh4zzWuc3z2O0ijMd7wVuQC2jwYynBfM2qHGVMBydZ3fIjc4pXb+R6EoZPah6AW48sUvZvKsyWmIaS9M7Ex5ONz2X/uNLN9/n0XMDAIQf14bjS1/6ErW3n6ZvfevvmfHoF3+//vWLrFH9LyLxbUeOHLFSQb/j0BlmNPppVWpEGfM23u4YpnfffZce/7OvufA2JNzwzekCcuGJrXPgnTWdSJ332RZKWueVXVhuvs8Hzw0AEG4wO64Dck1yOPrDD9L999+hH/1ogS128aHJ6+L3HNzb4IzXf4M+e+iz1LCyTxjXw9/8jNgeai51Uvn59TTZlt5N5g7MLgxAtPAU4wAGfLTU2NjXhUEwk8l3/vbfqb+hTxgWbjQ8/aI8cC5Tu+mFCE9kivbUeTMaAIDoAY/DAYW0mMcnpkQXXja08Dh8Ah4HANECHkdAcB0ObhyyJQAA0BV4HA4wW8wvv/yy3AIAAPEFhgMAAIArshoO3soGAAAA0slpOHj3DAAAAKCS13Do3q/Pta4Rm8hNlPMIzz9YkL/xQX3WjgyHrrGPdOOn630ESZTzCM8/WJC/8SH9WWM4rt/wX1tjsr+MGNodxtxWoEjoXj7xfoUCGA4AAACugOFQdSlYCpeKXdoUIFaSLa60axdJ1V33g0zfUaDXYMxInJrh2DHKLLyBIFqx9nu0FBFDQLpao1Yt7lC/X/nI8/45JeTlqxDibTh4od6iKN5NsOWumhAVbnMqeD5lOVFF63F5nfbZcrdb188+R91U7bfxYNi+Y8VV6hQKgPojus+aTij3x1M3PSj3Fxej4tp1Q5VWZql3vdwfckL/fuXD2fuXi3CXr8KJteEYOdxNEzXdUveDw6cbX04TpgSqbC3x1oHa6rO3Fm7RkSfNfaxVbEkQplB1uW2tFUfnd4MxLXrFlQyaGr7BvqNhG5GqtmjLg7R7nNNqS2utWXlgP4eRB/JYMb27okDIki+VD/vup7uuigohVQY4a6iKr7t+/jxlfr4m4jwdUhY5472nPKuRjhYa5Hr86Xo169j1ycWc3y9IO7eb8ukRLd6vOd6AC8824/NTzs/2h7l8eSHGhuMynTvOWhEftfebcOGkCrILEg02raZzDxithYM1cqNkpKOGOslsEe4lOmNqWxjwQl1t6nKL40/QrjSPINf5XSNEl4iSN4Nr1c3cnJJLHF4o1TxIv0dFiKuXGZwsDDbV0E8ajM8NscplsIm/IPJYoVC4nPaYioUs+SG+ZQhYbaPGPOfK/nzy3bszBpvaiZ41jze9OVk+N61NEzlTyf/9fpTPwtDh/WINkznegPuu1MxllxHq8uWN2Mc4EoszPdSr9BOlZaO2GKraTGVCjvFybG8wW4SyNW5xmZ4TLY5aq4VY9UB6az3X+Qtn4g0fmhWZsFpR8p6EHjmr1J9NtYqr6tx7PWoelC4uMxYCxm4As5P1+fh079t7U5WVUT64nruxnrl8SvJ+vz/l0wvhfr+4RDWrWPu9VaTZyq4O5atQYm84MrfMl9PSxXKRkfXlnZ2hZNpnbYj9rBLv4nrc0lUUanv2FlfOyqFA0lt6XuGtInH9W7qJWEG3Wvym7KwK13eXi04JIg/ckq27IOu1+XTvucjpOeb7fp/KpxfC/X4xQ8Q1858leto83uzmcYHTshvG8lUoMTYca+hB5hamt8xnLp5lD0vR486LvfVkQz5k3qIw3GAzFTCyyCmileK8MDtFdedt3USLl1CFXLSQL3TYMbpNUlrqvLXHA5fbjdX8uL73WzR9Qy5mwWil8vIny6cZD8iEo+8vVvnU6P0S8szsON4lerzFt+B9uMuXsV4osfY4hNvHCopl/dO7YfIh4wmD55VglGjxmDh4+X3lMrXzQPKKFnrKcscDRuSBvd9UBEX9vAZRQeSoQApFaKbzPukCA4b57l1WbGarW/TXM6OejlV+2It/4cxVopr1ovxVtfFK5gTtyhawzvv9xS2foX+/Lh21P3f5vHxrdIW8fHkh3l1VvKXBWhnJbN0weWGuLh/FxF4OcfwzRF9OCwDzVsZQ6xR7+VNuqvORDeaIJKNApFxye0VidSNVtNBgTTdNpo/CCRTu7sthwPL+dh3fRgd9vQZjNE7qPlkqoEshE+K3Jb1lyogtlodyX37y3bs5ish4bjwAmmnww3a6KL9bBkItjXdjcIAIiMrzG8ksP/m+P+jymYfQv1/TtpF6/Nkn2fWlYiDO3r9chLt8FQ7mqoo5Uc6j0N8bb0GzypT/ziFVWekD3q+Q42P5Sn/WsQ+OAwAAcAcMBwAAAFfAcABQLORoHh27qYAGBFi+HMU42tvb5Vb9UPvldL6PIIlyHuH5BwvyNz6ozzoW0rH8Zrl6FchOlPMIzz9YkL/xwbHh6O35ityiN3w4Gv9xEP7m/gsAANngdoLjyHCYH9YR3hLi9/Dar38ut4BM3PfhP9L6OeeCl+uo3lsYQP7GA/U5OzYc6j5dUO+Bt6gHRk7LPSCd2qpH6M6dO3ItWqBiCxbkbzxQn3NsRlXNu9F4pZdVxr30mlwNO+imAgA4xR/DwX+h6Nc0BQHBW9SR4M3v0b6qz9NLb8p1n+AemRuGGxfQggWNNCzXI8PUflq7YC3tdzYjNnAL8jcSeDIc1jTBfGI9uS2s5PU4RIX8iDAwPO0bfEvuCAevdclre+xQIDPPuvI4hhupul8uRwTDELKUaKYxuQ34B/I3WngwHLdocZ2cxjiHsltYyOlxcKPBKuSSrtPCwAyM7KOSA/UhMh5v0Z88Lq+t6yG5zV8cexy8xVhNNDTUIDdEgSlKtN4RMZ47kbqvsID8jRoeDMciKg1KUyIAcnkcr32bteI/uY/+xqo7V9Ejn7uXkgf+zYhRSG/km6xRbrX85XqKt+ilXea+z9NLGbrtfjb4eetYW/wj7/kX0kfukYsB4czjmKL9dc1UPtRHm+WWaFBGZfMjOhhTkL9RIzbBcV4RZ2acxr9PlFhir5k/UllFCZqmt5RYwrnWR2i8ymj5f+GTcqPkta56OkL19M/CY/lH9uGX5B4DbjT+4cAS+oLYz49/ib6663v0M7mfk+v8QePE45jaX0fN5UPUFy2rAQBwSWwMRy6Pg1NSslAuqVynNxXPIfG5Q5ZXcl/racVDMYzPg3Wfoo+I9YX0UJ3apTROpw9cZ8d/mu6TW+6rYvsnZ+mncp2T/fzBk9fjmNpPdc3lNASrAUDsgcchmZ3NFM+4l+5RuuMyGxfGm2/SbNpnbYj9RMkD9VY3VG0r90jsHk3W888D+TyOqReP0Rj1UzUPcPIkouPGemPkhlYBAHIBj4NW0apPskp92j6+9WdjI5SkJbTQcWzB7p3YuOce4jaFexT8OlLpn+ihgGMXTsnncZTtHjWCm2YSQc4GGmLLcEIAiBfwOBj3PV5Pie/vSwWj3/weHUzrWsrJPX9Jq8uJzo2MG+s82C08ChNpnMxgewhx+zsOAEB88WA4TD1eloSA/NWUru6TKXH1sJAzxnHPp2jfd+tptlV2Iz12iIh5B/u2O+06WkgPtRnGRxzfQbQrbdgsj1n88+em6atmV5VITn/IN07fNI8RBuk6HXlMrqcF2AvF1e84IscwNdq64MaoOSHX1+4n/FbNK8jfqIG5qoCAGyHeBRVFMJdSsCB/4wHmqgJziLfHAQBwA2IcQIAYBwDAKfA4gAAeBwDAKY5jHLrqCSPG4QzEOEChIH/jgfqcHRmOpuYvyq16wu8BCoC5gQIgKBTkbzxwbTiiAPc4eHcM/ub+CwAA2XBlOHRuTZia4zrfw3wQ5VYjWsTBgvyNB+pzjs3vOFCwcxPllx8VW7Agf+OB+pxjM6pq3hlupEhKqwIAYo8nwzEzUC/6xq3UcVnuAX4ztX+tMUWDmYo5JW0W3ehQXaNLdL52LRANKSV/MdWI1njzOBZ/hoYmpHzsqRaqON5COwduyZ2aISrDVMFeGzY1/WVtlDRnpk32UGV/9bxfY17d6BBcY8HofO06kGhN5e+dIWoYa6Y65K+2eDIcpevWUKlcppK1tGkF0cQb2eYWDzHcaLDKsHwoVbDLmxOhqjjKNm8mS32z7GHaUUk0di0pN8wH+XWji3+NhaPztWtBWVkqfylBH2P5C/TF9xhHxUc1EiKXDHexFnSDKom6mVp7KmmsucuIUUhvhPdeWK1uuZ5iivavNfetpf035GYFe3eIEv9wdP65VH4sIZfmg8J0o+f3Gv1F52sPNVMv0rGxStrxcAEFCoQC/wzHpQHqvLKNGmsXyQ26MEwn++dWEmUP76BKmqQbitPRX72ATm41Wt3pje7hxgQ1U490xw8THePTR6fgRiPBpVfFfn58P1Wn9fPmOr+N4S5qHmugtt0hfvF0uMZs6HztIcZqOCWO0Y7kKCF79cUfwzF7lHY2TdGeU3uoSm7SjfJlmUrxGKm9FZU9Scsr2dynKt8Zxqehbbd0x8tod5ta8w9TV/MYO76V+TIGm7ey/WPXSO0MyX5+Be6dVE9ST7LPOlfo0OEas6HztYccS0UyuYOOJUIYRwSO8W44uNHYcpY2nTpET+jXS2UxqboWFpWkOiKZjQtj6gbzTeyftSH2MzvRnLC6oQxBG7tHk/X8JrxSC3trTYdrzIbO164TZbuJt6vGjr2IkVWa4tFw3KIjz3RToldno7GZjMa/PRA69eIx5m+UU766PIXdO7FRtoydyfAoRIvLSm4qqCnaX8cD+GGu1HS4xmzofO36kUB0XGs8GY6ZgXbqpBZ6ap3coCmbW43hl1YwmrU869K6lnIiR+H0n5QnEN0daoxDGicz2F4AU/vrRAylNcT9JzpcYzZ0vnbtMN+vHQ8rI62ATngwHLfowpmrRFe6qVr3HwEy13k02UOT1bIbKdFMxLyDUcdNzzLafdgwPuL4OqLDadFtHrNI9kxStdlVJdLcH9FlZopePDbGLQ8l1OPzDbvylXy60WG4xkLR+do1Qf0BIP8dUMOQi/cLhA3MVQUEUZ5vCHMpBQvyNx6oz9mfUVUAAABiAwwHAAAAV8BwAAAAcIXjGIfumuPog80NYhygUJC/8UB9zo4MRxQ0x1GwcwPDAQoF+RsPXBsOAAAAwJXh0Lk1Ac1xZ0S51YgWcbAgf+OB+pzxOw4giPLLj4otWJC/8UB9zhhVFRTQHAcARBRvhuNSp11z/MmjNCN3AZ8Jk2Yzn4sLmuPADdAcjxTeDMfi2pTm+EQ3bb/STU9DczwYQqDZDM1xUDDQHI8U3gxHyaKU5jiV0NIVclE3uNEIueZ48TWboTkOPADN8UjhX4xjdpTOXFlOm9brJh3LWtK6aY4XRbMZmuPAJ6A5rj2eDcfMQL0R39BWBVAfzXGtNJuhOQ7S0Kr8gpx4NhyltYeMGMepjXRmy2raqWmMQwfNcW00m7kHBc1xkAY0x6ODf11VJY9SYw3RxJlRLUdWaaE5bhJmzWYRL4LmOMgBNMe1xz/DwVj80eVySSd00Ry3E07NZmiOA2dAc1xvfAyOH6Wnu65Sxaa1ykgrPdBBc9yGeX0h02yG5jhwREjLL3CON8Oh/gBwSzdN1HTTC7X6jaoKv+Y4o+iazdAcBx6A5nikwFxVQBDl+YYwl1KwIH/jgfqcfY1xAAAAiD4wHAAAAFwBwwEAAMAVjmMc0ByPNohxgEJB/sYD9Tk7MhzQHI8+MBygUJC/8cC14QAAAABcGQ6dWxPQHHdGlFuNaBEHC/I3HqjPGb/jAIIov/yo2IIF+RsP1OeMUVVBAc1xAEBE8c1wjHTwqUc6aUSug+AwxJ6KaJT4XFzQHAdugOZ4pPDHcFzqpF3H5bKuiMowVbBDqxXAXkDb/InzCDTHQcFAczxSeDccs0dpZxPRwd5tcoOGcKMRds1xDr/OaqKhLJrfwQLNceABaI5HCo+G4xYdeaabEr17qEpu0RE9NMdNvYhiKdNBcxz4BDTHtceT4ZgZaKfOZd20d53coCV6aI4LvYhy1biFHGiOgzSgOR4dCjccQripjA62rZEb9CbUmuPMI6njRkcXqyG61KA5DuxAczw6FGw4Zi6epQk6QbtMIaemE2yrsd5+yfiMToRZc9yQsWUeiu1YYz10g394xQvNcZALaI5rT8GGo7T2EE1OvJJKIji+jQ6yZb26rsKvOW611Mwk+rEaRLdXuJwQaI4DZ0BzXG88BsejgXaa4yEFmuPAEeb7Bc1xbYHh4OigOV50oDkOPADN8UiBuaqAIMrzDWEupWBB/sYD9TnD4wAAAOAKGA4AAACugOEAAADgCscxDmiORxvEOEChIH/jgfqcHRkOaI5HHxgOUCjI33jg2nAAAAAArgyHzq0JaI47I8qtRrSIgwX5Gw/U54zfcQBBlF9+VGzBgvyNB+pzxqiqoIDmOAAgongzHFz9z5wdV6adA7fkTuArfP4rdToMluZ7WmonutyqEJVWU3ZAE3t+EOVYp6l2QCa8exwrWmhImSX3hdpFcodmpFXModQKqDSFoow073P95NHl5kajmobk9SWpZ7JaH+MBTexAyatXD7QCXVUcbjR00BwvMrl1uaWY1VZzelkpZtV/Uo/uOmhiB0h+vXqgFzAcDD00x8NJfl1uu1iVFkAT22cK06sH4cW74bjSTdUivlFPR2blNq3QQ3NcYE37HYI+4jm63EYrvb/DvCdmSDuM6dezClyFDGhiA+AMb4aj5FF6QcY2hlqJOrfoKRvLCbXmOIdrhkijk+whoYVRNI+Ee0hzdLnZPY8asQHDuNURtfUw45tDUjdkQBMbAGf41lVVWruX9qwgGjx/WW7RizBrjqdTtvsw9aiKg/OJiAdla5Fvpj5p3IQsLl3TMxAKTWwAcoIYhynrGmLN8fDgTpd7WPQB7iAdQwXQxAYgO/4ZjksD1HmFaPsDa+QGfdBOc1zEF9QRTPODK13u4UbiWZDqvtMI8/lDExuAjHgzHJc6Uz/+azpB23tfob3r5D6d0EFzXP2BGquRG4bSYiCBk1+X2zZqrCjX6AFoYgdIPr16oBuYqwoIojzfEOZSChbkbzxQnzNiHAAAAFwBwwEAAMAVMBwAAABc4TjGAc3xaIMYBygU5G88UJ+zI8MBzfHoA8MBCgX5Gw9cGw4AAADAleHQuTUBzXFnRLnViBZxsCB/44H6nPE7DiCI8suPii1YkL/xQH3OGFUVFNAcBwBEFO+GI113vEPP2XG1gM+BZU7dwNN8z6vuVJdbXKe+utKGmBaMfiBoXjaAgTfDwY3Glm6i1uOW5vhkm36THArSKuXQaTHw65NzaFmz6873RFB5dLkjoSstJ2cE/gLN8WjhyXCMHO6miRUt9OXaRXKLpshKOcya40LetrKHDhdz4r2cutwR0JXm5aCaaAi62D4DzfGo4cFwXKZzx4kqNq2lUrlFV8KvOS7lbcM0zfccXW7ddaVNrRFV1RD4AzTHo4bnGEdi8Sy1WzEOHXXH9dEcL1+WTE1PXaR+4qjqcgutkXK18QAAyEbhhmN2RuhlDzZdpAdtuuOdNGJ8QitCrTkupWf7q0/SVml4DN3x+Q/gRlKXm3l8ddyow2oA4IjCDUdJKfE2ekVrLVUZW6h0/UaqYG3oae28jpBrjlvSsynDk8kjmlcipMttyAQzD9D2bIz1+R64BoAOeOiqKqGlK+SiiTQmeqGD5nh6IJohz1lMoqLLbXlRZhL9hA2iWxFOCABz8WA4FtETDdtooqvdimvMDPwrDa7YSBtKjHVdCL/muNH1NdZcZ8U1pvZ3UH/lDrJi0/ONmUfQ5QYgdngLjq/bI+MaRnC8uquMDj7/qH6jrHTQHN/cJ+MaxrEi0D5qxlTmiZy63NCVBtlA2YgamKsKCKI83xDmUgoW5G88UJ+zN48DAABA7IDhAAAA4AoYDgAAAK5wHOOA5ni0QYwDFAryNx6oz9mR4YDmePSB4QCFgvyNB64NBwAAAODKcOjcmoDmuDOi3GpEizhYkL/xQH3O+B0HEET55UfFFizI33igPmeMqgoKaI4DACKKB8NxWdHhUJOOmhxhR5mywZaKp92cTZdbFaKad010L0gxLTV/IzFlfNgQ+QzNcd3xYDjW0F5TZ9zS41hOVPMZekKzSQ4FaRVHuCqNzdSnzt7KUrKnkguAFEdIKYsuNzca1TQkrzFJPZPVehmPSlOIy0jO5yoD+bAaFNAcjwQ+dlVdpuf4JIdta+S6RnCjEXLNcTtcGKpIwkM8rzLqcksxq62WYoghZtV/Et11sQea41HDN8PBp1Sn3j2WqJNOhF9z3A6fUp2Koo1diC53EcWmQEiA5njU8MdwzB6lp89spKfWyXWt0EdzXMCMTN2xHdRaFGcjly63ITbV32HeEzMyHcYU2lkFrsLGWDMlhNFGHzwAufDFcIwc7qZEg4Y6HAqh1hxX4N5RufU98wg3WDm7x9g9jw5Rg1X51hG19TDjm0NSN0xwTRZp1E3dE53CMwDMJ94NB/M2+m60aOptpAi15rgJq7w7JnuK42040uVWg/ijtJuuaRkILdt9mPjYA0vREQBgw7Ph0N/b0EFz3KBo3gajEF3uYdEHWER5WwBAIHgzHNzbOL6NHtTc2wi/5jiDexv9DWQNWgo7cshuqvtOI4a7iD1+ZYQYAEDFm+G4OU0TNeu1HEllQwfN8eQ1GmvY6syQFQnbqDFmNRqGsnsjoUPVU9ft2kMPNMejBuaqAoIozzeEuZSCBfkbD9Tn7MuoKgAAAPEBhgMAAIArYDgAAAC4wnGMA5rj0QYxDlAoyN94oD5nR4YDmuPRB4YDFAryNx64NhwAAADijdo4yGs4Xn75ZbkFAAAAQHAcAACAK4j+H5z0eI5KdirQAAAAAElFTkSuQmCC


--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Talvin Singh
Talvin Singh
SSC Eights!
SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)

Group: General Forum Members
Points: 918 Visits: 254
that works very well. Thought it would be a more complicated code!
and correct on the new column, i needed that too!

Expanding the code, looking at the new column [OrdersInGroup], a max of 5 orders can only fit on a shelf space, and there are a max of 6 locations. If the current result is PickGroup 1:



a new wave of orders come through and have similar Points:

SELECT *
INTO #TestTable
FROM (VALUES
('Order8', 4)
,('Order9',4)
,('Order10', 8)
,('Order11',12)
,('Order12',12)
,('Order13',19)
,('Order14',24)
,('Order15',4)
,('Order16',4)
,('Order17',10)
)v(OrderID,Point);


i need to check if there is "free space" on the shelf, order8 would go into groupCode 1 as there now 2 orders therefore can be in PickGroup 1, order10 is a new Point therefore will go into next open shelf location groupCode 6, order11 and 12 drop in and go into groupCode 2, but now reached max 5. therefore that location is now free, the next order13 can now occupy it, therefore it will now wait for other orders for Point 19.
there are no shelf locations (groupCode) open as all are occupied. order14 must therefore be missed, PickGroup 0, orders 15 and 16 go into groupCode 1 and is now full therefore 1 becomes free for next order to be part of PickGroup 2....and so on.



missed orders (pickGroup = 0) will get allocated when no new sets of orders drop in using same method above.

maybe first x orders sets the Points until all 6 GroupCodes have been allocated, then from there iterate through each incoming order?

OrdersInGroup could be a incrementing value, rather than a count?

i'm applying this method on a small dataset of 50 orders, but a typical dataset could hold 10k orders, therefore i would benefit from fast processing...
Many thanks for your help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search