How to Find Running Total Query - Bandi

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

Bandi

I need Running Total . I have attached Excel sheet
given below.

Mark as Answer if its helpful to you


Responses

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

Up
0
Down
Hi,

For the email subject 'Regarding Sql Query' sent on 28th August, refer below query


declare @tab table(ProdDate date, MemberNo int, Receipt int, Payment int)
insert @tab
SELECT '29-04-2015', 111, 17000, 1000 union all
SELECT '01-05-2015', 111, 1000, 100 union all
SELECT '01-06-2015', 111, 3000, 0 union all
SELECT '15-07-2015', 111, 0, 500 union all
SELECT '15-08-2015', 111, 0, 1000

declare @FromDate date = '01-06-2015', @ToDate date = '15-08-2015'
, @OpeningBal int
SELECT @OpeningBal = SUM(Receipt)-SUM(Payment)
FROM @tab
WHERE ProdDate < @FromDate
-- SELECT @OpeningBal


SELECT [ProdDate], Receipt, Payment, @OpeningBal+
Receipt-Payment +
COALESCE((SELECT SUM(Receipt-Payment) FROM @tab AS s
WHERE s.ProdDate < o.ProdDate
and ProdDate between @FromDate and @ToDate), 0) As Total
FROM @tab 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

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

Up
0
Down
Hi
Bandi

can you convert and send me this query in mysql

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/28/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
i do not know much about MySQL...

try to do by yourself... post us back if you get any error

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/28/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi
I canot attached image here i send a mail to you check and reply me

Mark as Answer if its helpful to you

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

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

Up
0
Down
hi
bandi

you mention this query

declare @FromDate date = '01-06-2015', @ToDate date = '15-08-2015'
, @OpeningBal int
SELECT @OpeningBal = SUM(Receipt)-SUM(Payment)
FROM @tab
WHERE ProdDate < @FromDate

can you attached same query?

you have mention 2 query i need one query can you change one query?



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/28/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
i think you have to do like this

SELECT @OpeningBal := SUM(Receipt)-SUM(Payment)
FROM @tab
WHERE ProdDate < @FromDate


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/28/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
refer
http://stackoverflow.com/questions/12578298/how-to-store-sumfield-name-in-a-variable-within-mysql-select-statement

http://stackoverflow.com/questions/14760060/mysql-assign-column-value-to-variable

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/28/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi

Bandi

I tried in your query not working . I mention below Query


CREATE TABLE `test`.`tbltest1` (

`id` INT NOT NULL AUTO_INCREMENT ,

`ShareNo` INT NULL ,

`Date` DATE NULL ,

`Recptamt` DECIMAL(18,2) NULL ,

`Payamt` DECIMAL(18,2) NULL ,

PRIMARY KEY (`id`) );


INSERT INTO `test`.`tbltest1` (`ShareNo`, `Date`, `Recptamt`, `Payamt`) VALUES (2544, '2015-04-29', '17000.00', '0');

INSERT INTO `test`.`tbltest1` (`ShareNo`, `Date`, `Recptamt`, `Payamt`) VALUES (2544, '2015-05-01', '1000.00', '0');

INSERT INTO `test`.`tbltest1` (`ShareNo`, `Date`, `Recptamt`, `Payamt`) VALUES (2544, '2015-05-02', '10000.00', '0');

INSERT INTO `test`.`tbltest1` (`ShareNo`, `Date`, `Recptamt`, `Payamt`) VALUES (2544, '2015-05-03', '3000.00', '0');

INSERT INTO `test`.`tbltest1` (`ShareNo`, `Date`, `Recptamt`, `Payamt`) VALUES (2544, '2015-05-04', '1000.00', '0');

INSERT INTO `test`.`tbltest1` (`ShareNo`, `Date`, `Recptamt`, `Payamt`) VALUES (2544, '2015-04-29', '0', '1000.00');

INSERT INTO `test`.`tbltest1` (`ShareNo`, `Date`, `Recptamt`, `Payamt`) VALUES (2544, '2015-05-01', '0', '100.00');

INSERT INTO `test`.`tbltest1` (`ShareNo`, `Date`, `Recptamt`, `Payamt`) VALUES (2544, '2015-05-02', '0', '300.00');

INSERT INTO `test`.`tbltest1` (`ShareNo`, `Date`, `Recptamt`, `Payamt`) VALUES (2544, '2015-05-03', '0', '400.00');

INSERT INTO `test`.`tbltest1` (`ShareNo`, `Date`, `Recptamt`, `Payamt`) VALUES (2544, '2015-05-04', '0', '600.00');

INSERT INTO `test`.`tbltest1` (`ShareNo`, `Date`, `Recptamt`, `Payamt`) VALUES (2548, '2015-05-04', '0', '800');



But My Query This But not working can you alter this



CREATE DEFINER=`root`@`localhost` PROCEDURE `Testt`(in _FromDate date,_ToDate date,_shareno int)
BEGIN
SELECT @OpeningBal:= SUM(Recptamt)-SUM(Payamt) OpeningBal
FROM tbltest1
WHERE Date < _FromDate;


SELECT Date, Recptamt, Payamt, @OpeningBal +
Recptamt-Payamt +
COALESCE((SELECT SUM(Recptamt-Payamt) FROM tbltest1 AS s
WHERE s.Date < o.Date
and Date between _FromDate and _ToDate), 0) As Total , @OpeningBal as OpeningBal
FROM tbltest1 AS o
WHERE Date between @FromDate and @ToDate and shareno=_shareno
ORDER BY Date;
END









Mark as Answer if its helpful to you

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

Login to post response