single update statement for this problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

single update statement for this problem

Author

Topic

fan2005
Yak Posting Veteran

85 Posts

Posted - 2014-03-10 : 11:54:23

Hi,
I have a table like below which is for customers and their accounts. Accounts for each customer is sorted based on their priority column , balance and some other factors via clustered index.
I need to deduct from customer's accounts (CIFNO)20$ according to their total balance ,for each of their accounts based on the current order.

the output should be a table like this: that it has deducted each account the max value below 20$ and if it does not meet 20$ goes through next accounts until it meets 20$ or if the totsl sum of all accounts balance is below the 20$ it deducts as much as possible.

Thank you


BALANCE	branch	TBTYPE	CIFNO	SERIAL	priority	Amount
7	101	800	1	3	1	7
5	101	800	1	1	1	5
5	101	800	1	2	1	5
2	101	12	1	3	2	2
100	101	700	1	3	3	1
8	101	800	2	3	1	8
5	101	800	2	1	1	5
2	101	813	2	2	1	2
3	101	12	2	3	2	3
3	101	700	2	3	3	2
10	101	800	3	3	1	10
5	101	800	3	1	1	5
4	101	12	3	3	2	4
20	101	700	3	3	3	1
10	101	700	3	3	3	0

I need a fast solution with single update statement ,
since there are a lot of records in the table.

I tryed this code but it doesn't work correctly


DECLARE @Prevcif DECIMAL( 8,0) =0 
DECLARE @Runningamnt DECIMAL( 18,0) =0 
DECLARE @Base DECIMAL(18,0) = 20

UPDATE dbo.[mytable]
SET    @Runningamnt = [Amount] = CASE 
                                        WHEN [CIFNO] = @Prevcif THEN CASE 
                                                                          WHEN 
                                                                               balance 
                                                                               >
                                                                               = (@Base - @Runningamnt) THEN 
                                                                               @Base 
                                                                               - 
                                                                               @Runningamnt
                                                                          ELSE 
                                                                               balance
                                                                     END
                                        ELSE CASE 
                                                  WHEN balance >= @Base THEN 
                                                       @Base
                                                  ELSE balance
                                             END
                                   END,
       @Prevcif = [CIFNO]




DROP TABLE [mytable]
CREATE TABLE [dbo].[mytable](
	[BALANCE] [decimal](18, 0) NULL,	
	[branch] [decimal](4, 0) NOT NULL,
	[TBTYPE] [decimal](3, 0) NOT NULL,
	[CIFNO] [decimal](8, 0) NOT NULL,
	[SERIAL] [decimal](3, 0) NOT NULL,
	[priority] [int] NOT NULL,	
	[Amount] [decimal](18, 0) NULL	
) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX [OrderdByDEPandBal] ON [dbo].[mytable] 
(
	[CIFNO] ASC,
	[priority] ASC,
	[BALANCE] DESC,
	[branch] ASC,
	[TBTYPE] ASC,
	[SERIAL] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


INSERT INTO [mytable] 
SELECT 7,101,800,1,3,1,NULL UNION 
SELECT 5,101,800,1,1,1,NULL UNION
SELECT 5,101,800,1,2,1,NULL UNION
SELECT 2,101,12,1,3,2,NULL UNION
SELECT 100,101,700,1,3,3,NULL  UNION
SELECT 8,101,800,2,3,1,NULL UNION 
SELECT 5,101,800,2,1,1,NULL UNION
SELECT 2,101,813,2,2,1,NULL UNION
SELECT 3,101,12,2,3,2,NULL UNION
SELECT 3,101,700,2,3,3,NULL UNION
SELECT 10,101,800,3,3,1,NULL UNION 
SELECT 5,101,800,3,1,1,NULL UNION
SELECT 4,101,12,3,3,2,NULL UNION
SELECT 20,101,700,3,3,3,NULL UNION
SELECT 10,101,700,3,3,3,NULL

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-03-11 : 01:47:17

UPDATE dbo.[mytable]
SET @Runningamnt = [Amount] = CASE
WHEN [CIFNO] = @Prevcif THEN CASE
WHEN balance > = (@Base - @Runningamnt) THEN BALANCE/BALANCE
WHEN CIFNO = 3 and priority = 3 THEN 0
WHEN balance < @base and priority = 3 THEN balance - 1
ELSE
balance
END
ELSE CASE
WHEN balance >= @Base THEN
@Base
ELSE balance
END
END,
@Prevcif = [CIFNO]

Veera

fan2005
Yak Posting Veteran

85 Posts

Posted - 2014-03-11 : 07:20:11

quote:
Originally posted by VeeranjaneyuluAnnapureddy

UPDATE dbo.[mytable]
SET @Runningamnt = [Amount] = CASE
WHEN [CIFNO] = @Prevcif THEN CASE
WHEN balance > = (@Base - @Runningamnt) THEN BALANCE/BALANCE
WHEN CIFNO = 3 and priority = 3 THEN 0
WHEN balance < @base and priority = 3 THEN balance - 1
ELSE
balance
END
ELSE CASE
WHEN balance >= @Base THEN
@Base
ELSE balance
END
END,
@Prevcif = [CIFNO]

Veera

There is no Constraint for or logic behind CIFNO , priority and other fileds and there are just sample data . Imean it is not in the way that for all CIFNO = 3 and priority = 3 the amount is zero.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources