Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query result more than One?

Author  Topic 

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-01-20 : 19:52:17
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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-20 : 20:14:36
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

52326 Posts

Posted - 2013-01-21 : 01:31:19
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 - 2013-01-21 : 08:50:57
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 - 2013-01-21 : 08:54:01
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
   

- Advertisement -