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 2000 Forums
 SQL Server Development (2000)
 Help with aggregate query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mayoorsubbu
Yak Posting Veteran

India
94 Posts

Posted - 12/25/2013 :  11:19:13  Show Profile  Reply with Quote
Dear all,
I have three tables in my SQL server 2000 database like so
I have three tables like so

ItemMaster

Itemcode PartNo Description
12345 KU7 Blah Blah
34567 KAU30 Tester
98765 UPAS1M Speedometer


ItemSecondary

PrimaryItemcode SecondaryCode
12345 776564
12345 435543
12345 324456
12345 98765

StockMaster

Itemcode QtyInStk
12345 20
776564 10
435543 5
324456 9
98765 11

I want a result as under

itemcode stk inLieuCode
12345 55 776564, 435543, 324456, 98765

Edited by - mayoorsubbu on 12/25/2013 11:21:58

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 12/25/2013 :  12:47:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SET NOCOUNT ON
GO

CREATE TABLE	dbo.ItemMaster
		(
			ItemCode INT,
			PartNo VARCHAR(10)
		)

CREATE TABLE	dbo.ItemSecondary
		(
			PrimaryItemCode INT,
			SecondaryCode INT
		)

CREATE TABLE	dbo.StockMaster
		(
			ItemCode INT,
			QtyInStk INT
		)
GO

INSERT	dbo.ItemMaster
	(
		ItemCode,
		PartNo
	)
SELECT	12345, 'KU7' UNION ALL
SELECT	34567, 'KAU30' UNION ALL
SELECT	98765, 'UPAS1M'

INSERT	dbo.ItemSecondary
	(
		PrimaryItemCode,
		SecondaryCode
	)
SELECT	12345, 776564 UNION ALL
SELECT	12345, 435543 UNION ALL	
SELECT	12345, 324456 UNION ALL
SELECT	12345, 98765

INSERT	dbo.StockMaster
	(
		ItemCode,
		QtyInStk
	)
SELECT	12345, 20 UNION ALL
SELECT	776564, 10 UNION ALL	
SELECT	435543, 5 UNION ALL
SELECT	324456, 9 UNION ALL
SELECT	98765, 11
GO
CREATE FUNCTION dbo.StringConcat
(
	@Primary INT
)
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE	@Data VARCHAR(8000)

	SELECT		@Data = ISNULL(@Data + ', ', '') + CAST(SecondaryCode AS VARCHAR(12))
	FROM		dbo.ItemSecondary
	WHERE		PrimaryItemCode = @Primary
	ORDER BY	SecondaryCode DESC

	RETURN	@Data
END
GO
/*
itemcode	 stk	 inLieuCode
12345	 55	 776564, 435543, 324456, 98765
*/
SELECT		m.ItemCode AS itemcode,
		MIN(v.QtyInStk) + SUM(w.QtyInStk) AS stk
INTO		#Temp
FROM		dbo.ItemMaster AS m
INNER JOIN	dbo.StockMaster AS v ON v.ItemCode = m.ItemCode
INNER JOIN	dbo.ItemSecondary AS s ON s.PrimaryItemCode = m.ItemCode
INNER JOIN	dbo.StockMaster AS w ON w.ItemCode = s.SecondaryCode
GROUP BY	m.ItemCode

SELECT	itemcode,
	stk,
	dbo.StringConcat(itemcode) AS inLieuCode
FROM	#Temp
GO

DROP FUNCTION dbo.StringConcat

DROP TABLE	dbo.ItemMaster,
		dbo.ItemSecondary,
		dbo.StockMaster,
		#Temp
GO



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

mayoorsubbu
Yak Posting Veteran

India
94 Posts

Posted - 12/26/2013 :  01:40:23  Show Profile  Reply with Quote
SwePeso,

First of thanks for the solution. Could you please explain as to why have you selected MIN(v.QtyInStk) in the query. "Select m.ItemCode AS itemcode, MIN(v.QtyInStk) + SUM(w.QtyInStk) AS stk"

Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/26/2013 :  03:50:48  Show Profile  Reply with Quote

CREATE PROC GetData
AS


IF OBJECT_ID('tempdb..#Result') IS NOT NULL
 DROP TABLE #Result

SELECT Itemcode,CAST(0 AS int) AS stk,CAST(NULL AS varchar(8000)) AS inLieuCode
INTO #Result
FROM dbo.ItemMaster

UPDATE r
SET r.Stk = TotalStk
FROM #Result r
INNER JOIN (
SELECT is.PrimaryItemCode,
SUM(QtyInStk) AS TotalStk
FROM dbo.ItemSecondary is
INNER JOIN dbo.StockMaster sm
ON sm.ItemCode = is.SecondaryCode
OR sm.ItemCode = is.PrimaryItemCode
GROUP BY is.PrimaryItemCode
)s
ON s.PrimaryItemCode = r.ItemCode



UPDATE r
SET r.inLieuCode = COALESCE(inLieuCode,'') + ',' + CAST(SecondaryCode AS varchar(10)) 
FROM #Result r
INNER JOIN dbo.ItemSecondary i
ON i.PrimaryItemCode = r.Itemcode

SELECT itemCode,stk,STUFF(inLieuCode,1,1,'') AS inLieuCode
FROM #result

GO


Then call it like

EXEC Getdata


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 12/26/2013 03:51:56
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 12/26/2013 :  06:09:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by mayoorsubbu

Could you please explain as to why have you selected MIN(v.QtyInStk) in the query.
It's the nature of the JOIN. The sum is built of all secondarycodes and the primarycodes (all just once).



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

mayoorsubbu
Yak Posting Veteran

India
94 Posts

Posted - 12/26/2013 :  06:43:06  Show Profile  Reply with Quote
Thank you Peso and Visakh. Two different approach.


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.06 seconds. Powered By: Snitz Forums 2000