How to Find Running Total Query - Bandi

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

Hi
Bandi

can you convert and send me this query in mysql

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

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

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

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

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

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

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

Kumaraspcode2009@gmail.com

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

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

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

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

Kumaraspcode2009@gmail.com

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