May 7, 2024 at 8:24 pm
I have table TicketNumbers
i TicketNumber UID
2 10 09901a22c7c3acc6786847c775f1d113
6 5 00dad28bef21f916240d6e8c1c1bd67d
12 20 00dad28bef21f916240d6e8c1c1bd67d
I need to produced 35 rows (UID also must be present in result) for the same rules:
10 sequence number started from 1 (row i=2)
than next 5 sequence number (row i=6)
than next 20 sequence number (row i=12)
How SQL query will look in this case?
May 7, 2024 at 9:45 pm
That is not a lot to go on. Can you provide sample output and what you have tried? What you provided doesn't tell me enough to give you anything meaningful.
What I mean is you say you need "10 sequence numbers starting from 1 (row i=2)", but is that just the numbers 1 through 10 that you want?
If I had to guess, I would say you want your results (using i=6 as the result set is smaller - 5 rows) to be something like:
i Ticket Number UID Sequence
6 5 00dad28bef21f916240d6e8c1c1bd67d 1
6 5 00dad28bef21f916240d6e8c1c1bd67d 2
6 5 00dad28bef21f916240d6e8c1c1bd67d 3
6 5 00dad28bef21f916240d6e8c1c1bd67d 4
6 5 00dad28bef21f916240d6e8c1c1bd67d 5
Is that what you are looking for (or similar) or something different? If it is what you want, then a recursive CTE is probably going to be the easiest way to write it and support it long term, but I suspect that someone on the forum will have a more efficient way to do this. A loop is another way to do this, but that would be inefficient and would be my "last resort" approach as loops are horrid for performance and do not scale.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 7, 2024 at 11:40 pm
I have table TicketNumbers
i TicketNumber UID
2 10 09901a22c7c3acc6786847c775f1d113
6 5 00dad28bef21f916240d6e8c1c1bd67d
12 20 00dad28bef21f916240d6e8c1c1bd67d
I need to produced 35 rows (UID also must be present in result) for the same rules:
10 sequence number started from 1 (row i=2) than next 5 sequence number (row i=6) than next 20 sequence number (row i=12)
How SQL query will look in this case?
Please see the article at the first link in my signature line below for the many reasons to post test data in a "Readily Consumable" fashion and other things that will prevent us from having to ask questions instead of writing code.
Here's one way of doing that for this thread:
--===== This is one way to create test data to help others help you.
DROP TABLE IF EXISTS #TestTable;
GO
SELECT *
INTO #TestTable
FROM (VALUES
( 2,10,'09901a22c7c3acc6786847c775f1d113')
,( 6, 5,'00dad28bef21f916240d6e8c1c1bd67d')
,(12,20,'00dad28bef21f916240d6e8c1c1bd67d')
)v1(i, TicketNumber, UID)
;
Since you posted in a 2022 forum, here's one of the easiest and most effective ways of doing what you asked used a method known as "Relational Multiplication".
--===== Solution using "Relational Multiplication"
SELECT tt. *
,SeqNum = ROW_NUMBER() OVER (ORDER BY i)
FROM #TestTable tt
CROSS APPLY GENERATE_SERIES(1,tt.TicketNumber)
ORDER BY i, SeqNum
;
Here are the results...
i TicketNumber UID SeqNum
----------- ------------ -------------------------------- --------------------
2 10 09901a22c7c3acc6786847c775f1d113 1
2 10 09901a22c7c3acc6786847c775f1d113 2
2 10 09901a22c7c3acc6786847c775f1d113 3
2 10 09901a22c7c3acc6786847c775f1d113 4
2 10 09901a22c7c3acc6786847c775f1d113 5
2 10 09901a22c7c3acc6786847c775f1d113 6
2 10 09901a22c7c3acc6786847c775f1d113 7
2 10 09901a22c7c3acc6786847c775f1d113 8
2 10 09901a22c7c3acc6786847c775f1d113 9
2 10 09901a22c7c3acc6786847c775f1d113 10
6 5 00dad28bef21f916240d6e8c1c1bd67d 11
6 5 00dad28bef21f916240d6e8c1c1bd67d 12
6 5 00dad28bef21f916240d6e8c1c1bd67d 13
6 5 00dad28bef21f916240d6e8c1c1bd67d 14
6 5 00dad28bef21f916240d6e8c1c1bd67d 15
12 20 00dad28bef21f916240d6e8c1c1bd67d 16
12 20 00dad28bef21f916240d6e8c1c1bd67d 17
12 20 00dad28bef21f916240d6e8c1c1bd67d 18
12 20 00dad28bef21f916240d6e8c1c1bd67d 19
12 20 00dad28bef21f916240d6e8c1c1bd67d 20
12 20 00dad28bef21f916240d6e8c1c1bd67d 21
12 20 00dad28bef21f916240d6e8c1c1bd67d 22
12 20 00dad28bef21f916240d6e8c1c1bd67d 23
12 20 00dad28bef21f916240d6e8c1c1bd67d 24
12 20 00dad28bef21f916240d6e8c1c1bd67d 25
12 20 00dad28bef21f916240d6e8c1c1bd67d 26
12 20 00dad28bef21f916240d6e8c1c1bd67d 27
12 20 00dad28bef21f916240d6e8c1c1bd67d 28
12 20 00dad28bef21f916240d6e8c1c1bd67d 29
12 20 00dad28bef21f916240d6e8c1c1bd67d 30
12 20 00dad28bef21f916240d6e8c1c1bd67d 31
12 20 00dad28bef21f916240d6e8c1c1bd67d 32
12 20 00dad28bef21f916240d6e8c1c1bd67d 33
12 20 00dad28bef21f916240d6e8c1c1bd67d 34
12 20 00dad28bef21f916240d6e8c1c1bd67d 35
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2024 at 11:56 pm
p.s. If you need the Sequence Number to restart for each ticket, then just add a PARTITION BY, as follows...
--===== Solution using "Relational Multiplication"
SELECT tt. *
,SeqNum = ROW_NUMBER() OVER (PARTITION BY i ORDER BY i)
FROM #TestTable tt
CROSS APPLY GENERATE_SERIES(1,tt.TicketNumber)
ORDER BY i, SeqNum
;
Here's the output for that...
i TicketNumber UID SeqNum
----------- ------------ -------------------------------- --------------------
2 10 09901a22c7c3acc6786847c775f1d113 1
2 10 09901a22c7c3acc6786847c775f1d113 2
2 10 09901a22c7c3acc6786847c775f1d113 3
2 10 09901a22c7c3acc6786847c775f1d113 4
2 10 09901a22c7c3acc6786847c775f1d113 5
2 10 09901a22c7c3acc6786847c775f1d113 6
2 10 09901a22c7c3acc6786847c775f1d113 7
2 10 09901a22c7c3acc6786847c775f1d113 8
2 10 09901a22c7c3acc6786847c775f1d113 9
2 10 09901a22c7c3acc6786847c775f1d113 10
6 5 00dad28bef21f916240d6e8c1c1bd67d 1
6 5 00dad28bef21f916240d6e8c1c1bd67d 2
6 5 00dad28bef21f916240d6e8c1c1bd67d 3
6 5 00dad28bef21f916240d6e8c1c1bd67d 4
6 5 00dad28bef21f916240d6e8c1c1bd67d 5
12 20 00dad28bef21f916240d6e8c1c1bd67d 1
12 20 00dad28bef21f916240d6e8c1c1bd67d 2
12 20 00dad28bef21f916240d6e8c1c1bd67d 3
12 20 00dad28bef21f916240d6e8c1c1bd67d 4
12 20 00dad28bef21f916240d6e8c1c1bd67d 5
12 20 00dad28bef21f916240d6e8c1c1bd67d 6
12 20 00dad28bef21f916240d6e8c1c1bd67d 7
12 20 00dad28bef21f916240d6e8c1c1bd67d 8
12 20 00dad28bef21f916240d6e8c1c1bd67d 9
12 20 00dad28bef21f916240d6e8c1c1bd67d 10
12 20 00dad28bef21f916240d6e8c1c1bd67d 11
12 20 00dad28bef21f916240d6e8c1c1bd67d 12
12 20 00dad28bef21f916240d6e8c1c1bd67d 13
12 20 00dad28bef21f916240d6e8c1c1bd67d 14
12 20 00dad28bef21f916240d6e8c1c1bd67d 15
12 20 00dad28bef21f916240d6e8c1c1bd67d 16
12 20 00dad28bef21f916240d6e8c1c1bd67d 17
12 20 00dad28bef21f916240d6e8c1c1bd67d 18
12 20 00dad28bef21f916240d6e8c1c1bd67d 19
12 20 00dad28bef21f916240d6e8c1c1bd67d 20
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2024 at 11:59 pm
Last but not least... your the one that will need to " 'splain'n'maintain" this... do you understand how it all works?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply