none
Calculation error in Query RRS feed

  • Question

  • I have a table tblPurchaseStatement. In this table we store the Purchase and Payments.

    RecordID      Vendor            Inflow     Outflow    Voucher
    -----------------------------------------------------------------
    1                   ABC CORP         2000        0               Purchase
    2                   ABC CORP         0             2000          Payment

    In case of Purchase, we are getting IN value so it's mentioned under Inflow
    In case of Payment, Amount is going OUT so it's mentioned under Outflow

    Another table is tblSaleStatementCustomer. In this table we store the sale orders similar to below, but don't have any data.

    RecordID       CustomerName     Inbound       Outbound        VoucherType
    ---------------------------------------------------------------------------------------
    1                    ABC CORP              0                   3000                Sales
    2                    ABC CORP              2000              0                     Receipt

    In case of Sales, Amount is going OUT so it's mentioned under Outbound
    In case of Receipt, we are getting IN value so it's mentioned under Inbound


                         

    Now, the last table is tblVendors. This is the table for storing the details of the party from whom we purchase and to whom we sale. As you can Identify them in Type Column. We Purchase from Vendor and sell to Dealer. And sometimes we do purchase from Dealer too.

    RecordID      Name              ContactNumber       OpeningBalance        Type
    -----------------------------------------------------------------------------------

    1                   ABC CORP        6569569696             5000                          Dealer
    2                   XYZ INC.          6585858547             7000                          Vendor  

    I had generated a query with the help of few MSDN Members too. The purpose of the query is to look for these three tables for the Incoming and Outgoing Amounts in Case if our party type is Dealer. This will calculate the difference and at last, add the Opening Balance to the amount and will display for each and every Dealer. The query looks similar to below -

    WITH ctePurchase AS (SELECT [Vendor], ISNULL(SUM(CASE WHEN [Voucher] = 'Purchase' THEN - [Inflow] ELSE [Outflow] END), 0) AS [BalancePur] 
    FROM tblPurchaseStatement GROUP BY [Vendor]), 
    cteSale AS (SELECT [CustomerName], ISNULL(SUM(CASE WHEN [VoucherType] = 'Sales' THEN [Outbound] ELSE - [Inbound] END), 0) AS [BalanceSal] 
    FROM tblSaleStatementCustomer where CustomerType = 'Dealer' GROUP BY [CustomerName])SELECT a.[Name], 
    CASE  WHEN [Type] = 'Vendor' and b.[BalancePur] < c.[BalanceSal] then - b.[BalancePur] - c.[BalanceSal] + a.[OpeningBalance] 
    WHEN [Type] = 'Dealer' and b.[BalancePur] < c.[BalanceSal] then  b.[BalancePur] + c.[BalanceSal] + a.[OpeningBalance] ELSE 0 END AS [Balance], 
    a.[ContactNumber] FROM tblVendors a LEFT JOIN ctePurchase b ON a.[Name] = b.[Vendor] LEFT JOIN cteSale c ON a.[Name] = c.[CustomerName] WHERE Type = 'Dealer'

    Now, if we have NO records in tblPurchaseStatement and tblSaleStatementCustomer but the tblVendor have record of all the parties. And, if we run the query we must get the Balance 5000. But, we are not getting the result. Can someone help me of what may be wrong with the query ?


    Friday, August 5, 2016 2:32 PM

Answers

  • WITH    ctePurchase
              AS ( SELECT   [Vendor] ,
                            SUM(ISNULL([Inflow], 0) -ISNULL([Outflow], 0)) AS [BalancePur]
                   FROM     tblPurchaseStatement
                   GROUP BY [Vendor]
                 ),
            cteSale
              AS ( SELECT   [CustomerName] ,
                            SUM(ISNULL(Inbound, 0) -ISNULL(Outbound, 0)) AS [BalanceSal]
                   FROM     tblSaleStatementCustomer
                   GROUP BY [CustomerName]
                 )
        SELECT  a.[Name] ,
                IsNull(a.OpeningBalance, 0) + IsNull(b.BalancePur, 0) + IsNull(c.BalanceSal, 0) AS [Balance] ,
                a.[ContactNumber]
        FROM    tblVendors a
                LEFT JOIN ctePurchase b ON a.[Name] = b.[Vendor]
                LEFT JOIN cteSale c ON a.[Name] = c.[CustomerName]
        WHERE   Type = 'Dealer';

    Tom
    • Marked as answer by Sid Williams Saturday, August 6, 2016 10:26 AM
    Saturday, August 6, 2016 6:51 AM

