I have this query that produces the following result:productcode itemname supplierprice BeginInventory Purchases TransfersIn TransfersOut SoldItem0005478652 Dairy Cream 50g 85.00 0 25 0 0 200005478652 Dairy Cream 50g 85.00 0 20 0 0 108562253378 Cotton Buds small 35.00 20 20 5 4 417896345853 Coca Cola 12.00 30 50 35 0 207896345853 Coca Cola 12.00 20 30 4 0 25
I want to make those results(marked in red) that appears twice or more to appear only once. The quantity value of each field for BeginInventory,purchases,TransfersIn,TransfersOut,SoldItem will be sum up to make it one. So the result must be:[b]productcode itemname supplierprice BeginInventory Purchases TransfersIn TransfersOut SoldItem[/v]0005478652 Dairy Cream 50g 85.00 0 45 0 0 308562253378 Cotton Buds small 35.00 20 20 5 4 417896345853 Coca Cola 12.00 50 80 39 0 45
Here is my query so far:USE [MFR_Merchandise_Dec_2012]GO/****** Object: StoredProcedure [dbo].[Show_all_items_DateView] Script Date: 01/17/2013 16:10:12 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[Show_all_items_DateView] -- Add the parameters for the stored procedure here @fromdate as DATETIME,@todate as DATETIMEAsBegin 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 ASCEnd
Thank you!-------------------------------------------------------------------------------------------------------Learning MS SQL Server 2008