Results 1 to 2 of 2

Thread: SQL Command

  1. #1
    Join Date
    May 2008
    Posts
    14

    Unanswered: SQL Command

    Hi There,

    I have a command that links 4 tables together that works ok. I really need to group a few fields to limit the records being returned.

    I want to make the "Item_Location_File.QtyOnHandPrimaryUn as QTY" field a sum and I need group the Item_Cost_File.LedgType.


    HTML Code:
    SELECT DISTINCT
    Item_Master.IdentifierShortItem,
    Item_Master.Identifier2ndItem, 
    Item_Master.DescriptionLine1,
    
    
    
    
    Item_Location_File.QtyOnHandPrimaryUn as QTY, 
    Item_Location_File.CostCenter,
    Item_Location_File.IdentifierShortItem,
    
    
    Item_Cost_File.IdentifierShortItem,
    Item_Cost_File.AmountUnitCost AS COST,
    Item_Cost_File.LedgType,
    Item_Cost_File.CostCenter,
    
    
    
    Sales_Order_Detail_File.IdentifierShortItem,
    Sales_Order_Detail_File.PurchasingReportCode1,
    Sales_Order_Detail_File.DtForGlAndVouch1 AS InvoiceDate
    
    
    
    FROM Item_Master, Item_Cost_File, Item_Location_File, Sales_Order_Detail_File
    
    
    
    WHERE 
    
    Item_Master.IdentifierShortItem = 27814
    AND
    
    Item_Cost_File.LedgType = '07'
    AND
    
    Item_Cost_File.CostCenter in ('         20', '         C20', '         21', '         C21')
    
    
    AND
    Item_Location_File.CostCenter in ('         20', '         C20', '         21', '         C21')
    
    AND
    
    Item_Master.IdentifierShortItem =Item_Location_File.IdentifierShortItem
    AND   Item_Master.IdentifierShortItem = 
    Item_Cost_File.IdentifierShortItem
    AND Item_Master.IdentifierShortItem = 
    Sales_Order_Detail_File.IdentifierShortIteM
    
    
    


    Thanks in advance

    Darren

  2. #2
    Join Date
    Nov 2009
    Posts
    9

    Lightbulb

    Darren,

    Are you trying to do the grouping on the SQL side or within Crystal Reports. Given the SQL above, you can create a CR Running Total which will sum the value of QTY. You could also create a group for LedgType and then use the Record Selector to limit what records are displayed in the group. I hope this helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •