May 19, 2016 at 3:23 pm
Hi
I need something like this pseudo code;
UPDATE Orders
SET InvoiceNo = MAX(InvoiceNo)
FROM Orders INNER JOIN SetupAccountTypes ON Orders.AccountTypeID = SetupAccountTypes.ID
WHERE (SetupAccountTypes.AccountType = N'COD') AND (Orders.DeliveryDate >= CONVERT(DATETIME, '2016-01-01 00:00:00', 102)) AND (Orders.DeliveryDate <= CONVERT(DATETIME, '2016-01-31 00:00:00', 102))
That is, a bunch of rows selected under the given WHERE criteria get assigned invoice numbers incrementally.
How do I achieve this please in T-SQL or otherwise?
Thanks
Regards
May 19, 2016 at 3:56 pm
You cannot use an aggregate in the main query of an UPDATE statement. You'll need to use a CTE, derived table, or subquery.
ROW_NUMBER() will give you a unique sequence of sequential numbers. DENSE_RANK() will give you sequential numbers, but they aren't guaranteed to be unique. You solution will probably need to include ROW_NUMBER().
You aren't excluding records that already have an invoice number, so you will be overwriting any existing invoice numbers.
If you want more, you'll need to provide to provide sample data and expected results as outlined in the first link in my signature.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 19, 2016 at 4:41 pm
Hi Drew
DDL and DML code below.
Your first link end up at the MSDN Order By page; https://msdn.microsoft.com/en-us/library/ms188385.aspx?f=255&MSPPError=-2147217396
Regards
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DeliveryDate] [datetime] NULL,
[InvoiceNo] [int] NULL,
[AccountTypeID] [int] NULL,
CONSTRAINT [PK_Orders_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SetupAccountTypes] Script Date: 19/05/2016 23:39:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SetupAccountTypes](
[ID] [int] IDENTITY(1,1) NOT NULL,
[AccountType] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_SetupAccountTypes] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Orders] ON
GO
INSERT [dbo].[Orders] ([ID], [DeliveryDate], [InvoiceNo], [AccountTypeID]) VALUES (1, CAST(N'2016-01-01 00:00:00.000' AS DateTime), NULL, 1)
GO
INSERT [dbo].[Orders] ([ID], [DeliveryDate], [InvoiceNo], [AccountTypeID]) VALUES (2, CAST(N'2016-01-15 00:00:00.000' AS DateTime), NULL, 1)
GO
INSERT [dbo].[Orders] ([ID], [DeliveryDate], [InvoiceNo], [AccountTypeID]) VALUES (3, CAST(N'2016-02-02 00:00:00.000' AS DateTime), NULL, 1)
GO
SET IDENTITY_INSERT [dbo].[Orders] OFF
GO
SET IDENTITY_INSERT [dbo].[SetupAccountTypes] ON
GO
INSERT [dbo].[SetupAccountTypes] ([ID], [AccountType]) VALUES (1, N'COD')
GO
INSERT [dbo].[SetupAccountTypes] ([ID], [AccountType]) VALUES (2, N'Accounts')
GO
INSERT [dbo].[SetupAccountTypes] ([ID], [AccountType]) VALUES (3, N'Credit Card')
GO
SET IDENTITY_INSERT [dbo].[SetupAccountTypes] OFF
GO
ALTER TABLE [dbo].[Orders] WITH CHECK ADD CONSTRAINT [FK_Orders_SetupAccountTypes] FOREIGN KEY([AccountTypeID])
REFERENCES [dbo].[SetupAccountTypes] ([ID])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_SetupAccountTypes]
GO
May 19, 2016 at 5:02 pm
bit confused...are you saying that initially every row in Orders table has the InvoiceNo as 'NULL' and then based on a specifc WHERE clause you want an incrementing number?
eg
WHERE (SetupAccountTypes.AccountType = N'COD') AND (Orders.DeliveryDate >= CONVERT(DATETIME, '2016-01-01 00:00:00', 102)) AND (Orders.DeliveryDate <= CONVERT(DATETIME, '2016-01-31 00:00:00', 102))
what do you want to happen next time you run this.....I assume you would want to increment based on the MAX invoice number previously allocated?
what about other scenarios where the AccountType is not 'COD'
maybe you could expand your sample data to cover all possibilities and also please, provide expected results.
thanks
edit typo
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 19, 2016 at 5:49 pm
Hi
First time they would all be null then we start with Invoice No = 1 for first row that falls under the criteria when the query is run and then go from there.
If I can have example of thus far then I can give it a go at expanding for other cases.
Regards
May 19, 2016 at 6:04 pm
yahya-236450 (5/19/2016)
HiFirst time they would all be null then we start with Invoice No = 1 for first row that falls under the criteria when the query is run and then go from there.
If I can have example of thus far then I can give it a go at expanding for other cases.
Regards
as Drew suggested...have you explored using ROW_NUMBER?
once again...maybe you could expand your sample data to cover all possibilities and also please, provide expected results.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 19, 2016 at 8:21 pm
I strongly recommend you start with an actual value and not NULL, and make the column NOT NULL by definition. Otherwise you have the NULL bit dereference and MUCH worse you take a value from NULL to NOT NULL, which increases the size and opens you up to page splits for those updates that make a row too big to fix on a page (or a forwarding pointer if the table is a HEAP).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 20, 2016 at 2:32 am
Hi Kevin
Invoice numbers are assigned when the above query is run on order delivery etc. Before that invoice number has no value. We can start with zero value fro invoice no but it doesn't look nearly as nice on user screen as no (blank) value.
Thanks
Regards
May 20, 2016 at 2:33 am
J Livingston SQL (5/19/2016)
bit confused...are you saying that initially every row in Orders table has the InvoiceNo as 'NULL' and then based on a specifc WHERE clause you want an incrementing number?eg
WHERE (SetupAccountTypes.AccountType = N'COD') AND (Orders.DeliveryDate >= CONVERT(DATETIME, '2016-01-01 00:00:00', 102)) AND (Orders.DeliveryDate <= CONVERT(DATETIME, '2016-01-31 00:00:00', 102))what do you want to happen next time you run this.....I assume you would want to increment based on the MAX invoice number previously allocated?
Correct.
what about other scenarios where the AccountType is not 'COD'
edit typo
For that I can run a very similar query updating delivery note numbers instead.
May 20, 2016 at 2:37 am
J Livingston SQL (5/19/2016)
yahya-236450 (5/19/2016)
HiFirst time they would all be null then we start with Invoice No = 1 for first row that falls under the criteria when the query is run and then go from there.
If I can have example of thus far then I can give it a go at expanding for other cases.
Regards
as Drew suggested...have you explored using ROW_NUMBER?
once again...maybe you could expand your sample data to cover all possibilities and also please, provide expected results.
If for instance query based on the given WHERE clause returns below rows
Order ID InvoiceNo
234 NULL
256 NULL
and the Max(Orders.InvoiceNo) at present is 1000 then I need the above two rows to be assigned Invoice Nos 1001 and 1002.
Thanks
May 20, 2016 at 5:54 am
DECLARE @InvoiceNo int = ISNULL((SELECT MAX(o.InvoiceNo) FROM Orders o),0);
WITH cte (ID,InvoiceNo)
AS (
SELECTo.ID,@InvoiceNo+ROW_NUMBER() OVER (ORDER BY o.ID ASC)
FROMOrders o
JOIN SetupAccountTypes a
ON a.ID = o.AccountTypeID
AND a.AccountType = 'COD'
AND o.DeliveryDate >= '20160101'
AND o.DeliveryDate <= '20160131'
)
UPDATE o
SET o.InvoiceNo = cte.InvoiceNo
FROMcte
JOIN Orders o ON o.ID = cte.ID;
Far away is close at hand in the images of elsewhere.
Anon.
May 20, 2016 at 6:02 am
David Burrows (5/20/2016)
DECLARE @InvoiceNo int = ISNULL((SELECT MAX(o.InvoiceNo) FROM Orders o),0);
WITH cte (ID,InvoiceNo)
AS (
SELECTo.ID,@InvoiceNo+ROW_NUMBER() OVER (ORDER BY o.ID ASC)
FROMOrders o
JOIN SetupAccountTypes a
ON a.ID = o.AccountTypeID
AND a.AccountType = 'COD'
AND o.DeliveryDate >= '20160101'
AND o.DeliveryDate <= '20160131'
)
UPDATE o
SET o.InvoiceNo = cte.InvoiceNo
FROMcte
JOIN Orders o ON o.ID = cte.ID;
OP, please note that solutions like this suffer from concurrency flaws that allow for bad data. Without proper locking and transaction control between the first and second statements here (explicitly or via isolation level) you can easily have multiple threads get the same max and thus create duplicate InvoiceNo's (or fail if you have uniqueness enforced). Other ways of doing this query suffer from that too. It is a classic data processing problem. I have lost track of the number of clients I have encountered over the years with this problem, many of whom had to set up processes to deal with failures that happened.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 20, 2016 at 6:07 am
TheSQLGuru (5/20/2016)
....suffer from concurrency flaws....
Nice catch Kevin 🙂
Far away is close at hand in the images of elsewhere.
Anon.
May 20, 2016 at 6:50 am
This should fix the concurrency problems, and a few pet peeves I have.
WITH cte AS (
SELECT o.*,
ISNULL((SELECT MAX(o.InvoiceNo) FROM Orders o),0) + ROW_NUMBER() OVER (ORDER BY o.ID ASC) NewInvoiceNo
FROMOrders o
WHERE o.DeliveryDate >= '20160101'
AND o.DeliveryDate <= '20160131'
AND EXISTS( SELECT 1
FROM SetupAccountTypes a
WHERE a.ID = o.AccountTypeID
AND a.AccountType = 'COD')
)
UPDATE cte
SET InvoiceNo = cte.NewInvoiceNo;
May 20, 2016 at 8:34 am
Luis Cazares (5/20/2016)
This should fix the concurrency problems, and a few pet peeves I have.
WITH cte AS (
SELECT o.*,
ISNULL((SELECT MAX(o.InvoiceNo) FROM Orders o),0) + ROW_NUMBER() OVER (ORDER BY o.ID ASC) NewInvoiceNo
FROMOrders o
WHERE o.DeliveryDate >= '20160101'
AND o.DeliveryDate <= '20160131'
AND EXISTS( SELECT 1
FROM SetupAccountTypes a
WHERE a.ID = o.AccountTypeID
AND a.AccountType = 'COD')
)
UPDATE cte
SET InvoiceNo = cte.NewInvoiceNo;
Having a brain cloud at the moment but can't recall if this syntax will lead to that MAX getting iterated or just run once for all affected rows.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply