productcode   itemname          supplierprice  BeginInventory   Purchases TransfersIn TransfersOut SoldItem
0005478652    Dairy Cream 50g        85.00          0              25         0            0          20
0005478652    Dairy Cream 50g        85.00          0              20         0            0          10
8562253378    Cotton Buds small      35.00          20             20         5            4          41
7896345853    Coca Cola              12.00          30             50         35           0          20
7896345853    Coca Cola              12.00          20             30         4            0          25

[b]productcode  itemname          supplierprice  BeginInventory   Purchases TransfersIn TransfersOut SoldItem[/v]
0005478652      Dairy Cream 50g        85.00          0              45         0            0          30
8562253378      Cotton Buds small      35.00          20             20         5            4          41
7896345853      Coca Cola              12.00          50             80         39           0          45

USE [MFR_Merchandise_Dec_2012]
GO
/****** Object:  StoredProcedure [dbo].[Show_all_items_DateView]    Script Date: 01/17/2013 16:10:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Show_all_items_DateView]
	-- Add the parameters for the stored procedure here
	
@fromdate as DATETIME,
@todate as DATETIME

As
Begin

	SELECT p.productcode,
               itemname,
               supplierprice,
               BeginInventory,
               Purchases,
               TransfersIn,
               TransfersOut,
               SoldItem
               --COALESCE(BeginInventory,0) + COALESCE(Purchases,0) + COALESCE(TransfersIn,0) - COALESCE(TransfersOut,0) - COALESCE(Sold,0) AS RunningOnhand
                  
    FROM tbl_product_list p 
         
         LEFT JOIN  (SELECT prodcode,SUM(qty) AS BeginInventory
                    FROM PO_detail
                    WHERE (memo='beginning') AND podate >= @fromdate AND podate < @todate + 1
                    GROUP BY prodcode)i
         ON i.prodcode = p.productcode
         
         LEFT JOIN (SELECT prodcode,SUM(qty) AS Purchases
                    FROM PO_detail
                    WHERE (memo='purchases') AND podate >= @fromdate AND podate < @todate + 1
                    GROUP BY prodcode)po
         ON po.prodcode= p.productcode
         
         LEFT JOIN (SELECT prodcode,
                           SUM(CASE WHEN TransferInOutMemo = 'IN' THEN qty END) AS TransfersIn,
                           SUM(CASE WHEN TransferInOutMemo = 'OUT' THEN qty END) AS TransfersOut
                    FROM tbl_product_transfer_detail
                    WHERE transdate >= @fromdate AND transdate < @todate + 1
                    GROUP BY prodcode)pts
         ON pts.prodcode= p.productcode
         
         LEFT JOIN (SELECT productcode,SUM(qty) AS SoldItem
                    FROM tbl_pos_sales_detail
                    WHERE transdate >= @fromdate AND transdate < @todate + 1
                    GROUP BY productcode)psd
         ON psd.productcode= p.productcode
         
	LEFT JOIN  tbl_supplier s
		ON  s.suppliername=(SELECT s.suppliername
		WHERE s.suppliercode=p.supplierid)
			
		LEFT JOIN  tbl_prod_category c
		ON  c.categoryname=(SELECT c.categoryname
		WHERE c.catgoryid=p.category)
	
	ORDER BY p.itemname ASC
End

SELECT p.productcode,
       itemname,
       supplierprice,
       SUM(BeginInventory) AS BeginInventory,
       SUM(Purchases) AS Purchases,
       SUM(TransfersIn) AS TransfersIn,
       SUM(TransfersOut) AS TransfersOut,
       SUM(SoldItem) AS SoldItem
FROM
-- your "FROM" section here
GROUP BY
	p.productcode,
	itemname,
	supplierprice
ORDER BY p.itemname ASC	

...
  LEFT JOIN  (SELECT prodcode,SUM(qty) AS BeginInventory
                    FROM PO_detail
                    WHERE (memo='beginning') AND podate >= @fromdate AND podate < @todate + 1
                    GROUP BY prodcode)i
         ON i.prodcode = p.productcode
         
         LEFT JOIN (SELECT prodcode,SUM(qty) AS Purchases
                    FROM PO_detail
                    WHERE (memo='purchases') AND podate >= @fromdate AND podate < @todate + 1
                    GROUP BY prodcode)po
         ON po.prodcode= p.productcode
..

  LEFT JOIN  (SELECT prodcode,SUM(CASE WHEN memo='beginning' THEN qty ELSE 0 END) AS BeginInventory,
                    SUM(CASE WHEN memo='purchases' THEN qty ELSE 0 END) AS Purchases
                    FROM PO_detail
                    WHERE  podate >= @fromdate AND podate < @todate + 1
                    GROUP BY prodcode)i
         ON i.prodcode = p.productcode

quote:

---

Originally posted by James K

Add aggregation function (SUM) for the columns you want to add up and add a GROUP BY clause that has all the columns that are outside of the aggregates like shown below

SELECT p.productcode,
       itemname,
       supplierprice,
       SUM(BeginInventory) AS BeginInventory,
       SUM(Purchases) AS Purchases,
       SUM(TransfersIn) AS TransfersIn,
       SUM(TransfersOut) AS TransfersOut,
       SUM(SoldItem) AS SoldItem
FROM
-- your "FROM" section here
GROUP BY
	p.productcode,
	itemname,
	supplierprice
ORDER BY p.itemname ASC	

---

quote:

---

Originally posted by visakh16

the below two joins

...
  LEFT JOIN  (SELECT prodcode,SUM(qty) AS BeginInventory
                    FROM PO_detail
                    WHERE (memo='beginning') AND podate >= @fromdate AND podate < @todate + 1
                    GROUP BY prodcode)i
         ON i.prodcode = p.productcode
         
         LEFT JOIN (SELECT prodcode,SUM(qty) AS Purchases
                    FROM PO_detail
                    WHERE (memo='purchases') AND podate >= @fromdate AND podate < @todate + 1
                    GROUP BY prodcode)po
         ON po.prodcode= p.productcode
..

can be merged into 1

  LEFT JOIN  (SELECT prodcode,SUM(CASE WHEN memo='beginning' THEN qty ELSE 0 END) AS BeginInventory,
                    SUM(CASE WHEN memo='purchases' THEN qty ELSE 0 END) AS Purchases
                    FROM PO_detail
                    WHERE  podate >= @fromdate AND podate < @todate + 1
                    GROUP BY prodcode)i
         ON i.prodcode = p.productcode

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---