July 15, 2014 at 8:39 am
I have table A
|account | Unmort |
| A |10.000.000 |
and a Table B
|account| Jenis | Nominal | Unmort |Total|
-------------------------------------------
| A | 021 | 200.000| - | - |
| A | 028 | 3.200.000| - | - |
| A | 023 | 7.200.000| - | - |
how to update to be like this??
|account| Jenis |Nominal | Unmort |Total |
| A | 021 |200.000 | - |200.000 |
| A | 028 |3.200.000 | 2.800.000 |400.000 |
| A | 023 |7.200.000 | 7.200.000 | 0 |
for this type of account number jenis 021 Field Unmort Fill set= 0 and Field Total must not be a minus
July 15, 2014 at 11:33 am
I must be sincere, I can't guarantee this method and you should test it thoroughly before implementing it. This method is documented in the following article and you should read it: http://www.sqlservercentral.com/articles/T-SQL/68467/
This will be fast and remove the need of a cursor. I made some tests and added more data to test it for multiple accounts. Please let me know if you have any doubts.
--Creating sample data
CREATE TABLE tableA(
account char(1),
Unmort decimal(10,2));
INSERT INTO tableA VALUES('A', 10000), ('B', 9000);
CREATE TABLE tableB(
account char(1),
Jenis char(3),
Nominal decimal(10,2),
Unmort decimal(10,2),
Total decimal(10,2));
INSERT INTO tableB VALUES
('A', '021', 200, NULL, NULL)
,('A', '028', 3200, NULL, NULL)
,('A', '023', 7200, NULL, NULL)
,('B', '021', 200, NULL, NULL)
,('B', '028', 3200, NULL, NULL)
,('B', '023', 7200, NULL, NULL);
--End of sample data
CREATE TABLE #QUTable(
account char(1),
Jenis char(3),
Nominal decimal(10,2),
TotalUnmort decimal(10,2),
Total decimal(10,2));
CREATE INDEX IX_QUTable ON #QUTable( account, Nominal DESC);
INSERT INTO #QUTable
SELECT b.account,
b.Jenis,
b.Nominal,
a.Unmort,
b.Total
FROM tableB b
JOIN tableA a ON b.account = a.account
ORDER BY account, Nominal DESC;
DECLARE @Unmort decimal(10, 2) = 0,
@account char(3) = ''; --Must be initialized
UPDATE q SET
@Unmort = CASE WHEN CASE WHEN @account <> account THEN TotalUnmort ELSE @Unmort END > 0
THEN CASE WHEN @account <> account THEN TotalUnmort ELSE @Unmort END - Nominal
ELSE 0 - Nominal
END,
Total = CASE WHEN @Unmort > 0 THEN 0 ELSE ABS(@Unmort) END,
@account = account
FROM #QUTable q WITH (TABLOCKX)
OPTION (MAXDOP 1);
UPDATE b SET
Unmort = q.Nominal - q.Total,
Total = q.Total
FROM Tableb b WITH (TABLOCKX)
JOIN #QUTable q ON b.account = q.account AND b.Jenis = q.Jenis;
DROP TABLE #QUTable
SELECT *
FROM tableB
ORDER BY account, Nominal;
GO
--Clean my db
DROP TABLE tableA
DROP TABLE tableB
July 15, 2014 at 3:15 pm
HI tri_hartanto,
First, It's so fuzzy to understand what you are trying to do.
Please furnish table structure with sample data and with your intended Result Set/Table and describe what you trying to achieve. As it helps readers to get better ex-poser towards your question and able address a solution to your problem.
As Per my understanding from your description that you wanted to make Unmort Field to "0" If their is Null or "-" and Total Field Shouldn't reflect the negated Value since Unmort Field is Null or "-" for that particular Account.
If that is right. Below is the query that works for your need.
UPDATE R
SET R.Unmort = CASE WHEN ISNULL(R.Unmort,'-') = '-' THEN '0' ELSE R.Unmort END,
R.Total = CASE WHEN ISNULL(R.Unmort,'-') = '-' THEN R.Total ELSE R.Total - R.Unmort END
FROM ResultTable R
WHERE R.Account = 'A'
Thanks,
Prudhvi
July 15, 2014 at 3:48 pm
Prudhvi _SQL (7/15/2014)
HI tri_hartanto,...
UPDATE R
SET R.Unmort = CASE WHEN ISNULL(R.Unmort,'-') = '-' THEN '0' ELSE R.Unmort END,
R.Total = CASE WHEN ISNULL(R.Unmort,'-') = '-' THEN R.Total ELSE R.Total - R.Unmort END
FROM ResultTable R
WHERE R.Account = 'A'
Thanks,
Prudhvi
Are you serious? What are you trying to do with that?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply