SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query result more than One?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

adbasanta
Posting Yak Master

120 Posts

Posted - 01/20/2013 :  19:52:17  Show Profile  Reply with Quote
I have this query that produces the following result:


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


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          30
8562253378      Cotton Buds small      35.00          20             20         5            4          41
7896345853      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 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


Thank you!


-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

Edited by - adbasanta on 01/20/2013 19:55:40

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 01/20/2013 :  20:14:36  Show Profile  Reply with Quote
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	
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/21/2013 :  01:31:19  Show Profile  Reply with Quote
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/

Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 01/21/2013 :  08:50:57  Show Profile  Reply with Quote
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	




thank you James K!

These works!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 01/21/2013 :  08:54:01  Show Profile  Reply with Quote
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/





thank you visakh16!

These makes it more optimized!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000