This query is not returning top 5

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

General SQL Server Forums

New to SQL Server Programming

This query is not returning top 5

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-11-03 : 17:22:16

The following query, is supposed to return only the 'top 5' categories of sales, based on sales amt. However, it is returning much more than 5. Perhaps someone can see what the problem is?

USE [PBS]
GO
/****** Object: StoredProcedure [dbo].[_USP_QuoteMonth_Top5_only5] Script Date: 11/03/2010 17:12:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* --------------------------------------------------------------------------------------------------------*/
ALTER PROCEDURE [dbo].[_USP_QuoteMonth_Top5]
(
@FromDate VARCHAR(25),
@ToDate VARCHAR(25),
@CUSTNAME varchar(250),
@Department varchar(4000),
@Category varchar(4000),
@SalesTypes Varchar(50),
@Debug bit = 0
)
AS
/*
UNIT TESTING
============
EXEC [_USP_QuoteMonth_Top5_only5] '01/01/2009', '01/31/2009', 'Northern Manor Multicare center', 'ALL' , 'ALL', '1'
EXEC [_USP_QuoteMonth_Top5_only5] '01/04/2009', '01/08/2009', 'Northern Manor Multicare center', 'ALL' , 'ALL', '1', @Debug = 1

EXEC [_USP_QuoteMonth_Top5_only5] '01/01/2009', '12/31/2009', 'Northern Manor Multicare center', 'ALL' , 'ALL', '1'
EXEC [_USP_QuoteMonth_Top5_only5] '01/01/2009', '12/31/2009', 'Northern Manor Multicare center', 'ALL' , 'ALL', '1', @Debug = 1
*/

BEGIN
DECLARE @SQL varchar(MAX)
DECLARE @SQL2 varchar(MAX)

IF @CUSTNAME <> 'ALL'
BEGIN
SET @CustName = REPLACE(@CustName,',', ''',''')
SET @CustName = '''' + @CustName + ''''
END

IF @Department <> 'ALL'
BEGIN
SET @Department = REPLACE(@Department,',', ''',''')
SET @Department = '''' + @Department + ''''
END

IF @Category <> 'ALL'
BEGIN
SET @Category = REPLACE(@Category,',', ''',''')
SET @Category = '''' + @Category + ''''
END

SET @SalesTypes = REPLACE(@SalesTypes,',', ''',''')
SET @SalesTypes = '''' + @SalesTypes + ''''

-- Create temporary table for Budget
CREATE TABLE #Budget
(
ID datetime,
CUSTNMBR char(15),
UserCatLongDescr char(255),
Budget money
)

DECLARE @iterator int
DECLARE @d1 datetime, @d2 datetime, @diff int
SET @d1 = CAST(@FromDate as datetime)
SET @d2 = CAST(@ToDate as datetime)

SET @diff = DATEDIFF(m, @d1, @d2) + 1
SET @iterator = 1

WHILE @iterator <= @diff
BEGIN
IF @Debug = 1
PRINT CAST(CAST(DATEPART(YEAR, @d1) as varchar) + '-' + CAST(DATEPART(MONTH, @d1) as varchar) + '-1' as datetime)

