CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Faisal
Faisal

Reputation: 200

Query to handle the dates

Background Result set should look like

Type     Amount   Remaining_Credit   Transaction_date    expiry_date
Credit   12000    12000              10/11/2019          12/11/2019
Debit    -7500    4500               11/11/2019
Credit   4000     8500               11/11/2019
Credit   1000     5000               13/11/2019

(4500 is removed from remaining_credit because 12000 had an expiry_date for 12/11/2019)

Upvotes: 1

Views: 60

Answers (1)

Shekar Kola
Shekar Kola

Reputation: 1297

You can try following methods:

  1. Case expression to set Amount to 0 when it's expired 
CASE 
  WHEN (ISNULL(EXPIRY_DATE, DATEADD(DAY,1, GETDATE() ))) >= GETDATE()
     THEN AMOUNT
  ELSE 0 
END

CASE 
  WHEN EXPIRY_DATE IS NULL OR  EXPIRY_DATE >= GETDATE()
     THEN AMOUNT
  ELSE 0 
END
  1. Filter-out expired rows 
Where (ISNULL(EXPIRY_DATE, DATEADD(DAY,1, GETDATE() ))) >= GETDATE()

Full Script - Updated

Create table #Wallet
(Account_ID varchar(3),
 TransID int,
 TransDate datetime,
 ExpriryDate datetime,
 Amount float,
 TranType varchar (10),
 Credit float,
 Debit float )
 go 

 Insert into #Wallet 
 (Account_ID, TransID, TransDate, ExpriryDate, Amount, TranType, Credit,Debit)
 values 
 ('def', (ABS(CHECKSUM(NEWID()))/10000), DATEADD(DAY, -5, GETDATE()), DATEADD(DAY, -1, GETDATE()), 500, 'CR', 500, 0 ),
 ('def', (ABS(CHECKSUM(NEWID()))/10000), DATEADD(DAY, -5, GETDATE()), null, 400, 'CR', 500, 0 ),
 ('def', (ABS(CHECKSUM(NEWID()))/10000), DATEADD(DAY, -5, GETDATE()), null, -300, 'DR', 0, 300 )
 go


 --- Filter out expired rows -----------------------------------------------------------------
 SELECT TranType, 
        Amount, 
        TransDate, 
        SUM (Amount) over (partition by Account_ID order by TransDate) ReminingBalance,
        ExpriryDate
 FROM #Wallet
 Where (ISNULL(ExpriryDate, DATEADD(DAY,1, GETDATE() ))) >= GETDATE ()
 go


 --- Include expired rows with 0 Value -----------------------------------------------------------------

 SELECT TranType, 
        Amount, 
        TransDate, 
        SUM (NewAmount) over (partition by Account_ID order by TransDate) ReminingBalance,
        ExpriryDate
FROM 
        (SELECT *, (CASE WHEN ExpriryDate IS NULL OR  ExpriryDate >= GETDATE () THEN AMOUNT ELSE 0 END) as NewAmount FROM #Wallet) AS T1
go

--Drop table #Wallet

Upvotes: 2

Related Questions