All replies

  • hmm, not an ideal model. Any chance to correct this??

    And post a concise and complete example. Include table DDL (preferable as table variable) and sample data INSERT statements as runnable T-SQL script.

    Cause when typing it from your prose, it has a lot of errors. Also qualify always all columns with table aliases.

    DECLARE @tblPurchaseStatement TABLE
        (
          RecordID INT ,
          Vendor NVARCHAR(255) ,
          Inflow INT ,
          Outflow INT ,
          Voucher NVARCHAR(255)
        );
    
    INSERT  INTO @tblPurchaseStatement
    VALUES  ( 1, 'ABC CORP', 2000, 0, 'Purchase' ),
            ( 2, 'ABC CORP', 0, 2000, 'Payment ' );
    
    
    DECLARE @tblSaleStatementCustomer TABLE
        (
          RecordID INT ,
          CustomerName NVARCHAR(255) ,
          Inbound INT ,
          Outbound INT ,
          VoucherType NVARCHAR(255)
        );
     
    INSERT  INTO @tblSaleStatementCustomer
            ( RecordID, CustomerName, Inbound, Outbound, VoucherType )
    VALUES  ( 1, 'ABC CORP', 0, 3000, 'Sales	' ),
            ( 2, 'ABC CORP', 2000, 0, 'Receipt ' );                      
    
    DECLARE @tblVendors TABLE
        (
          RecordID INT ,
          Name NVARCHAR(255) ,
          ContactNumber NVARCHAR(255) ,
          OpeningBalance INT ,
          CustomerType NVARCHAR(255)
        );
    
    INSERT  INTO @tblVendors
            ( RecordID, Name, ContactNumber, OpeningBalance, CustomerType )
    VALUES  ( 1, 'ABC CORP ', '6569569696', 5000, 'Dealer' ),
            ( 2, 'XYZ INC.', '6585858547 ', 7000, 'Vendor' );
    
    
    WITH    ctePurchase
              AS ( SELECT   [Vendor] ,
                            ISNULL(SUM(CASE WHEN [Voucher] = 'Purchase' THEN -[Inflow]
                                            ELSE [Outflow]
                                       END), 0) AS [BalancePur]
                   FROM     @tblPurchaseStatement
                   GROUP BY [Vendor]
                 ),
            cteSale
              AS ( SELECT   [CustomerName] ,
                            ISNULL(SUM(CASE WHEN [VoucherType] = 'Sales' THEN [Outbound]
                                            ELSE -[Inbound]
                                       END), 0) AS [BalanceSal]
                   FROM     @tblSaleStatementCustomer
                   WHERE    VoucherType = 'Dealer'
                   GROUP BY [CustomerName]
                 )
        SELECT  a.[Name] ,
                CASE WHEN [Type] = 'Vendor'
                          AND b.[BalancePur] < c.[BalanceSal] THEN -b.[BalancePur] - c.[BalanceSal] + a.[OpeningBalance]
                     WHEN [Type] = 'Dealer'
                          AND b.[BalancePur] < c.[BalanceSal] THEN b.[BalancePur] + c.[BalanceSal] + a.[OpeningBalance]
                     ELSE 0
                END AS [Balance] ,
                a.[ContactNumber]
        FROM    @tblVendors a
                LEFT JOIN ctePurchase b ON a.[Name] = b.[Vendor]
                LEFT JOIN cteSale c ON a.[Name] = c.[CustomerName]
        WHERE   Type = 'Dealer';
    

    Friday, August 5, 2016 3:35 PM
  • Thanks for your consideration, when I execute the query suggested by You. I am still not getting the values right. Have a look at the screenshot below.

    Friday, August 5, 2016 6:29 PM
  • hmm, maybe I didn't make my point clear: You haven't post an useful example. Much worse: Why do you think posting an image of code is useful at all???
    Saturday, August 6, 2016 2:45 AM
  • Okay Sir ! I will post the code and the table structure in a better way in few minutes. Kindly consider that.
    Saturday, August 6, 2016 3:12 AM
  • Sir, first of all, I had created three tables

    CREATING tblPurchaseStatement

    USE [QueryTesting]
    GO
    
    /****** Object:  Table [dbo].[tblPurchaseStatement]    Script Date: 8/6/2016 8:42:57 AM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[tblPurchaseStatement](
    	[RecordID] [int] NULL,
    	[Vendor] [nvarchar](255) NULL,
    	[Inflow] [int] NULL,
    	[Outflow] [int] NULL,
    	[Voucher] [nvarchar](255) NULL
    ) ON [PRIMARY]
    
    GO

    CREATING tblSaleStatementCustomer

    USE [QueryTesting]
    GO
    
    /****** Object:  Table [dbo].[tblSaleStatementCustomer]    Script Date: 8/6/2016 8:44:01 AM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[tblSaleStatementCustomer](
    	[RecordID] [int] NULL,
    	[CustomerName] [nvarchar](255) NULL,
    	[Inbound] [int] NULL,
    	[Outbound] [int] NULL,
    	[VoucherType] [nvarchar](255) NULL
    ) ON [PRIMARY]
    
    GO

    CREATING tblVendors

    USE [QueryTesting]
    GO
    
    /****** Object:  Table [dbo].[tblVendors]    Script Date: 8/6/2016 8:44:27 AM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[tblVendors](
    	[RecordID] [int] NULL,
    	[Name] [nvarchar](255) NULL,
    	[ContactNumber] [nvarchar](255) NULL,
    	[OpeningBalance] [int] NULL,
    	[Type] [nvarchar](255) NULL
    ) ON [PRIMARY]

    Now that our tables are created, we are entering some data to these three tables

    INSERT  INTO tblPurchaseStatement
    VALUES  ( 1, 'ABC CORP', 2000, 0, 'Purchase' ),
            ( 2, 'ABC CORP', 0, 2000, 'Payment ' );
    
    
    INSERT  INTO tblSaleStatementCustomer
            ( RecordID, CustomerName, Inbound, Outbound, VoucherType )
    VALUES  ( 1, 'ABC CORP', 0, 3000, 'Sales	' ),
            ( 2, 'ABC CORP', 2000, 0, 'Receipt ' );        
    
    
    INSERT  INTO tblVendors
            ( RecordID, Name, ContactNumber, OpeningBalance, CustomerType )
    VALUES  ( 1, 'ABC CORP ', '6569569696', 5000, 'Dealer' ),
            ( 2, 'XYZ INC.', '6585858547 ', 7000, 'Vendor' );


    Now, as we just entered the data into the table, we are executing this query suggested by you.

    WITH    ctePurchase
              AS ( SELECT   [Vendor] ,
                            ISNULL(SUM(CASE WHEN [Voucher] = 'Purchase' THEN -[Inflow]
                                            ELSE [Outflow]
                                       END), 0) AS [BalancePur]
                   FROM     tblPurchaseStatement
                   GROUP BY [Vendor]
                 ),
            cteSale
              AS ( SELECT   [CustomerName] ,
                            ISNULL(SUM(CASE WHEN [VoucherType] = 'Sales' THEN [Outbound]
                                            ELSE -[Inbound]
                                       END), 0) AS [BalanceSal]
                   FROM     tblSaleStatementCustomer
                   WHERE    VoucherType = 'Dealer'
                   GROUP BY [CustomerName]
                 )
        SELECT  a.[Name] ,
                CASE WHEN [Type] = 'Vendor'
                          AND b.[BalancePur] < c.[BalanceSal] THEN -b.[BalancePur] - c.[BalanceSal] + a.[OpeningBalance]
                     WHEN [Type] = 'Dealer'
                          AND b.[BalancePur] < c.[BalanceSal] THEN b.[BalancePur] + c.[BalanceSal] + a.[OpeningBalance]
                     ELSE 0
                END AS [Balance] ,
                a.[ContactNumber]
        FROM    tblVendors a
                LEFT JOIN ctePurchase b ON a.[Name] = b.[Vendor]
                LEFT JOIN cteSale c ON a.[Name] = c.[CustomerName]
        WHERE   Type = 'Dealer';

    By Executing the execute, I am getting the following result.

        

    Name          Balance       ContactNumber
    ----------------------------------------------------------
    ABC CORP    0                 6569569696 

    But it seems wrong, I should get a Balance of 6000 instead of 0   

    Now, can you help me up sir ?


    Saturday, August 6, 2016 3:27 AM
  • WITH    ctePurchase
              AS ( SELECT   [Vendor] ,
                            SUM(ISNULL([Inflow], 0) -ISNULL([Outflow], 0)) AS [BalancePur]
                   FROM     tblPurchaseStatement
                   GROUP BY [Vendor]
                 ),
            cteSale
              AS ( SELECT   [CustomerName] ,
                            SUM(ISNULL(Inbound, 0) -ISNULL(Outbound, 0)) AS [BalanceSal]
                   FROM     tblSaleStatementCustomer
                   GROUP BY [CustomerName]
                 )
        SELECT  a.[Name] ,
                IsNull(a.OpeningBalance, 0) + IsNull(b.BalancePur, 0) + IsNull(c.BalanceSal, 0) AS [Balance] ,
                a.[ContactNumber]
        FROM    tblVendors a
                LEFT JOIN ctePurchase b ON a.[Name] = b.[Vendor]
                LEFT JOIN cteSale c ON a.[Name] = c.[CustomerName]
        WHERE   Type = 'Dealer';

    Tom
    • Marked as answer by Sid Williams Saturday, August 6, 2016 10:26 AM
    Saturday, August 6, 2016 6:51 AM
  • Thanks Tom, it's working but where is the 

    ISNULL(SUM(CASE WHEN [Voucher] = 'Purchase' 
    Can you please rearrange the query as It would be really helpful for me to understand.

    Saturday, August 6, 2016 7:43 AM
  • I think Tom's query is very clear.

    I guess he removed the CASE, because there is nothing that says there cannot be money both in the Inflow and Outflow columns at the same time.

    I would suggest that you run the CTEs in Tom's query and the one you had on their own to compare the differences to understand why the queries give different results. In that way you can learn something for the future.

    Permit me also to comment on the tables. I note that all columns are nullable. This is rarely a correct design. First of all, tables should have a primary key and a primary key cannot be nullable. But apart from that: NULL is a special value. When you make column nullable, you must also have an understanding of that that means.

    What does it mean if there is a row with no RecordID in tblSaleStatementCustomer? Or if the CustomerName is NULL? I can guess that the assumption is that only one of Inbound and Outbound is to have a value, but what if both are NULL? Both have a value? And what voucher types can their be? Seemlingly quite many since the column is nvarchar(255). What would a VoucherType of "Mommy is gone to the gas station" imply?

    These questions are not only academical. Most likely you are writing your queries under some assumptions, but you don't encode those assumptions in your tables, there will sooner or later be data that does not match your assumptions, and your queries will not be correct. Therefore you should have NOT NULL and other constraints to state your assumptions. Your assumptions about the business may be wrong, but at least you preventing data that would cause incorrect results from your queries to be entered.

    Saturday, August 6, 2016 9:06 AM
  • I didn't use the Vendor (or the VoucherType) column because as I understood your database design, the value in Inflow is always added to the quantity balance and the value in Outflow is always subtracted.  So I just added Inflow and subtracted Outflow (after convertling any NULL's to 0) regardless of the value in Vendor.

    I second Erland's comment about NULLs.  When defining your tables, make them NOT NULL unless you really want that column to be able to contain NULL.  If you mark them NULL, then in all of your DML code, you need to be sure you properly handle any NULL values that are in your table.

    Tom

    Saturday, August 6, 2016 1:51 PM
  • If you're allowed to change the names and the structure of the tables, I would also drop the tbl prefix and use plural for the table names.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Saturday, August 6, 2016 6:24 PM
    Moderator