How to solve this Query - Bandi

Posted by Jayakumars under Sql Server on 8/23/2015 | Points: 10 | Views : 414 | Status : [Member] [MVP] | Replies : 3
Hi

this is my table data

Sno Date Payment Receipt
1 02/02/1981 100 0
2 04/02/1981 0 200
3 05/02/1981 100 0


when i Query generated from date 04/02/1981 to 05/02/1981


My Query output this

sno Date Payment Receipt
1 04/02/1981 0 200
2 05/02/1981 100 0

here how to table my previous record for opening balacnce

i need how to single sql query from date and todate then
fromdate<date

how will perform where statement fromdate and todate and lessthan
fromdate

Mark as Answer if its helpful to you


Responses

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

Up
0
Down
declare @tab1 table(Sno	int, Date date, Payment int, Receipt int)
insert @tab1
SELECT 1, '02/02/1981', 100 ,0 union all
SELECT 2, '04/02/1981', 0 ,200 union all
SELECT 3, '05/02/1981', 100, 0

declare @FromDate date = '04/02/1981', @ToDate date = '05/02/1981'
SELECT *
FROM @tab1
WHERE (DATE between @FromDate and @ToDate) or date<@FromDate

-- If you want only recent record for the condition date<@FromDate
SELECT Top 1 * FROM @tab1 WHERE date<@FromDate
UNION ALL
SELECT *
FROM @tab1
WHERE (DATE between @FromDate and @ToDate)


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

Posted by: Jayakumars on: 8/24/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi
Bandi

I attached Images but not coming ok i mention my output given below



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

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

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