Insert values into a column in the same table based on a criteria

Transact-SQL
1

I'm need your help to insert vales into my table based on criteria.
My table & its values are as follows.

CREATE TABLE [TEST].[dbo].[bank] (
[DATE] datetime,
[CHQNO] float,
[Deposit] float,
[Withdrawal] float,
[Status] nvarchar(255)
)

INSERT INTO [Test].[dbo].[bank]
([DATE]
,[CHQNO]
,[Deposit]
,[Withdrawal])
VALUES
('2016-04-19',533247,1975000,NULL)
,('2016-04-19',533247,NULL,1975000)
,('2016-05-05',NULL,1947293.42,NULL)
,('2016-05-05',NULL,1947293.42,NULL)
,('2016-06-03',535199,1930000,NULL)
,('2016-06-04',535199,NULL,1930000)
,('2016-08-04',NULL,195000,NULL)
,('2016-08-05',628490,5000,NULL)
,('2016-08-05',628490,NULL,5000)
,('2016-12-30',750200,15000,NULL)
,('2016-12-31',326500,NULL,15000)

I want to update the value in [Status] column as 'RTN' if there is two records with same value in [CHQNO] column & same value in [Deposit] & [Withdrawal] columns.

I'm expecting a table like this.

DATE CHQNO Deposit Withdrawal Status
19-Apr-16 533247 1975000 NULL RTN
19-Apr-16 533247 NULL 1975000 RTN
5-May-16 NULL 1947293.42 NULL NULL
5-May-16 NULL 1947293.42 NULL NULL
3-Jun-16 535199 1930000 NULL RTN
4-Jun-16 535199 NULL 1930000 RTN
4-Aug-16 NULL 195000 NULL NULL
5-Aug-16 628490 5000 NULL RTN
5-Aug-16 628490 NULL 5000 RTN
30-Dec-16 750200 15000 NULL NULL
31-Dec-16 326500 NULL 15000 NULL

Can someone help me on how to code to check & insert the values into [Status] column.

Thank you very much.

2

Try this

WITH CTE
AS
(
select CHQNO
from bank
group by CHQNO
having COUNT(chqno) > 1
)

Update T
SET Status = 'RTN'
FROM [Test].[dbo].[bank] T, CTE C
WHERE T.CHQNO = C.CHQNO

3

I have an idea , is not elegant but anyway but could be a starting point for you.

SELECT
    [DATE]
    ,[CHQNO]
    ,[Deposit]
    ,[Withdrawal]
    ,CASE WHEN A.CHQNO = A.CHQNO_prev AND Deposit_prev = Withdrawal THEN 'RTN1'
          WHEN A.CHQNO = A.CHQNO_prev AND Deposit = Withdrawal_prev THEN 'RTN2'
          WHEN A.CHQNO = A.CHQNO_prev AND Deposit_next = Withdrawal THEN 'RTN3'
          WHEN A.CHQNO = A.CHQNO_prev AND Deposit = Withdrawal_next THEN 'RTN4'
          ELSE NULL
     END AS [status]
FROM
(
    SELECT
        *
        ,LAG([CHQNO],1,[CHQNO]) OVER(PARTITION BY [CHQNO] ORDER BY DATE,Deposit,Withdrawal) as CHQNO_prev
        ,LAG(Deposit,1,Deposit) OVER(PARTITION BY [CHQNO] ORDER BY DATE,Deposit,Withdrawal) as Deposit_prev
        ,LAG(Withdrawal,1,Withdrawal) OVER(PARTITION BY [CHQNO] ORDER BY DATE,Deposit,Withdrawal) as Withdrawal_prev    
        ,LEAD(Deposit,1,Deposit) OVER(PARTITION BY [CHQNO] ORDER BY DATE,Deposit,Withdrawal) as Deposit_next
        ,LEAD(Withdrawal,1,Withdrawal) OVER(PARTITION BY [CHQNO] ORDER BY DATE,Deposit,Withdrawal) as Withdrawal_next
    FROM dbo.Bank
 )A

or this one:

