How to Perform Running Total Querys - Bandi

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

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 below 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


Responses

Posted by: Sheonarayan on: 8/29/2015 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
Jaya...

Thanks for asking question, however you should have explained the code so that it would be quicker for members to understand and help you quickly. Could you do that please. Also kindly, keep the codes in CODE block by selecting the code and clicking on Code icon.

Thanks

Regards,
Sheo Narayan
http://www.dotnetfunda.com

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

Posted by: Manub22 on: 9/22/2015 [Member] Starter | Points: 25

Up
0
Down
Please provide and explain the criteria on which you want to calculate the Running totals, but for now you can check the following query:

;with RunTot as (
select id, ShareNo, Date,
pmt = CASE WHEN Recptamt <> 0.00 THEN Recptamt ELSE -Payamt END
from tbltest1
)
select A.id, A.ShareNo, A.Date, A.pmt,
sum(B.pmt) as RunTotal
from RunTot A
JOIN RunTot B
ON A.id >= B.id
GROUP BY A.Id, A.ShareNo, A.Date, A.pmt


For more details check mu blog post on Running totals: http://sqlwithmanoj.com/tag/running-totals/

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

Login to post response