SET @SQL = 'SELECT CAST(''' + CAST(DATEPART(YEAR, @d1) as varchar) + '-' + CAST(DATEPART(MONTH, @d1) as varchar) + '-1' + ''' as datetime) as ID,
CustomerBudget.CUSTNMBR, IV40600.UserCatLongDescr, SUM(BudgetAmount) as Budget
FROM CustomerBudget INNER JOIN RM00101 ON CustomerBudget.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN IV40600 ON CustomerBudget.USCATVAL = IV40600.USCATVAL
WHERE ''' + CAST(DATEPART(YEAR, @d1) as varchar) + '-' + CAST(DATEPART(MONTH, @d1) as varchar) + '-1' + ''' BETWEEN BudgetDateFrom AND BudgetDateTo '

IF @CUSTNAME <> 'ALL'
BEGIN
SET @SQL = @SQL + ' AND RM00101.CUSTNAME IN (' + @CUSTNAME + ')'
END

IF @Category <> 'ALL'
BEGIN
SET @SQL = @SQL + ' AND CustomerBudget.USCATVAL IN (' + @Category+ ')'
END

IF @Department <> 'ALL'
BEGIN
SET @SQL = @SQL + ' AND CustomerBudget.Dept IN (' + @Department + ')'
END
--ELSE
--BEGIN
-- SET @SQL = @SQL + ' AND CustomerBudget.Dept IS NULL'
--END

SET @SQL = @SQL + ' GROUP BY CustomerBudget.CUSTNMBR, IV40600.UserCatLongDescr'

INSERT INTO #Budget
EXEC (@SQL)

SET @d1 = DATEADD(MONTH, 1, @d1)
SET @iterator = @iterator + 1
END

IF @Debug = 1
SELECT * FROM #Budget

-- Querying Raw Data
SET @SQL = 'SELECT t1.CUSTNMBR, t2.XTNDPRCE AS XTNDPRCE, t2.QUANTITY AS QUANTITY, DATEADD(month, DATEDIFF(month, 0, t1.DOCDATE), 0) AS ID,
t6.UserCatLongDescr, t5.ITEMNMBR, t5.ITEMDESC, NULL AS Budget
FROM dbo.SOP10100 AS t1
INNER JOIN dbo.SOP10200 AS t2 ON t1.SOPNUMBE = t2.SOPNUMBE
LEFT JOIN dbo.SOP10106 AS t4 ON t1.SOPTYPE = t4.SOPTYPE
AND t1.SOPNUMBE = t4.SOPNUMBE
INNER JOIN dbo.IV00101 AS t5 ON t2.ITEMNMBR = t5.ITEMNMBR
INNER JOIN dbo.IV40600 AS t6 ON t5.ITMGEDSC = t6.USCATVAL
INNER JOIN dbo.RM00101 AS t7 ON t7.CUSTNMBR = t1.CUSTNMBR
WHERE t1.DOCDATE >= ''' + CONVERT(varchar, @FromDate, 101) + '''
AND t1.DOCDATE <= ''' + CONVERT(varchar, @ToDate, 101) + '''
AND t1.VOIDSTTS=0
AND t2.QUANTITY>0 '

IF @Category <> 'ALL'
BEGIN
SET @SQL = @SQL + ' AND t5.ITMGEDSC in (' + @Category+ ')'
END

IF @Department <> 'ALL'
BEGIN
SET @SQL = @SQL + ' AND t4.USRDEF05 in (' + @Department + ')'
END

IF @CUSTNAME <> 'ALL'
BEGIN
SET @SQL = @SQL + ' AND t7.CUSTNAME in (' + @CUSTNAME + ')'
END

IF @SalesTypes <> ''
BEGIN
SET @SQL = @SQL + ' AND t1.SOPTYPE in (' + @SalesTypes + ')'
END

SET @SQL2 = REPLACE(@SQL, 'SOP10100', 'SOP30200')
SET @SQL = @SQL + ' UNION ALL ' + @SQL2

SET @SQL = @SQL + ' Order by t6.UserCatLongDescr, t5.ITEMDESC'

-- Create temporary table as container
CREATE TABLE #Temp
(
CUSTNMBR char(15),
XTNDPRCE numeric(19, 5),
QUANTITY numeric(19, 5),
ID datetime,
UserCatLongDescr char(255),
ITEMNMBR char(31),
ITEMDESC char(101),
Budget money NULL
)

IF @Debug = 1
PRINT @SQL

INSERT INTO #Temp
EXEC (@SQL)

--IF @Debug = 1
-- SELECT * FROM #Temp

-- Update the budget
UPDATE #Temp
SET Budget = (SELECT ISNULL(Budget, 0) FROM #Budget WHERE #Temp.ID = #Budget.ID
AND #Temp.CUSTNMBR = #Budget.CUSTNMBR AND #Temp.UserCatLongDescr = #Budget.UserCatLongDescr)

IF @Debug = 1
SELECT * FROM #Temp

-- Create temp table to store the Item Category
CREATE TABLE #Category
(
ID int IDENTITY(1,1),
UserCatLongDescr char(255),
)

INSERT INTO #Category (UserCatLongDescr)
SELECT DISTINCT UserCatLongDescr FROM #Temp

IF @Debug = 1
SELECT * FROM #Category

-- Create temp table to store the top 5 items for each category
CREATE TABLE #TopItem
(
ID int IDENTITY(1,1),
TotalAmount numeric(19, 5),
ITEMNMBR char(31),
UserCatLongDescr char(255),
ITEMDESC char(101)
)

DECLARE @count int
DECLARE @cat char(101)

SET @iterator = 1
SELECT @count = MAX(ID) FROM #Category
WHILE @iterator <= @count
BEGIN
SELECT @cat = UserCatLongDescr FROM #Category WHERE ID = @iterator

INSERT INTO #TopItem (TotalAmount, ITEMNMBR, UserCatLongDescr, ITEMDESC)
SELECT TOP 5 SUM(XTNDPRCE) as Total, ITEMNMBR, @cat, ITEMDESC
FROM #Temp
WHERE UserCatLongDescr = @cat
GROUP BY ITEMNMBR, ITEMDESC
ORDER BY Total DESC

SET @iterator = @iterator + 1
END

IF @Debug = 1
SELECT * FROM #TopItem

-- Build the result table
CREATE TABLE #Result
(
CUSTNMBR char(15),
XTNDPRCE numeric(19, 5),
QUANTITY numeric(19, 5),
ID datetime,
UserCatLongDescr char(255),
ITEMNMBR char(31),
ITEMDESC char(101),
Budget money,
Months int,
CatTotalBudget money NULL,
TotalBudget money NULL
)

DECLARE @item char(31)
DECLARE @months int
DECLARE @tbudget money
DECLARE @cust char(15)
DECLARE @id datetime
DECLARE @iterator2 int
DECLARE @desc char(101)

SELECT TOP 1 @cust = UPPER(CUSTNMBR) FROM #Budget

SELECT @months = COUNT(DISTINCT ID) FROM #Budget

SET @iterator = 1
SELECT @count = MAX(ID) FROM #TopItem
WHILE @iterator <= @count
BEGIN
SELECT @item = ITEMNMBR, @cat = UserCatLongDescr, @desc = ITEMDESC
FROM #TopItem
WHERE ID = @iterator

INSERT INTO #Result
SELECT CUSTNMBR, SUM(XTNDPRCE), SUM(QUANTITY), ID, UserCatLongDescr, ITEMNMBR, ITEMDESC, Budget, @months, 0, 0
FROM #Temp
WHERE ITEMNMBR = @item
AND UserCatLongDescr = @cat
GROUP BY CUSTNMBR, ID, UserCatLongDescr, ITEMNMBR, ITEMDESC, Budget

-- refining data
SET @d1 = CAST(@FromDate as datetime)
SET @diff = DATEDIFF(m, @d1, @d2) + 1
SET @iterator2 = 1

WHILE @iterator2 <= @diff
BEGIN
SET @id = CAST(CAST(DATEPART(YEAR, @d1) as varchar) + '-' + CAST(DATEPART(MONTH, @d1) as varchar) + '-1' as datetime)

IF NOT EXISTS(SELECT * FROM #Result WHERE ID = @id
AND ITEMNMBR = @item
AND UserCatLongDescr = @cat
AND CUSTNMBR = @cust)
BEGIN

INSERT INTO #Result
VALUES (@cust, 0, 0, @id, @cat, @item, @desc,
(SELECT Budget FROM #Budget WHERE ID = @id AND CUSTNMBR = @cust AND UserCatLongDescr = @cat),
@months, 0, 0)

END

SET @d1 = DATEADD(MONTH, 1, @d1)
SET @iterator2 = @iterator2 + 1
END

-- increase iterator
SET @iterator = @iterator + 1
END

-- update category total budget
UPDATE #Result
SET CatTotalBudget = (SELECT SUM(#Budget.Budget) FROM #Budget WHERE #Budget.UserCatLongDescr = #Result.UserCatLongDescr)

-- update the total budget
SELECT @tbudget = SUM(Budget) FROM #Budget WHERE UserCatLongDescr IN (SELECT DISTINCT UserCatLongDescr FROM #Result)
UPDATE #Result
SET TotalBudget = @tbudget

-- return result table
SELECT * FROM #Result
ORDER BY UserCatLongDescr, ITEMDESC, ID

DROP TABLE #Budget
DROP TABLE #Temp
DROP TABLE #Category
DROP TABLE #TopItem
DROP TABLE #Result
END

Sachin.Nand

2937 Posts

Posted - 2010-11-04 : 03:18:38

I think you have a problem here

quote:
SELECT @count = MAX(ID) FROM #Category

What is the value of @count?

PBUH

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-11-04 : 11:56:43

This seems to be the problem, it is not getting to 5.

quote:
Originally posted by Sachin.Nand

I think you have a problem here

quote:
SELECT @count = MAX(ID) FROM #Category

What is the value of @count?

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-11-04 : 13:19:35

So I had asked you what is the value of @count?
Because based on value of @count the loop is being iterated that ma.So each time it inserts top 5 records in the table #TopItem.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources