Help with aggregate query - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Help with aggregate query

Author

Topic

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2013-12-25 : 11:19:13

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-12-25 : 12:47:15

[code]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[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2013-12-26 : 01:40:23

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-26 : 03:50:48

[code]
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
[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-12-26 : 06:09:04

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

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2013-12-26 : 06:43:06

Thank you Peso and Visakh. Two different approach.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources