How to Change row wise total Query - Bandi

Posted by Jayakumars under Sql Server on 8/24/2015 | Points: 10 | Views : 911 | Status : [Member] [MVP] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Kumaraspcode2009@gmail.com

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

Latest Posts