SELECT
    [DATE] ,
    [CHQNO] ,
    [Deposit] ,
    [Withdrawal] 
    ,CASE WHEN EXISTS (SELECT * FROM dbo.bank AS D WHERE d.chqno = b.chqno AND d.withdrawal = b.deposit) THEN 'RTN'
      WHEN EXISTS (SELECT * FROM dbo.bank AS D WHERE d.chqno = b.chqno AND d.deposit = b.withdrawal) THEN 'RTN'
      ELSE NULL
 END AS STATUS
FROM
    dbo.Bank as B


 	DATE	CHQNO	Deposit	Withdrawal	status
1	05.05.2016 00:00:00	NULL	1947293,42	NULL	NULL
2	05.05.2016 00:00:00	NULL	1947293,42	NULL	NULL
3	04.08.2016 00:00:00	NULL	195000	NULL	NULL
4	31.12.2016 00:00:00	326500	NULL	15000	NULL
5	19.04.2016 00:00:00	533247	NULL	1975000	RTN3
6	19.04.2016 00:00:00	533247	1975000	NULL	RTN2
7	03.06.2016 00:00:00	535199	1930000	NULL	RTN4
8	04.06.2016 00:00:00	535199	NULL	1930000	RTN1
9	05.08.2016 00:00:00	628490	NULL	5000	RTN3
10	05.08.2016 00:00:00	628490	5000	NULL	RTN2
11	30.12.2016 00:00:00	750200	15000	NULL	NULL
1 Like
4

Another idea, if you have a CHQNO distinct per date (this should work only if you have CHQNO once per day )

;WITH cteDeposit
AS (SELECT DATE,CHQNO,MAX(Deposit) AS myDeposit FROM dbo.bank GROUP BY Date,CHQNO)
,cteWithdrawal 
AS  (SELECT DATE,CHQNO,MAX(Withdrawal) AS myWithdrawal FROM dbo.bank GROUP BY Date,CHQNO)

SELECT
    B.Date
    ,B.CHQNO
    ,B.Deposit
    ,B.Withdrawal
    ,CASE WHEN B.CHQNO = W.CHQNO AND B.Deposit = W.myWithdrawal THEN 'RTN'
          WHEN D.CHQNO = B.CHQNO AND D.myDeposit = B.Withdrawal THEN 'RTN'  
          ELSE NULL
     END AS Status    
FROM
    dbo.Bank AS B
    LEFT JOIN cteDeposit AS D
        ON B.CHQNO  = D.CHQNO 
        AND B.Withdrawal = D.myDeposit
    LEFT JOIN cteWithdrawal AS W
        ON B.CHQNO = W.CHQNO
        AND B.Deposit = W.myWithdrawal 


 	Date	CHQNO	Deposit	Withdrawal	Status
1	19.04.2016 00:00:00	533247	1975000	NULL	RTN
2	19.04.2016 00:00:00	533247	NULL	1975000	RTN
3	05.05.2016 00:00:00	NULL	1947293,42	NULL	NULL
4	05.05.2016 00:00:00	NULL	1947293,42	NULL	NULL
5	03.06.2016 00:00:00	535199	1930000	NULL	RTN
6	04.06.2016 00:00:00	535199	NULL	1930000	RTN
7	04.08.2016 00:00:00	NULL	195000	NULL	NULL
8	05.08.2016 00:00:00	628490	5000	NULL	RTN
9	05.08.2016 00:00:00	628490	NULL	5000	RTN
10	30.12.2016 00:00:00	750200	15000	NULL	NULL
11	31.12.2016 00:00:00	326500	NULL	15000	NULL
1 Like
5

My suggestion:

update a
   set a.status='RTN'
  from test.dbo.bank as a
 where a.chqno is not null
   and exists (select 1
                 from test.dbo.bank as b
                where b.chqno=a.chqno
                group by b.chqno
                having sum(isnull(deposit,0))=sum(isnull(withdrawal,0))
              )
;
1 Like
6

Thanks. This works the way i wanted.

7

Thank you so much