locked
how to make Account Ledger with Views using sql server 2005 RRS feed

  • Question

  • hi

    I want to create Account Ledger as OpeningBalance+Debit-Credit of each account.The problem is that opBalance and Credit amount columns repeated with each row.actually i want to each row should be present a single transaction either Debit or Credit when there is made any transaction of such Debit and Credit Columns then the  amount will be showing in the resulted Debit and credit column and the other columns should have null values  when there is not made any transaction in such a columns.

    My query is as under

    SELECT     dbo.opAccount.AccountName, dbo.pStock.AccountID AS PurchaseNo, dbo.opAccount.AccountID, dbo.pmtView.Date, dbo.pmtView.VoucherNo, dbo.pmtView.Narration, 
                          dbo.opAccount.Balance AS OpBalance, dbo.pmtView.Balance AS Debit, dbo.pStock.TotalCost AS Credit, ISNULL(SUM(dbo.opAccount.OpCredit), 0) 
                          - ISNULL(SUM(dbo.opAccount.OpDebit), 0) + ISNULL(SUM(dbo.pStock.TotalCost), 0) - ISNULL(SUM(dbo.pmtView.Balance), 0) AS Balance
    FROM         dbo.opAccount LEFT OUTER JOIN
                          dbo.pStock ON dbo.opAccount.AccountID = dbo.pStock.AccountID LEFT OUTER JOIN
                          dbo.pmtView ON dbo.opAccount.AccountID = dbo.pmtView.AccountID
    GROUP BY dbo.pmtView.Date, dbo.pmtView.VoucherNo, dbo.pmtView.Narration, dbo.opAccount.AccountID, dbo.opAccount.AccountName, dbo.pmtView.AccountID, 
                          dbo.pStock.AccountID, dbo.opAccount.OpDebit, dbo.opAccount.OpCredit, dbo.pmtView.Balance, dbo.pStock.TotalCost, dbo.opAccount.Balance

    I have three views and ddl is as under

    1.OpAccount

    USE [StoreDB]
    GO
    /****** Object:  View [dbo].[opAccount]    Script Date: 05/19/2012 12:18:05 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE VIEW [dbo].[opAccount]
    AS
    SELECT     AccountID, AccountName, OpDebit, OpCredit, Balance
    FROM         dbo.AccountsTable
    
    GO



    2. PStock

    USE [StoreDB]
    GO
    /****** Object:  View [dbo].[pStock]    Script Date: 05/19/2012 12:19:51 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE VIEW [dbo].[pStock]
    AS
    SELECT     dbo.Requistion.AccountID, dbo.RequisitionDetail.ItemID, dbo.RequisitionDetail.ItemName, dbo.RequisitionDetail.Accept AS Purchase, 
                          dbo.RequisitionDetail.TotalCost
    FROM         dbo.Requistion INNER JOIN
                          dbo.RequisitionDetail ON dbo.Requistion.ReceivingID = dbo.RequisitionDetail.ReceivingID
    GROUP BY dbo.RequisitionDetail.ItemID, dbo.RequisitionDetail.ItemName, dbo.Requistion.AccountID, dbo.RequisitionDetail.TotalCost, dbo.RequisitionDetail.Accept
    
    GO

    3.pmtView

    USE [StoreDB]
    GO
    /****** Object:  View [dbo].[pmtView]    Script Date: 05/19/2012 12:20:54 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE VIEW [dbo].[pmtView]
    AS
    SELECT     dbo.Payment.VoucherNo, dbo.Payment.Date, dbo.PaymentDetail.AccountID, dbo.PaymentDetail.AccountName, dbo.PaymentDetail.Narration, 
                          dbo.PaymentDetail.Balance, dbo.PaymentDetail.Dr, dbo.PaymentDetail.Cr
    FROM         dbo.Payment INNER JOIN
                          dbo.PaymentDetail ON dbo.Payment.VoucherNo = dbo.PaymentDetail.VoucherNo
    
    GO



    Saturday, May 19, 2012 7:31 AM

Answers

  • I don't really understand what you are asking for, but so much I can say that your query is not correct.

    Here is first a cleaned-up version of your query and views:

    SELECT     A.AccountName, S.AccountID AS PurchaseNo, A.AccountID, pmt.Date,
               pmt.VoucherNo, pmt.Narration,
               A.Balance AS OpBalance, pmt.Balance AS Debit, S.TotalCost AS Credit,
               ISNULL(SUM(A.OpCredit), 0) - ISNULL(SUM(A.OpDebit), 0) +
               ISNULL(SUM(S.TotalCost), 0) - ISNULL(SUM(pmt.Balance), 0) AS Balance
    FROM       dbo.opAccount A
    LEFT JOIN  dbo.pStock S ON A.AccountID = S.AccountID
    LEFT JOIN  dbo.pmtView ON A.AccountID = pmt.AccountID
    GROUP BY   pmt.Date, pmt.VoucherNo, pmt.Narration, A.AccountID, A.AccountName, pmt.AccountID,
               S.AccountID, A.OpDebit, A.OpCredit, pmt.Balance, S.TotalCost, A.Balance

    SELECT     dbo.Requistion.AccountID, RD.ItemID, RD.ItemName, RD.Accept AS Purchase,
               RD.TotalCost
    FROM       dbo.Requistion R
    JOIN       dbo.RequisitionDetail RD ON dbo.Requistion.ReceivingID = RD.ReceivingID
    -- GROUP BY RD.ItemID, RD.ItemName, dbo.Requistion.AccountID, RD.TotalCost, RD.Accept

    SELECT     P.VoucherNo, P.Date, PD.AccountID, PD.AccountName, PD.Narration,
               PD.Balance, PD.Dr, PD.Cr
    FROM       dbo.Payment P
    JOIN       dbo.PaymentDetail ON P.VoucherNo = PD.VoucherNo

    (I've commented out the GROUP BY from the pStock view, because it serves no purpose.)

    Say that for a single account you have 3 requisitions with 4 detail rows each, and 8 payments with a total of 30 detail rows. In that case, how many rows will this query return for this account?

    SELECT     A.AccountName, S.AccountID AS PurchaseNo, A.AccountID, pmt.Date,
               pmt.VoucherNo, pmt.Narration,
               A.Balance AS OpBalance, pmt.Balance AS Debit, S.TotalCost AS Credit
    FROM       dbo.opAccount A
    LEFT JOIN  dbo.pStock S ON A.AccountID = S.AccountID
    LEFT JOIN  dbo.pmtView ON A.AccountID = pmt.AccountID

    (That is the same query as above, but without the GROUP BY).

    The answer is: 360, 3*4*30. That is, every payment-detail row is combined with every requisition-detail row. As you realise, the outcome of this is pure garbage.

    There is another thing in the query that looks spooky:

       SELECT  A.AccountName, S.AccountID AS PurchaseNo, A.AccountID

    S.AccountID and A.AccountID have the same value, except when the account has no requisitions at all, in which case S.AccountID will be NULL. In any case, I cannot understand how an AccountID can be a PurchaseNo.

    And unfortunately, this is aomething I would need to have sorted out before I can suggest something better. Could it be that you intended to include another column from the Requisions table?

    Generally, for this type of questions it helps if you post:

    o  CREATE TABLE statements for your tables.
    o  INSERT statements with sample data.
    o  The desired result given the sample.
    o  Which version of SQL Server you are using.

    I realise that with five tables it will take some time to compose a complete script. But this kind of information is extremely helpful. It helps to clarify what you are looking for, and it makes it easy to copy and paste to develop a tested solution.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Stephanie Lv Monday, May 21, 2012 6:35 AM
    • Marked as answer by Stephanie Lv Monday, June 4, 2012 9:12 AM
    Saturday, May 19, 2012 9:26 AM

All replies

  • I don't really understand what you are asking for, but so much I can say that your query is not correct.

    Here is first a cleaned-up version of your query and views:

    SELECT     A.AccountName, S.AccountID AS PurchaseNo, A.AccountID, pmt.Date,
               pmt.VoucherNo, pmt.Narration,
               A.Balance AS OpBalance, pmt.Balance AS Debit, S.TotalCost AS Credit,
               ISNULL(SUM(A.OpCredit), 0) - ISNULL(SUM(A.OpDebit), 0) +
               ISNULL(SUM(S.TotalCost), 0) - ISNULL(SUM(pmt.Balance), 0) AS Balance
    FROM       dbo.opAccount A
    LEFT JOIN  dbo.pStock S ON A.AccountID = S.AccountID
    LEFT JOIN  dbo.pmtView ON A.AccountID = pmt.AccountID
    GROUP BY   pmt.Date, pmt.VoucherNo, pmt.Narration, A.AccountID, A.AccountName, pmt.AccountID,
               S.AccountID, A.OpDebit, A.OpCredit, pmt.Balance, S.TotalCost, A.Balance

    SELECT     dbo.Requistion.AccountID, RD.ItemID, RD.ItemName, RD.Accept AS Purchase,
               RD.TotalCost
    FROM       dbo.Requistion R
    JOIN       dbo.RequisitionDetail RD ON dbo.Requistion.ReceivingID = RD.ReceivingID
    -- GROUP BY RD.ItemID, RD.ItemName, dbo.Requistion.AccountID, RD.TotalCost, RD.Accept

    SELECT     P.VoucherNo, P.Date, PD.AccountID, PD.AccountName, PD.Narration,
               PD.Balance, PD.Dr, PD.Cr
    FROM       dbo.Payment P
    JOIN       dbo.PaymentDetail ON P.VoucherNo = PD.VoucherNo

    (I've commented out the GROUP BY from the pStock view, because it serves no purpose.)

    Say that for a single account you have 3 requisitions with 4 detail rows each, and 8 payments with a total of 30 detail rows. In that case, how many rows will this query return for this account?

    SELECT     A.AccountName, S.AccountID AS PurchaseNo, A.AccountID, pmt.Date,
               pmt.VoucherNo, pmt.Narration,
               A.Balance AS OpBalance, pmt.Balance AS Debit, S.TotalCost AS Credit
    FROM       dbo.opAccount A
    LEFT JOIN  dbo.pStock S ON A.AccountID = S.AccountID
    LEFT JOIN  dbo.pmtView ON A.AccountID = pmt.AccountID

    (That is the same query as above, but without the GROUP BY).

    The answer is: 360, 3*4*30. That is, every payment-detail row is combined with every requisition-detail row. As you realise, the outcome of this is pure garbage.

    There is another thing in the query that looks spooky:

       SELECT  A.AccountName, S.AccountID AS PurchaseNo, A.AccountID

    S.AccountID and A.AccountID have the same value, except when the account has no requisitions at all, in which case S.AccountID will be NULL. In any case, I cannot understand how an AccountID can be a PurchaseNo.

    And unfortunately, this is aomething I would need to have sorted out before I can suggest something better. Could it be that you intended to include another column from the Requisions table?

    Generally, for this type of questions it helps if you post:

    o  CREATE TABLE statements for your tables.
    o  INSERT statements with sample data.
    o  The desired result given the sample.
    o  Which version of SQL Server you are using.

    I realise that with five tables it will take some time to compose a complete script. But this kind of information is extremely helpful. It helps to clarify what you are looking for, and it makes it easy to copy and paste to develop a tested solution.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Stephanie Lv Monday, May 21, 2012 6:35 AM
    • Marked as answer by Stephanie Lv Monday, June 4, 2012 9:12 AM
    Saturday, May 19, 2012 9:26 AM
  • thanks for your kind of help but when i execute your above query it give me following error.

    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "pmt.AccountID" could not be bound.
    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "pmt.Date" could not be bound.
    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "pmt.VoucherNo" could not be bound.
    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "pmt.Narration" could not be bound.
    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "pmt.Balance" could not be bound.

    Saturday, May 19, 2012 9:47 AM
  • The query I posted, was the same as yours. I only cleaned it up to make it easier to read. It seems that I forgot to add an alias. But since the query was for display only it is immaterial.

    It is much more important that you read the text so that you understand why the query does not work. And to get further help, you need to consider the questions I raise at the of my post.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, May 19, 2012 12:08 PM