Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
please help me to create suppliers ledger in sql server 2005

Answered please help me to create suppliers ledger in sql server 2005

  • Saturday, May 12, 2012 10:14 AM
     
      Has Code
    Sign In to Vote
    0

    hi all

    i have two relational tables like

    I want to create supplier from these two tables

    1.Requisition                            RequisitionDetail

    ReceivingID  <----------------->  ReceivingID

    Date                                        ItemID

    SupplierName                          ItemName

                                                    AcceptQty

                                                    UnitPrice

                                                   TotalCost

    following is my query

    SELECT     TOP (100) PERCENT dbo.Requistion.ReceivingID, 
dbo.Requistion.Date, dbo.Requistion.SupplierName, 
dbo.RequisitionDetail.ItemName, dbo.RequisitionDetail.Accept,                      
 dbo.RequisitionDetail.UnitPrice, dbo.RequisitionDetail.Cost, 
dbo.RequisitionDetail.TotalCost AS TotalAmountFROM         dbo.Requistion INNER JOIN                      dbo.RequisitionDetail ON dbo.Requistion.ReceivingID = dbo.RequisitionDetail.ReceivingID AND                       dbo.Requistion.ReceivingID = dbo.RequisitionDetail.ReceivingID CROSS JOIN                      dbo.SuppliersGROUP
 BY dbo.Requistion.ReceivingID, dbo.Requistion.Date, 
dbo.Requistion.SupplierName, dbo.Requistion.OrderNo, 
dbo.Requistion.OrderDate,                       
dbo.RequisitionDetail.ItemName, dbo.RequisitionDetail.Accept, 
dbo.RequisitionDetail.UnitPrice, dbo.RequisitionDetail.Cost, 
dbo.RequisitionDetail.TotalCostORDER BY dbo.Requistion.ReceivingID

    now i want to create Suppliers Ledger date wise

    please help me
     

All Replies

  • Saturday, May 12, 2012 11:20 AM
     
     Answered Has Code
    Sign In to Vote
    0

    Hello,

    I don't have the tables + data to test it, but try it this way.

    SELECT R.ReceivingID, 
       R.Date,
       R.SupplierName, 
       RD.ItemName, 
       RD.Accept,                      
       RD.UnitPrice, 
       SUM(RD.Cost) AS CostSum, 
       SUM(RD.TotalCost) AS TotalAmount
FROM  dbo.Requistion AS R
      INNER JOIN
      dbo.RequisitionDetail AS RD
          ON R.ReceivingID = RD.ReceivingID AND
             R.ReceivingID = RD.ReceivingID 
GROUP BY R.ReceivingID, 
         R.Date, 
         R.SupplierName, 
         R.OrderNo, 
         R.OrderDate,                       
         RD.ItemName, 
         RD.Accept, 
         RD.UnitPrice, 
ORDER BY R.ReceivingID

    You should use alias like here R + RD.

    What do you suppose to do with the CROSS JOIN on supplier? The table isn't used in your script.

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing



     
  • Sunday, May 13, 2012 4:05 AM
     
     Answered
    Sign In to Vote
    0
    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html 

    Most of your data element names are vague garbage reserved words or verbs! You duplicated a join condition. Suppliers is never used in the query, but it will make a huge cross join. This is the usual pattern for this type of query.

    SELECT R.receipt_nbr, R.receipt_date, R.sup_name, 
           RD.item_name, RD.accepted_qty, RD.unit_price, RD.unit_cost
      FROM Requisitions AS R,
           Requisition_Details AS RD,
     WHERE R.receipt_nbr = RD.receipt_nbr 

    >> now I want to create Suppliers Ledger date wise <<

    That makes no sense and we have no spec. Also “date wise” is bad English. Try again if you really want help. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL