How to Fixed this issue - Bandi

Posted by Jayakumars under Sql Server on 8/24/2015 | Points: 10 | Views : 1142 | Status : [Member] [MVP] | Replies : 1
Hi
Bandi

Create table PaymentDet
(
Name varchar(20),
Date date,
Payment decimal(18,2),
Receipt decimal(18,2)
)

Insert into PaymentDet values('John1','19/08/2015',0,500)
Insert into PaymentDet values('John','20/08/2015',1500,0)
Insert into PaymentDet values('John sanjith','21/08/2015',100,300)
Insert into PaymentDet values('John wesly','22/08/2015',100,0)
Insert into PaymentDet values('John read','23/08/2015',0,300)

Select * from PaymentDet

--now my output this

Name Date Payment Receipt
John 2015-08-20 1500.00 0.00
John sanjith 2015-08-21 1000.00 300.00
John wesly 2015-08-22 2500.00 0.00
John read 2015-08-23 0.00 300.00


--But My Need output
--FromDate '21/08/2015' to '23/08/2015'
Name Date Payment Receipt OpeningBalance OldBal
John sanjith 2015-08-21 1000.00 300.00 700 1000.00
John wesly 2015-08-22 2500.00 0.00 3200 1000.00
John read 2015-08-23 0.00 300.00 2900 1000.00

-- My Requiremnt is
--when i enter from date and to date opening balance calculation like this
-- OpeningBalance=OpeningBalance+Payment-Receipt
--Old blanace previous date value calculate like openingbalance calculation
--for ex: here I enter 21 to 23 but i need comes 21 to 23 then before from date
-- 21 below records sum and come oldbal column calculation like openingbalance
--how will do this

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com



Responses

Posted by: Bandi on: 8/25/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down

declare @FromDate datetime = '21-08-2015' , @ToDate datetime = '23-08-2015'
SELECT
[Date],
Payment-Receipt,
RunningTotal = Payment-Receipt + COALESCE(
(
SELECT SUM(Payment-Receipt)
FROM dbo.PaymentDet AS s
WHERE s.Date < o.Date
and Date between @FromDate and @ToDate), 0
)
,(SELECT SUM(Payment-Receipt) FROM dbo.PaymentDet WHERE date<@FromDate) OldBal
FROM dbo.PaymentDet AS o
WHERE Date between @FromDate and @ToDate
ORDER BY [Date];


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response