How to solve this Query - Bandi

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

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

Kumaraspcode2009@gmail.com

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

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