How to Change row wise total Query - Bandi

Posted by Jayakumars under Sql Server on 8/24/2015 | Points: 10 | Views : 286 | Status : [Member] [MVP] | Replies : 2
Create Table tbla
(
Prodname varchar(20),
ProdDate date,
Plusamt decimal(18,2),
Minuamt decimal(18,2)
)


Insert into tbla VALUES('Mouse','02/02/1981',300,0)
Insert into tbla VALUES('Mouse1','03/02/1981',0,100)
Insert into tbla VALUES('Mouse2','04/02/1981',1400,0)
Insert into tbla VALUES('Mouse3','05/02/1981',0,1000)
Insert into tbla VALUES('Mouse4','06/02/1981',2000,100)

SELECT * from Tbla

-- so for My output Query this

ProdName Date Plusamt Minusamt
Mouse 1981-02-02 300.00 0.00
Mouse1 1981-03-02 0.00 100.00
Mouse2 1981-04-02 1400.00 0.00
Mouse3 1981-05-02 0.00 1000.00
Mouse4 1981-06-02 2000.00 100.00


-- But I nee this output
-- My select Query date parameters
-- From date 04-02-1981 to 06-02-1981

ProdName Date Plusamt Minusamt Total
Mouse2 1981-04-02 1400.00 0.00 1400
Mouse3 1981-05-02 0.00 1000.00 400
Mouse4 1981-06-02 2000.00 100.00 2300

Mark as Answer if its helpful to you


Responses

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

Up
0
Down
SQL Server 2012 version
declare @FromDate datetime = '04-02-1981' , @ToDate datetime = '06-02-1981'

SELECT *,SUM(Plusamt-Minuamt) OVER( ORDER BY ProdDate) as Total
FROM Tbla
WHERE ProdDate 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: Bandi on: 8/25/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down

SQL Server 2008 Version

GO
declare @FromDate datetime = '04-02-1981' , @ToDate datetime = '06-02-1981'
SELECT
[ProdDate],
Plusamt-Minuamt,
RunningTotal = Plusamt-Minuamt + COALESCE(
(
SELECT SUM(Plusamt-Minuamt)
FROM dbo.Tbla AS s
WHERE s.ProdDate < o.ProdDate
and ProdDate between @FromDate and @ToDate), 0
)
FROM dbo.Tbla AS o
WHERE ProdDate between @FromDate and @ToDate
ORDER BY [ProdDate];


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