July 12, 2016 at 7:47 am
Hi
I had accountno as field for each account no there is number of transactions happening
AccountNo sequence
100001 1
2
3
4
5
.
.
.
100002 1
2
3
.
.
For each account no there is new sequence created
What i want whenever a new account no comes its transaction seq no starts from 1 for existing its sequence no increment from the previous one
thanks
July 12, 2016 at 8:19 am
So what's the problem? What is impeding you to do what you need?
July 12, 2016 at 8:24 am
I don't know of an "automated" way to do this - i.e. a system or generated thing that will handle this for you. Number 1 is easy - you simply code your first insert to hard-code the value of 1. From there new inserts for the same acctno will need to get the prior value and increment by one in some form of code object. Do be sure to keep concurrency in mind if multiple threads can insert rows for the same acctno or you will eventually get duplicates.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 12, 2016 at 8:56 am
You could use row_number to build a list that increments from the last item.
I'm not sure where you need this, or when, so it's hard to do code, but if you needed to add xx transactions, I'd build a list that was xx long, starting at the last transaction number + 1
July 12, 2016 at 9:03 am
SQL006 (7/12/2016)
HiI had accountno as field for each account no there is number of transactions happening
AccountNo sequence
100001 1
2
3
4
5
.
.
.
100002 1
2
3
.
.
For each account no there is new sequence created
What i want whenever a new account no comes its transaction seq no starts from 1 for existing its sequence no increment from the previous one
thanks
This is a really bad idea to do in a database. Could it be done? Yes but without any guarantees and only with a great amount of work and a huge increase in the potential for errors and deadlocks.
99% of the time, people want this done for purposes of sortability and display purposes. Keep the presentation layer out of the database. It doesn't belong there especially since it will have the same problems with rollbacks that occur with all such sequences.
You should have some sort of DATETIME column that tracks WHEN the transaction was made. If you need a tie-breaker, then add an IDENTITY column to the mix and use that as a secondary sort column.
If you need to display the sequence as you have it, then use the ROW_NUMBER() function partitioned by account number and ordered by entry datetime and IDENTITY to create the sequence only at display time.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 12, 2016 at 10:34 am
Note that you only need the ROW_NUMBER() stuff if you are inserting multiple rows in one batch. If you are like almost all of the clients I come across and this type of thing is done Row By Agonizing Row then you just need a max (+1) of the number for the acctno being inserted.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 12, 2016 at 10:49 am
Jeff beat me to it. Here's some DDL that shows how to implement what Jeff suggested.
USE tempdb -- a safe place to test this out...
GO
-- (0) Your table should look something like this
IF OBJECT_ID('tempdb.dbo.transactions') IS NOT NULL DROP TABLE dbo.transactions;
CREATE TABLE dbo.transactions
(
transactionID int identity NOT NULL,
accountNo int NOT NULL,
transDatetime datetime NOT NULL
);
-- (1) Create a clustered index that supports:
-- ROW_NUMBER() OVER (PARTITION BY accountNo ORDER BY transDatetime):
ALTER TABLE dbo.transactions
ADD CONSTRAINT pk_transactions UNIQUE CLUSTERED (accountNo, transDatetime, transactionID);
-- (2) Generate sample data
INSERT dbo.transactions (accountNo, transDatetime)
SELECT * FROM
(
SELECT TOP(20)
AccountNo = ABS(CHECKSUM(newid())%3)+1001,
transDatetime = DATEADD(MINUTE,CHECKSUM(newid())%1000,getdate())
FROM sys.all_columns
) transactions;
-- (3) Create a view that looks like this:
SELECT
accountNo,
[Sequence] = ROW_NUMBER() OVER (PARTITION BY accountNo ORDER BY transDatetime)
FROM dbo.transactions;
-- Itzik Ben-Gan 2001
July 12, 2016 at 11:32 am
Jeff: I would almost never want to pay the cost to do a SORT/ROW_NUMBER() in SQL Server to present that data. That is cost that just keeps on hitting you every time you throw out the data.
Alan: your PK breaks inserts of >1 row with the same datetime value, which is going to happen both with multi-row batch inserts as well as concurrent access (due to the 3.33ms specificity of datetime).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 12, 2016 at 11:54 am
TheSQLGuru (7/12/2016)
Jeff: I would almost never want to pay the cost to do a SORT/ROW_NUMBER() in SQL Server to present that data. That is cost that just keeps on hitting you every time you throw out the data.
It's a relatively low cost compared to the nightmare of storing the actual data in this case.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 12, 2016 at 11:56 am
TheSQLGuru (7/12/2016)
Note that you only need the ROW_NUMBER() stuff if you are inserting multiple rows in one batch. If you are like almost all of the clients I come across and this type of thing is done Row By Agonizing Row then you just need a max (+1) of the number for the acctno being inserted.
Great way to get deadlocks and incorrect/sometimes duplicate numbers. This is a "NextID" solution that's fraught with errors, deadlocks and, as your very post indicates, complexity because you must be able to accommodate both bulk and singleton inserts in a manner that would allow both types to operate concurrently by many sessions whether instigated by GUI hits or multiple batch runs.
The Sequence number should not be stored in the database for this.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 12, 2016 at 12:08 pm
TheSQLGuru (7/12/2016)
Alan: your PK breaks inserts of >1 row with the same datetime value, which is going to happen both with multi-row batch inserts as well as concurrent access (due to the 3.33ms specificity of datetime).
This would be true if I did not include an identity column as a tie-breaker 😉
-- Itzik Ben-Gan 2001
July 12, 2016 at 12:30 pm
TheSQLGuru (7/12/2016)
Note that you only need the ROW_NUMBER() stuff if you are inserting multiple rows in one batch. If you are like almost all of the clients I come across and this type of thing is done Row By Agonizing Row then you just need a max (+1) of the number for the acctno being inserted.
The OP hasn't specified with any certainty that he wishes this to be implemented as inserts...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
July 12, 2016 at 12:38 pm
I'm pretty confident that what I posted would do the trick provided the OP has or can add a datetime column.
-- Itzik Ben-Gan 2001
July 12, 2016 at 12:59 pm
Alan.B (7/12/2016)
TheSQLGuru (7/12/2016)
Alan: your PK breaks inserts of >1 row with the same datetime value, which is going to happen both with multi-row batch inserts as well as concurrent access (due to the 3.33ms specificity of datetime).This would be true if I did not include an identity column as a tie-breaker 😉
A scrolling we will go, a scrolling we will go ... hi-ho the derry oh a scrolling we will go! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply