making ledger report from two tables


making ledger report from two tables



I have two tables ledgertable,openingtable I want a report like given below using both the tables like this:
Item No. | Item Name | opening | Date | Voucher No. | Purchase | Issue | Return | Department | balance | Remarks



1 Ac Box 2 01/12/2018 20180000000044 1 3.00
09/16/2017 2017916005 1 III DORM 2.00 for Commen Room, Collected by Kishan
10/07/2017 2017107003 1 III DORM 1.00 Collected by Sanjay
09/15/2017 2017000000461 1 2.00
10/06/2017 2017000000526 1 3.00
I am not getting the query how to do it. I have written a query but it is giving wrong result in balance column. Basically all the values are coming right but I am getting problem to calculate balance.
Here is my query :
"select l.trandate,l.voucherno,l.itemno,l.itemname,l.recieve,l.issue,l.returnback,l.to_dept,o.opening,l.remarks,balance = ((o.opening+isnull(recieve,0)+isnull(returnback,0))-isnull([issue],0))+isnull((select sum((isnull(recieve,0)+isnull(returnback,0))-isnull([issue],0)) from ledgertable b where b.voucherno < l.voucherno and b.itemno=l.itemno),0) from ledgertable l join openingstock o on l.itemno=o.itemno where l.trandate between '" + startdate + "' and '" + enddate + "'"
I am attaching image of result which I am getting with this queryenter image description here









By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

-

Popular posts from this blog

api-platform.com Unable to generate an IRI for the item of type

api-platform.com Unable to generate an IRI for the item of type I'm trying to setup my first api-platform instance and have run into some presumably noob questions around IDE. What i'm trying to do is setting up an register and reset entity in order to deal with registrations and password reminders from an app. And then have the entity's passed to a custom controller to do some checks, balances and mails afterwards (haven’t gotten around to that yet). But I keep running into this error when posting to my custom entities: { "@context": "/api/contexts/Error", "@type": "hydra:Error", "hydra:title": "An error occurred", "hydra:description": "Unable to generate an IRI for the item of type "AppXXXUserBundleEntityRegister"", "trace": [ { "namespace": "", "short_class": "", "c
Read more

How to set up datasource with Spring for HikariCP?

How to set up datasource with Spring for HikariCP? Hi I'm trying to use HikariCP with Spring for connection pool. I'm using jdbcTempLate and JdbcdaoSupport. This is my spring configuration file for datasource: <bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource"> <property name="dataSourceClassName" value="oracle.jdbc.driver.OracleDriver"/> <property name="dataSource.url" value="jdbc:oracle:thin:@localhost:1521:XE"/> <property name="dataSource.user" value="username"/> <property name="dataSource.password" value="password"/> </bean> But unfortunately the following error message is generating: Cannot resolve reference to bean 'dataSource' while setting bean property 'dataSource'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource'
Read more

PHP contact form sending but not receiving emails

PHP contact form sending but not receiving emails I have created a contact form using PHP to send the data. The form seems to send fine as there are no errors and success message appears, however I am not receving the emails into the designated inbox. I am using PHPMailer as I originally tried just using the php 'mail' command which I now understand is a bit hit and miss. I cannot see why I would not be receving the emails so I would be very grateful for any help that could be given. I'm quite new to PHP so please be patient with me :) <?php use PHPMailerPHPMailerPHPMailer; use PHPMailerPHPMailerException; $msg = ""; if (isset($_POST['submit'])) { require 'phpmailer/src/Exception.php'; require 'phpmailer/src/PHPMailer.php'; require 'phpmailer/src/SMTP.php'; function sendemail ($to, $from, $body) { $mail = new PHPMailer(true); $mail->setFrom($from); $mail->addAddress($to); $ma
Read more