Crosstab without defined column headings,

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

Transact-SQL (2000)

Crosstab without defined column headings,

Author

Topic

haw001
Starting Member

20 Posts

Posted - 2006-06-26 : 03:45:49

I'm trying to create a crosstab like view which returns the values to a single field. There are no common values which can be used as column headings.

I have a recordset with a single common field which can be grouped.

I would like to return a single record based on the grouped field which contains a varchar or memo field with the data from all the records concatonated into it. Preferably in the form of a view which I can use with other views

ex data:
Item PO Site Qty
00615 PO00S0924 KP 600
00615 PO00F925 WA 240
00616 PO00F925 WA 480
00616 PO00S0924 KP 2400
00618 PO00S0924 KP 300
00618 PO00F925 WA 60

Desired result:

Item Orders
00615 PO00S0924-KP-600,PO00F925-WA-240
00616 PO00F925-WA-480,PO00S0924-KP-2400
00618 PO00S0924-KP-300,PO00F925-WA-60

Thanks,

Mark

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-26 : 04:12:07

[code]-- Prepare test data
DECLARE @Temp TABLE
(
Item VARCHAR(5),
PO VARCHAR(9),
Site VARCHAR(2),
Qty SMALLINT
)

INSERT @Temp
SELECT '00615', 'PO00S0924', 'KP', 600 UNION ALL
SELECT '00615', 'PO00F925', 'WA', 240 UNION ALL
SELECT '00616', 'PO00F925', 'WA', 480 UNION ALL
SELECT '00616', 'PO00S0924', 'KP', 2400 UNION ALL
SELECT '00618', 'PO00S0924', 'KP', 300 UNION ALL
SELECT '00618', 'PO00F925', 'WA', 60

-- Prepare output data
declare @output table (Item VARCHAR(5), Orders VARCHAR(8000))

insert @output (Item)
select distinct Item
from @temp
order by Item

-- Do the work
DECLARE @WS VARCHAR(8000), @mi varchar(5), @ma varchar(5)

select @mi = min(Item),
@ma = max(Item)
from @output

while @mi <= @ma
begin
SELECT @WS = NULL

SELECT @WS = ISNULL(@WS + ',', '') + z.Orders
FROM (
SELECT PO + '-' + Site + '-' + CONVERT(VARCHAR, Qty) Orders
FROM @Temp
WHERE Item = @mi
) z

update @output
set Orders = @WS
where Item = @mi

select @mi = min(item)
from @output
where item > @mi
end

-- output denormalized data
select * from @output[/code]
Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-26 : 04:24:00

Where do you want to show the data?
If you use front end application, then do the concatenation there
Refer this also
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

haw001
Starting Member

20 Posts

Posted - 2006-06-27 : 00:38:13

Peter,

Thanks for the info. I have to admit that I haven't been able to follow it all the way through.

Substituting my tables into your example I have:

-- Prepare output data

declare @output table (ITEMNMBR VARCHAR(31), Orders VARCHAR(8000))
INSERT @OUTPUT(ITEMNMBR)
SELECT DISTINCT POP10110.ITEMNMBR
FROM TR.dbo.POP10110 POP10110 INNER JOIN
TR.dbo.POP10100 POP10100 ON POP10110.PONUMBER = POP10100.PONUMBER
WHERE (POP10110.POLNESTA IN (1, 2, 3)) AND (NOT (POP10100.POSTATUS IN (4, 5, 6))) AND LOCNCODE IN ('KP', 'WA') AND
POP10110.QTYUNCMTBASE <> 0
ORDER BY ITEMNMBR
-- Do the work
DECLARE @WS VARCHAR(8000), @mi varchar(5), @ma varchar(5)

select @mi = min(ITEMNMBR),
@ma = max(ITEMNMBR)
from @output

while @mi <= @ma
begin
SELECT @WS = NULL

SELECT @WS = ISNULL(@WS + ',', '') + z.Orders
FROM (
SELECT POP10110.PONUMBER + '-' + POP10110.LOCNCODE + '-' + CONVERT(VARCHAR, POP10110.QTYUNCMTBASE) Orders
FROM TR.dbo.POP10110 POP10110 INNER JOIN
TR.dbo.POP10100 POP10100 ON POP10110.PONUMBER = POP10100.PONUMBER
WHERE ITEMNMBR = @mi AND (POP10110.POLNESTA IN (1, 2, 3)) AND (NOT (POP10100.POSTATUS IN (4, 5, 6))) AND LOCNCODE IN ('KP', 'WA') AND
POP10110.QTYUNCMTBASE <> 0
) z

update @output
set Orders = @WS
where ITEMNMBR = @mi

select @mi = min(ITEMNMBR)
from @output
where ITEMNMBR > @mi
end

-- output denormalized data
select * from @output

The first part is pretty straight forward, and returns 806 rows.

The second part is where I get lost. I can see where @mi and @ma get the beginning and end values and the do while part, but the "SELECT @WS = ISNULL(@WS + ',', '')" part is a bit confusing.

Running the query returns

(806 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
etc, etc.

I stopped it after 18 minutes, but I assume, perhaps incorrectly, that there would be a (1 row(s) affected) for each 806 records.

Admitedly, I skipped the @temp building part and referenced the tables directly. Perhaps this is my problem?

Otherwise there must be an error in my interpretation because the run time is just too long to be practical.

Any help would be appreciated.

Thanks,

Mark

haw001
Starting Member

20 Posts

Posted - 2006-06-27 : 00:51:40

Madhivanan,

Thanks for the link, I will explore it further.

To answer your question, the destination applicatin is Crystal Reports. The report is based entirely on a SQL Server View, The View itself which combines serveral other Views.

I could do the concatination within Crystal, but the real issue I am having is how to populate a single record with values from many records which do not have a common value which could be used to define the columns.

If there is a way (and I'm sure there is!) to put the values from the first record into column 1, the second record into column 2, and so on, and then start back with column 1 in a new record every time the item field changed, then I could define say 20 columns which would be more than enough. I could then do the concatenation in Crystal because the columns would be defined.

Any help would be appreciated.

Mark

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 00:59:04

Yes. You are doing a massive inner join for each part in item number order. That's were the long run time come from. What I can see, is that you really would benefit to stage your data before concatenating the information.

-- Stage the data for speed
DECLARE @Stage TABLE
        (
		Item VARCHAR(15),
		Order VARCHAR(40)
        )

INSERT		@Stage
SELECT		POP10110.ITEMNMBR,
		POP10110.PONUMBER + '-' + POP10110.LOCNCODE + '-' + CONVERT(VARCHAR, POP10110.QTYUNCMTBASE)
FROM		TR.dbo.POP10110 POP10110
INNER JOIN	TR.dbo.POP10100 POP10100 ON POP10110.PONUMBER = POP10100.PONUMBER
WHERE		POP10110.POLNESTA IN (1, 2, 3)
		AND POP10100.POSTATUS NOT IN (4, 5, 6)
		AND LOCNCODE IN ('KP', 'WA')
		AND POP10110.QTYUNCMTBASE <> 0

-- Prepare output data
DECLARE @Output TABLE (Item VARCHAR(15), Orders VARCHAR(8000))

INSERT		@Output
		(
			Item
		)
SELECT DISTINCT	Item
FROM		@Stage

-- Do the work
DECLARE @Concat VARCHAR(8000), @MinItem VARCHAR(15), @MaxItem VARCHAR(15)

SELECT	@MinItem = MIN(Item),
	@MaxItem = MAX(Item)
FROM	@Output

WHILE @MinItem <= @MaxItem
	BEGIN
		SELECT	@Concat = NULL

		SELECT	@Concat = ISNULL(@Concat + ', ', '') + z.Order
		FROM  	(
				SELECT	Order
				FROM	@Stage
				WHERE	Item = @MinItem
			) z

		UPDATE	@Output
		SET 	Orders = @Concat
		WHERE	Item = @MinItem

		SELECT	@MinItem = MIN(Item)
		FROM	@Output
		WHERE	Item > @MinItem
	END

-- Output denormalized data
SELECT		*
FROM		@Output
ORDER BY	Item

Peter Larsson
Helsingborg, Sweden

haw001
Starting Member

20 Posts

Posted - 2006-06-27 : 02:12:41

By using and example from the link that Madhivanan provided, I am very nearly there.

The T-SQL:

/* Declare temporary tables */
DECLARE @sourceTbl TABLE
(
ITEMNMBR VARCHAR(15),
PO VARCHAR(15),
Site VARCHAR(3),
Qty SMALLINT
)
DECLARE @destinationTbl TABLE
(
ITEMNMBR VARCHAR(15),
Orders varchar(8000)
)

INSERT @sourceTbl
SELECT DISTINCT RTRIM(POP10110.ITEMNMBR) AS ITEMNMBR, RTRIM(POP10110.PONUMBER) AS PO, RTRIM(POP10110.LOCNCODE) AS Site, POP10110.QTYUNCMTBASE AS Qty
FROM TR.dbo.POP10110 POP10110 INNER JOIN
TR.dbo.POP10100 POP10100 ON POP10110.PONUMBER = POP10100.PONUMBER
WHERE (POP10110.POLNESTA IN (1, 2, 3)) AND (NOT (POP10100.POSTATUS IN (4, 5, 6))) AND LOCNCODE IN ('KP', 'WA') AND
POP10110.QTYUNCMTBASE <> 0

UNION SELECT DISTINCT RTRIM(POP10110.ITEMNMBR) AS ITEMNMBR, RTRIM(POP10110.PONUMBER) AS PO, RTRIM(POP10110.LOCNCODE) AS Site, POP10110.QTYUNCMTBASE AS Qty
FROM VIC.dbo.POP10110 POP10110 INNER JOIN
VIC.dbo.POP10100 POP10100 ON POP10110.PONUMBER = POP10100.PONUMBER
WHERE (POP10110.POLNESTA IN (1, 2, 3)) AND (NOT (POP10100.POSTATUS IN (4, 5, 6))) AND LOCNCODE IN ('VIC') AND
POP10110.QTYUNCMTBASE <> 0
ORDER BY ITEMNMBR ASC

/*Select * from @sourceTbl*/

DECLARE @LastItem varchar(15)
DECLARE @i varchar(15)
DECLARE @tempWords VARCHAR(8000)

/* I need LastItem to know when to stop iterating */
SET @LastItem =
(
SELECT TOP 1 ITEMNMBR
FROM @sourceTbl
ORDER BY ITEMNMBR DESC
)
SET @i =
(
SELECT TOP 1 ITEMNMBR
FROM @sourceTbl
ORDER BY ITEMNMBR ASC
)
WHILE @i <= @LastItem
BEGIN
SET @tempWords = ''
SELECT @tempWords =
CASE @tempWords
WHEN '' THEN PO + '-' + Site + '-' + CONVERT(VARCHAR, QTY)
ELSE @tempWords + ', ' + PO + '-' + Site + '-' + CONVERT(VARCHAR, QTY)
END
FROM @sourceTbl
WHERE Itemnmbr = @i
INSERT INTO @destinationTbl (ITEMNMBR, Orders)
VALUES (@i, @tempWords)
SET @i = @i + 1
END

SELECT *
FROM @destinationTbl

Result:

00615 PO00F925-WA-240, PO00S0924-KP-600, POM00926-VIC-360
616
617
618
619
620

The problem is that the Itemnmbr field is a varchar and could contain letters and leading 0's, and the SET @i= @i+1 converts it to a number and as result the first entry works, but ther remainder do not.

I need a way to either ad an ID field to the @sourcetbl that changes with every itemnmbr, or another way of selecting the next itemnmbr in the @sourcetbl other than SET @i = @i + 1

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 02:36:06

quote:
Originally posted by haw001

The problem is that the Itemnmbr field is a varchar and could contain letters and leading 0's, and the SET @i= @i+1 converts it to a number and as result the first entry works, but ther remainder do not.

I need a way to either ad an ID field to the @sourcetbl that changes with every itemnmbr, or another way of selecting the next itemnmbr in the @sourcetbl other than SET @i = @i + 1

The algorithm I posted earlier will take care of both your issues. With the algorithm you posted, what if ITEMNMBR is not consecutive (00615, 00616, 00799, 09154)? Then you would do a lot of loops for numbers not present. Give the algorithm in my post one more chance to solve both your questions. It has only one drawback. It is that the length of the concatenated orders can not exceed 8000 characters. All these algorithms that use VARCHAR has this drawback.

-- Stage the data for speed

SET NOCOUNT ON

DECLARE @Stage TABLE
        (
		Item VARCHAR(15),
		[Order] VARCHAR(40)
        )

INSERT		@Stage
SELECT		POP10110.ITEMNMBR,
		POP10110.PONUMBER + '-' + POP10110.LOCNCODE + '-' + CONVERT(VARCHAR, POP10110.QTYUNCMTBASE)
FROM		TR.dbo.POP10110 POP10110
INNER JOIN	TR.dbo.POP10100 POP10100 ON POP10110.PONUMBER = POP10100.PONUMBER
WHERE		POP10110.POLNESTA IN (1, 2, 3)
		AND POP10100.POSTATUS NOT IN (4, 5, 6)
		AND LOCNCODE IN ('KP', 'WA')
		AND POP10110.QTYUNCMTBASE <> 0
UNION    -- THIS WILL TAKE CARE OF THE DISTINCT OPERATION
SELECT		POP10110.ITEMNMBR,
		POP10110.PONUMBER + '-' + POP10110.LOCNCODE + '-' + CONVERT(VARCHAR, POP10110.QTYUNCMTBASE)
FROM		VIC.dbo.POP10110 POP10110
INNER JOIN	VIC.dbo.POP10100 POP10100 ON POP10110.PONUMBER = POP10100.PONUMBER
WHERE		POP10110.POLNESTA IN (1, 2, 3)
		AND POP10100.POSTATUS NOT IN (4, 5, 6)
		AND LOCNCODE = 'VIC'
		AND POP10110.QTYUNCMTBASE <> 0

-- Prepare output data
DECLARE @Output TABLE (Item VARCHAR(15), Orders VARCHAR(8000))

INSERT		@Output
		(
			Item
		)
SELECT DISTINCT	Item
FROM		@Stage

-- Do the work
DECLARE @Concat VARCHAR(8000), @MinItem VARCHAR(15), @MaxItem VARCHAR(15)

SELECT	@MinItem = MIN(Item),
	@MaxItem = MAX(Item)
FROM	@Output

WHILE @MinItem <= @MaxItem
	BEGIN
		SELECT	@Concat = ISNULL(@Concat + ', ', '') + z.[Order]
		FROM  	(
				SELECT	[Order]
				FROM	@Stage
				WHERE	Item = @MinItem
			) z

		UPDATE	@Output
		SET 	Orders = @Concat
		WHERE	Item = @MinItem

		SELECT	@MinItem = MIN(Item),
			@Concat = NULL
		FROM	@Output
		WHERE	Item > @MinItem
	END

-- Output denormalized data
SELECT		Item,
		Orders
FROM		@Output
ORDER BY	Item

And for speed? I average 250 ITEMNMBR per second for Output table on my laptop. When I tried 500,000 unique values in Stage table, I could not use table variable any more. Changing table variable to temporary table solved that problem. No speed difference.

Peter Larsson
Helsingborg, Sweden

haw001
Starting Member

20 Posts

Posted - 2006-06-28 : 04:42:01

Peter,

Million thanks, with minor changes (data types and lengths only) the query worked like a dream.

Exactly what I was after and the entire query runs in less than 1 sec for 800 rows.

Fantastic, thanks again.

Mark

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-28 : 05:08:40

quote:
Originally posted by haw001

Exactly what I was after and the entire query runs in less than 1 sec for 800 rows.

Fantastic, thanks again.

You're welcome.
18+ minutes down to 1 sec is more than 1,000 times faster! That's good news any day.

Peter Larsson
Helsingborg, Sweden

haw001
Starting Member

20 Posts

Posted - 2006-06-28 : 06:38:39

Peter

Just to stretch the friendship a little further, I'd like to exten the view a little to have a seperate line per site with the PO values concatinated per site.

I've modified your code a little which gets me close:

-- Stage the data for speed

SET NOCOUNT ON

DECLARE @Stage TABLE
(
Item VARCHAR(15),
Site VARCHAR (3),
[Order] VARCHAR(100)
)

INSERT @Stage
SELECT RTRIM(POP10110.ITEMNMBR),
RTRIM(POP10110.LOCNCODE),
RTRIM(POP10110.PONUMBER) + '-' + (CONVERT(VARCHAR(6), CONVERT(INT,POP10110.QTYUNCMTBASE)))
FROM TR.dbo.POP10110 POP10110
INNER JOIN TR.dbo.POP10100 POP10100 ON POP10110.PONUMBER = POP10100.PONUMBER
WHERE POP10110.POLNESTA IN (1, 2, 3)
AND POP10100.POSTATUS NOT IN (4, 5, 6)
AND LOCNCODE IN ('KP', 'WA')
AND POP10110.QTYUNCMTBASE <> 0
UNION -- THIS WILL TAKE CARE OF THE DISTINCT OPERATION
SELECT RTRIM(POP10110.ITEMNMBR),RTRIM(POP10110.LOCNCODE),
RTRIM(POP10110.PONUMBER) + '-' + (CONVERT(VARCHAR(6), CONVERT(INT,POP10110.QTYUNCMTBASE)))
FROM VIC.dbo.POP10110 POP10110
INNER JOIN VIC.dbo.POP10100 POP10100 ON POP10110.PONUMBER = POP10100.PONUMBER
WHERE POP10110.POLNESTA IN (1, 2, 3)
AND POP10100.POSTATUS NOT IN (4, 5, 6)
AND LOCNCODE = 'VIC'
AND POP10110.QTYUNCMTBASE <> 0

-- Prepare output data
DECLARE @Output TABLE (Item VARCHAR(15), Site VARCHAR(3), Orders VARCHAR(8000))

INSERT @Output
(
Item, Site
)
SELECT DISTINCT Item, Site
FROM @Stage

-- Do the work
DECLARE @Concat VARCHAR(8000), @MinItem VARCHAR(15), @MaxItem VARCHAR(15)

SELECT @MinItem = MIN(Item),
@MaxItem = MAX(Item)
FROM @Output

WHILE @MinItem <= @MaxItem
BEGIN
SELECT @Concat = ISNULL(@Concat + ', ', '') + z.[Order]
FROM (
SELECT [Order]
FROM @Stage
WHERE Item = @MinItem
) z

UPDATE @Output
SET Orders = @Concat
WHERE Item = @MinItem

SELECT @MinItem = MIN(Item),
@Concat = NULL
FROM @Output
WHERE Item > @MinItem
END

-- Output denormalized data
SELECT Item, Site,
Orders
FROM @Output
ORDER BY Item, Site

Results in:

00615 KP PO00S0924-600, POM00926-360, PO00F925-240
00615 VIC PO00S0924-600, POM00926-360, PO00F925-240
00615 WA PO00S0924-600, POM00926-360, PO00F925-240
00616 KP PO00S0924-2400, POM00926-2400, PO00F925-480
00616 VIC PO00S0924-2400, POM00926-2400, PO00F925-480
00616 WA PO00S0924-2400, POM00926-2400, PO00F925-480
00618 KP PO00S0924-300, POM00926-240, PO00F925-60

However I'd really like only the orders for that site to show in the concatenation.

I would think that one or more of the 'WHERE Item = @MinItem' will have to have 'AND site = (something)', but I'm not sure.

Only other issue I need to resolve is the speed in Crystal. I've wrapped the whole thing in a Stored Procedure and added the Stored Procedure in to Crystal, but for some reason it outputs very slowly. I can only guess that Crystal is evaluating the stored procedure once for every record coming from my view.

Is there any way that the process could be turned into a function which I could add to my view, or should I consider creating a temp table from the results and have the view use this?

I'm completely ignorant of functions, so I may be looking at this the wrong way.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-28 : 07:10:25

I think Crystal Reports can do the concatenating for you. I am not sure, haven't used CR since version 3.0.
But slow? Are you calling the SP for every row in the report? Call it once only, at REPORT_START event or something.

What are the datatypes for ITEMNMBR, LOCNCODE, PONUMBER and QTYUNCMTBASE, respectively?

But here is a generic solution that you can use in the future even if the preferences changes

As you can see, there are some minor changes in the algorithm to deal with multiple key columns. The differences are marked with red. Some things that are useful are marked with green.

DECLARE	@Stage TABLE
	(
		Item VARCHAR(15),
		Site VARCHAR(3),
		[Order] VARCHAR(100)
	)

INSERT		@Stage
		(
			Item,
			Site,
			[Order]
		)
SELECT		POP10110.ITEMNMBR,
		POP10110.LOCNCODE,
		POP10110.PONUMBER + '-' + CONVERT(VARCHAR, POP10110.QTYUNCMTBASE)
FROM		TR.dbo.POP10110 POP10110
INNER JOIN	TR.dbo.POP10100 POP10100 ON POP10110.PONUMBER = POP10100.PONUMBER
WHERE		POP10110.POLNESTA IN (1, 2, 3)
		AND POP10100.POSTATUS NOT IN (4, 5, 6)
		AND LOCNCODE IN ('KP', 'WA')
		AND POP10110.QTYUNCMTBASE <> 0
		-- AND SomeDateColumn BETWEEN ThisDate AND ThatDate
UNION SELECT	POP10110.ITEMNMBR,
		POP10110.LOCNCODE,
		POP10110.PONUMBER + '-' + CONVERT(VARCHAR, POP10110.QTYUNCMTBASE)
FROM		VIC.dbo.POP10110 POP10110
INNER JOIN	VIC.dbo.POP10100 POP10100 ON POP10110.PONUMBER = POP10100.PONUMBER
WHERE		POP10110.POLNESTA IN (1, 2, 3)
		AND POP10100.POSTATUS NOT IN (4, 5, 6)
		AND LOCNCODE = 'VIC'
		AND POP10110.QTYUNCMTBASE <> 0
		-- AND SomeDateColumn BETWEEN ThisDate AND ThatDate

DECLARE @Output TABLE
	(
		RowID INT IDENTITY(0, 1),
		Item VARCHAR(15),
		Site VARCHAR(3),
		Orders VARCHAR(8000)
	)

INSERT		@Output
		(
			Item,
			Site
		)
SELECT DISTINCT	Item,
		Site
FROM		@Stage

DECLARE @Concat VARCHAR(8000),
	@Item VARCHAR(15),
	@Site VARCHAR(3),
	@RowID INT

SELECT	@RowID = MAX(RowID)
FROM	@Output

WHILE @RowID >= 0
	BEGIN
		SELECT	@Item = Item,
			@Site = Site
		FROM	@Output
		WHERE	RowID = @RowID

		SELECT	@Concat = ISNULL(@Concat + ', ', '') + z.[Order]
		FROM	(
				SELECT	[Order]
				FROM	@Stage
				WHERE	Item = @Item
					AND Site = @Site
			) z

		UPDATE	@Output
		SET	Orders = @Concat
		WHERE	RowID = @RowID

		SELECT	@RowID = @RowID - 1,
			@Concat = NULL
	END

-- Output denormalized data
SELECT		Item,
		Site,
		Orders
FROM		@Output
ORDER BY	Item,
		Site

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-28 : 07:12:52

>>I think Crystal Reports can do the concatenating for you.

Yes. You need to make use of Formula fields

Madhivanan

Failing to plan is Planning to fail

haw001
Starting Member

20 Posts

Posted - 2006-06-28 : 09:17:32

Peter,

ITEMNMBR char(31), LOCNCODE char(11), PONUMBER char(31) and QTYUNCMTBASE numeric(19,5)

I reduced some of them in the query knowing what is in the data, probably not a good idea, but I know the limits of the data in the database. Converting QTYUNCMTBASE to INT and then varchar is just my quick and dirty way to get rid of the decimals.

The changes work exactly as I wanted them thanks. I should have thought to use a RowID. I couldn't figure out how to make the while loop work with site and MinItem. Nice and clean.

I could do the concatenation in Crystal, but the report is complex and to do so would require a lot of changes to the report. Doing the concatenation at the database level makes it simple.

The speed issue is still a strange one. The view runs in 8 seconds and produces 11000 rows with Item and Site in the results. The sp runs in 2 seconds and produces 1500 rows with item and site in the results. The view is left outer joined to the sp against both item and site. Nothing too complex that I can see, but it takes about ten minutes to run.

Thanks again for everything, it's been a great help.

Mark

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-28 : 09:57:29

If QTYUNCMTBASE is only positive numbers, use

POP10110.PONUMBER + '-' + CONVERT(VARCHAR, FLOOR(POP10110.QTYUNCMTBASE))

for cutting the decimals completely in one convert.

Is the view really needed? You know you don't have to insert the Item and Site into @Output from the @Stage table? You can do that from any table.

What is the VIEW composited of? What does it look like?

Peter Larsson
Helsingborg, Sweden

haw001
Starting Member

20 Posts

Posted - 2006-06-28 : 23:27:45

Peter,

CONVERT(VARCHAR, FLOOR(POP10110.QTYUNCMTBASE)) works well. Never used FLOOR before, will have to remember it.

The view is probably not needed. It brings together several other views which are used elsewhere to assemble on big report which puts everything together. The individual views were created over a period of time (and at varying levels of my experience) so not all are optimised, but they all contain the common elements ITEMNMBR and LOCATION (or WAREHOUSE which is interchangeable.

Main View:

CREATE VIEW dbo.STOCK_ANALYSIS
AS
SELECT TOP 100 PERCENT RTRIM(TR.dbo.IV00101.ITEMNMBR) AS ITEM, RTRIM(TR.dbo.IV00101.ITMSHNAM) AS BARCODE,
RTRIM(TR.dbo.IV00101.ITEMDESC) AS ITEM_DESC, TR.dbo.IV00101.ITEMTYPE, RTRIM(TR.dbo.IV00101.USCATVLS_1) AS CLASS,
RTRIM(TR.dbo.IV00101.USCATVLS_6) AS STATUS, TR.dbo.IV00101.CURRCOST, TR.dbo.IV00108.UOMPRICE AS SELL,
TR.dbo.IV00102.LOCNCODE AS LOCATION, dbo.QTY_ON_HAND.QUANTITY, dbo.QTY_ON_ORDER.ON_ORDER, dbo.SALES_HIST.LM_LY,
dbo.SALES_HIST.LM, dbo.SALES_HIST.MBL, dbo.SALES_HIST.M3AGO, dbo.SALES_HIST.M4AGO, dbo.SALES_HIST.M5AGO, dbo.SALES_HIST.M3SUM,
dbo.SALES_HIST.M4SUM, dbo.SALES_HIST.M6SUM, dbo.SALES_HIST.M12SUM, dbo.SALES_HIST.M6AGO
FROM TR.dbo.IV00108 INNER JOIN
TR.dbo.IV00101 ON TR.dbo.IV00108.ITEMNMBR = TR.dbo.IV00101.ITEMNMBR AND
TR.dbo.IV00108.PRCLEVEL = TR.dbo.IV00101.PRCLEVEL INNER JOIN
TR.dbo.IV00102 ON TR.dbo.IV00101.ITEMNMBR = TR.dbo.IV00102.ITEMNMBR LEFT OUTER JOIN
dbo.SALES_HIST ON TR.dbo.IV00102.ITEMNMBR = dbo.SALES_HIST.ITEMNMBR AND
TR.dbo.IV00102.LOCNCODE = dbo.SALES_HIST.LOCATION LEFT OUTER JOIN
dbo.QTY_ON_HAND ON TR.dbo.IV00102.ITEMNMBR = dbo.QTY_ON_HAND.ITEMNMBR AND
TR.dbo.IV00102.LOCNCODE = dbo.QTY_ON_HAND.LOCATION LEFT OUTER JOIN
dbo.QTY_ON_ORDER ON TR.dbo.IV00102.LOCNCODE = dbo.QTY_ON_ORDER.LOCATION AND
TR.dbo.IV00102.ITEMNMBR = dbo.QTY_ON_ORDER.ITEMNMBR
WHERE (TR.dbo.IV00108.FROMQTY = 1) AND (TR.dbo.IV00101.ITEMTYPE IN (1, 3)) AND (TR.dbo.IV00102.LOCNCODE IN ('KP', 'VIC', 'WA'))
ORDER BY TR.dbo.IV00101.ITEMNMBR

Sub Views:

CREATE VIEW SALES_HIST
AS
SELECT TOP 100 PERCENT ln.PRODUCT AS ITEMNMBR, ln.WAREHOUSE as LOCATION, ISNULL
((SELECT TOP 1 SUM(qty_picked)
FROM shipline b, shiphist c
WHERE b.product = ln.product AND b.WAREHOUSE = ln.WAREHOUSE AND c.packslip = b.packslip AND year(c.date_ship) * 12 + month(c.date_ship)
- 1 = year(getdate()) * 12 + month(getdate()) - 13
GROUP BY b.product), 0) AS LM_LY, ISNULL
((SELECT TOP 1 SUM(qty_picked)
FROM shipline b, shiphist c
WHERE b.product = ln.product AND b.WAREHOUSE = ln.WAREHOUSE AND c.packslip = b.packslip AND year(c.date_ship) * 12 + month(c.date_ship)
= year(getdate()) * 12 + month(getdate()) - 1
GROUP BY b.product), 0) AS LM, ISNULL
((SELECT TOP 1 SUM(qty_picked)
FROM shipline b, shiphist c
WHERE b.product = ln.product AND b.WAREHOUSE = ln.WAREHOUSE AND c.packslip = b.packslip AND year(c.date_ship) * 12 + month(c.date_ship)
= year(getdate()) * 12 + month(getdate()) - 2
GROUP BY b.product), 0) AS MBL, ISNULL
((SELECT TOP 1 SUM(qty_picked)
FROM shipline b, shiphist c
WHERE b.product = ln.product AND b.WAREHOUSE = ln.WAREHOUSE AND c.packslip = b.packslip AND year(c.date_ship) * 12 + month(c.date_ship)
= year(getdate()) * 12 + month(getdate()) - 3
GROUP BY b.product), 0) AS M3AGO, ISNULL
((SELECT TOP 1 SUM(qty_picked)
FROM shipline b, shiphist c
WHERE b.product = ln.product AND b.WAREHOUSE = ln.WAREHOUSE AND c.packslip = b.packslip AND year(c.date_ship) * 12 + month(c.date_ship)
= year(getdate()) * 12 + month(getdate()) - 4
GROUP BY b.product), 0) AS M4AGO, ISNULL
((SELECT TOP 1 SUM(qty_picked)
FROM shipline b, shiphist c
WHERE b.product = ln.product AND b.WAREHOUSE = ln.WAREHOUSE AND c.packslip = b.packslip AND year(c.date_ship) * 12 + month(c.date_ship)
= year(getdate()) * 12 + month(getdate()) - 5
GROUP BY b.product), 0) AS M5AGO, ISNULL
((SELECT TOP 1 SUM(qty_picked)
FROM shipline b, shiphist c
WHERE b.product = ln.product AND b.WAREHOUSE = ln.WAREHOUSE AND c.packslip = b.packslip AND year(c.date_ship) * 12 + month(c.date_ship)
= year(getdate()) * 12 + month(getdate()) - 6
GROUP BY b.product), 0) AS M6AGO, ISNULL
((SELECT TOP 1 SUM(qty_picked)
FROM shipline b, shiphist c
WHERE b.product = ln.product AND b.WAREHOUSE = ln.WAREHOUSE AND c.packslip = b.packslip AND
(year(c.date_ship) * 12 + month(c.date_ship)) between (year(getdate()) * 12 + month(getdate())-3)
and (year(getdate()) * 12 + month(getdate())-1)
GROUP BY b.product), 0) AS M3SUM, ISNULL
((SELECT TOP 1 SUM(qty_picked)
FROM shipline b, shiphist c
WHERE b.product = ln.product AND b.WAREHOUSE = ln.WAREHOUSE AND c.packslip = b.packslip AND
(year(c.date_ship) * 12 + month(c.date_ship)) between (year(getdate()) * 12 + month(getdate())-4)
and (year(getdate()) * 12 + month(getdate())-1)
GROUP BY b.product), 0) AS M4SUM, ISNULL
((SELECT TOP 1 SUM(qty_picked)
FROM shipline b, shiphist c
WHERE b.product = ln.product AND b.WAREHOUSE = ln.WAREHOUSE AND c.packslip = b.packslip AND
(year(c.date_ship) * 12 + month(c.date_ship)) between (year(getdate()) * 12 + month(getdate())-6)
and (year(getdate()) * 12 + month(getdate())-1)
GROUP BY b.product), 0) AS M6SUM, ISNULL
((SELECT TOP 1 SUM(qty_picked)
FROM shipline b, shiphist c
WHERE b.product = ln.product AND b.WAREHOUSE = ln.WAREHOUSE AND c.packslip = b.packslip AND
(year(c.date_ship) * 12 + month(c.date_ship)) between (year(getdate()) * 12 + month(getdate())-12)
and (year(getdate()) * 12 + month(getdate())-1)
GROUP BY b.product), 0) AS M12SUM
FROM dbo.SHIPLINE ln INNER JOIN
dbo.SHIPHIST sh ON ln.PACKSLIP = sh.PACKSLIP
WHERE (ln.WAREHOUSE IN ('VIC','KP','WA'))
GROUP BY ln.PRODUCT, ln.WAREHOUSE
ORDER BY ITEMNMBR

CREATE VIEW dbo.QTY_ON_HAND
AS
SELECT TOP 100 PERCENT ITEMNMBR, WAREHOUSE AS LOCATION, SUM(QUANTITY) AS QUANTITY
FROM (SELECT TOP 100 PERCENT dbo.BINLOCAT. PRODUCT AS ITEMNMBR, dbo.BINLOCAT.WAREHOUSE, SUM(dbo.BINLOCAT.QUANTITY)
AS QUANTITY
FROM dbo.BINLOCAT
GROUP BY dbo.BINLOCAT. PRODUCT, dbo.BINLOCAT.WAREHOUSE
UNION
SELECT TOP 100 PERCENT dbo.PICKDETL. PRODUCT AS ITEMNMBR, dbo.PICKDETL.WAREHOUSE, - SUM(dbo.PICKDETL.QTY_TOPICK)
AS QUANTITY
FROM dbo.PICKDETL
GROUP BY dbo.PICKDETL. PRODUCT, dbo.PICKDETL.WAREHOUSE) T
GROUP BY ITEMNMBR, WAREHOUSE
ORDER BY ITEMNMBR, WAREHOUSE

CREATE VIEW dbo.QTY_ON_ORDER
AS
SELECT POP10110.ITEMNMBR, POP10110.LOCNCODE AS LOCATION, SUM(POP10110.QTYUNCMTBASE) AS ON_ORDER
FROM TR.dbo.POP10110 POP10110 INNER JOIN
TR.dbo.POP10100 POP10100 ON POP10110.PONUMBER = POP10100.PONUMBER
WHERE (POP10110.POLNESTA IN (1, 2, 3)) AND (NOT (POP10100.POSTATUS IN (4, 5, 6))) AND LOCNCODE IN ('KP', 'WA') AND
POP10110.QTYUNCMTBASE <> 0
GROUP BY POP10110.ITEMNMBR, POP10110.LOCNCODE
UNION
SELECT POP10110.ITEMNMBR, POP10110.LOCNCODE AS LOCATION, SUM(POP10110.QTYUNCMTBASE) AS ON_ORDER
FROM VIC.dbo.POP10110 POP10110 INNER JOIN
VIC.dbo.POP10100 POP10100 ON POP10110.PONUMBER = POP10100.PONUMBER
WHERE (POP10110.POLNESTA IN (1, 2, 3)) AND (NOT (POP10100.POSTATUS IN (4, 5, 6))) AND LOCNCODE = 'VIC' AND POP10110.QTYUNCMTBASE <> 0
GROUP BY POP10110.ITEMNMBR, POP10110.LOCNCODE

The SALES_HIST view takes a long time to run because of the volume of data, and the values are static throughout the month, so I have moved it to a SP which creates a SALES_HIST table at the beginning of the month, but otherwise this is it.

Thanks again,

Mark

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-29 : 00:27:38

The view SALES_HIST can be optimized significantlly!
You are doing a lot of subqueries where all queries do the same stuff! And that takes time! It is just some month comparisons that differs. I will take a look on that too later today.

The view QTY_ON_ORDER can be merged with my previously posted algorithm since all fields are the same, even the grouping!

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-29 : 00:34:21

When I see joins that look like this, I start thinking about a date table.

year(c.date_ship) * 12 + month(c.date_ship) = year(getdate()) * 12 + month(getdate()) - 3

This join would more efficient as this, because it would not require the date_ship column to be run through 2 functions for each row, and would allow for the possibility of using an index on date_ship.


c.date_ship >=  dateadd(mm,datediff(mm,0,getdate())-3,0) and
c.date_ship <  dateadd(mm,datediff(mm,0,getdate())-2,0)

CODO ERGO SUM

haw001
Starting Member

20 Posts

Posted - 2006-06-29 : 00:44:58

To get it working for the moment, I took everything from the STOCK_ANALYSIS view and added to your query in one SP. Then changed the report to run off the SP. Now takes 10-12 seconds to run, so quite acceptable.

DECLARE @Stage TABLE
(
Item VARCHAR(15),
Site VARCHAR(3),
[Order] VARCHAR(100)
)

INSERT @Stage
(
Item,
Site,
[Order]
)
SELECT RTRIM(POP10110.ITEMNMBR),
POP10110.LOCNCODE,
RTRIM(SUBSTRING(POP10110.PONUMBER,PATINDEX('%[S,F,M]%',POP10110.PONUMBER),10)) + '-' + CONVERT(VARCHAR, FLOOR(POP10110.QTYUNCMTBASE))
FROM TR.dbo.POP10110 POP10110
INNER JOIN TR.dbo.POP10100 POP10100 ON POP10110.PONUMBER = POP10100.PONUMBER
WHERE POP10110.POLNESTA IN (1, 2, 3)
AND POP10100.POSTATUS NOT IN (4, 5, 6)
AND LOCNCODE IN ('KP', 'WA')
AND POP10110.QTYUNCMTBASE <> 0
-- AND SomeDateColumn BETWEEN ThisDate AND ThatDate
UNION SELECT RTRIM(POP10110.ITEMNMBR),
POP10110.LOCNCODE,
RTRIM(SUBSTRING(POP10110.PONUMBER,PATINDEX('%[S,F,M]%',POP10110.PONUMBER),10)) + '-' + CONVERT(VARCHAR, FLOOR(POP10110.QTYUNCMTBASE))
FROM VIC.dbo.POP10110 POP10110
INNER JOIN VIC.dbo.POP10100 POP10100 ON POP10110.PONUMBER = POP10100.PONUMBER
WHERE POP10110.POLNESTA IN (1, 2, 3)
AND POP10100.POSTATUS NOT IN (4, 5, 6)
AND LOCNCODE = 'VIC'
AND POP10110.QTYUNCMTBASE <> 0
-- AND SomeDateColumn BETWEEN ThisDate AND ThatDate

DECLARE @Output TABLE
(
RowID INT IDENTITY(0, 1),
Item VARCHAR(15),
Site VARCHAR(3),
Orders VARCHAR(8000)
)

INSERT @Output
(
Item,
Site
)
SELECT DISTINCT Item,
Site
FROM @Stage

DECLARE @Concat VARCHAR(8000),
@Item VARCHAR(15),
@Site VARCHAR(3),
@RowID INT

SELECT @RowID = MAX(RowID)
FROM @Output

WHILE @RowID >= 0
BEGIN
SELECT @Item = Item,
@Site = Site
FROM @Output
WHERE RowID = @RowID

SELECT @Concat = ISNULL(@Concat + ', ', '') + z.[Order]
FROM (
SELECT [Order]
FROM @Stage
WHERE Item = @Item
AND Site = @Site
) z

UPDATE @Output
SET Orders = @Concat
WHERE RowID = @RowID

SELECT @RowID = @RowID - 1,
@Concat = NULL
END

-- Output denormalized data

SELECT TOP 100 PERCENT RTRIM(TR.dbo.IV00101.ITEMNMBR) AS ITEM, RTRIM(TR.dbo.IV00101.ITMSHNAM) AS BARCODE,
RTRIM(TR.dbo.IV00101.ITEMDESC) AS ITEM_DESC, TR.dbo.IV00101.ITEMTYPE, RTRIM(TR.dbo.IV00101.USCATVLS_1) AS CLASS,
RTRIM(TR.dbo.IV00101.USCATVLS_6) AS STATUS, TR.dbo.IV00101.CURRCOST, TR.dbo.IV00108.UOMPRICE AS SELL,
TR.dbo.IV00102.LOCNCODE AS LOCATION, dbo.QTY_ON_HAND.QUANTITY, dbo.QTY_ON_ORDER.ON_ORDER, dbo.SALES_HIST.LM_LY,
dbo.SALES_HIST.LM, dbo.SALES_HIST.MBL, dbo.SALES_HIST.M3AGO, dbo.SALES_HIST.M4AGO, dbo.SALES_HIST.M5AGO, dbo.SALES_HIST.M3SUM,
dbo.SALES_HIST.M4SUM, dbo.SALES_HIST.M6SUM, dbo.SALES_HIST.M12SUM, dbo.SALES_HIST.M6AGO,Orders
FROM TR.dbo.IV00102 INNER JOIN
TR.dbo.IV00101 ON TR.dbo.IV00102.ITEMNMBR = TR.dbo.IV00101.ITEMNMBR INNER JOIN
TR.dbo.IV00108 ON TR.dbo.IV00101.ITEMNMBR = TR.dbo.IV00108.ITEMNMBR AND
TR.dbo.IV00101.PRCLEVEL = TR.dbo.IV00108.PRCLEVEL LEFT OUTER JOIN
dbo.SALES_HIST ON TR.dbo.IV00102.ITEMNMBR = dbo.SALES_HIST.ITEMNMBR AND
TR.dbo.IV00102.LOCNCODE = dbo.SALES_HIST.LOCATION LEFT OUTER JOIN
dbo.QTY_ON_HAND ON TR.dbo.IV00102.ITEMNMBR = dbo.QTY_ON_HAND.ITEMNMBR AND
TR.dbo.IV00102.LOCNCODE = dbo.QTY_ON_HAND.LOCATION LEFT OUTER JOIN
dbo.QTY_ON_ORDER ON TR.dbo.IV00102.LOCNCODE = dbo.QTY_ON_ORDER.LOCATION AND
TR.dbo.IV00102.ITEMNMBR = dbo.QTY_ON_ORDER.ITEMNMBR LEFT OUTER JOIN
@Output ON TR.dbo.IV00102.LOCNCODE = Site AND
TR.dbo.IV00102.ITEMNMBR = Item
WHERE (TR.dbo.IV00108.FROMQTY = 1) AND (TR.dbo.IV00101.ITEMTYPE IN (1, 3)) AND (TR.dbo.IV00102.LOCNCODE IN ('KP', 'VIC', 'WA'))
ORDER BY TR.dbo.IV00101.ITEMNMBR

Mark

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-29 : 00:47:08

quote:
Originally posted by Michael Valentine Jones

When I see joins that look like this, I start thinking about a date table.
year(c.date_ship) * 12 + month(c.date_ship) = year(getdate()) * 12 + month(getdate()) - 3

Yes, Michael, the only thin that changes for each field is the month(getdate()) - 3 part. I am thinking of multiple CASE statements.

I am also curious about the TOP 1 SUM statement...

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-29 : 00:52:02

quote:
Originally posted by Peso

quote:
Originally posted by Michael Valentine Jones

When I see joins that look like this, I start thinking about a date table.
year(c.date_ship) * 12 + month(c.date_ship) = year(getdate()) * 12 + month(getdate()) - 3
Yes, Michael, the only thin that changes for each field is the month(getdate()) - 3 part. I am thinking of multiple CASE statements.

I am also curious about the TOP 1 SUM statement...

Peter Larsson
Helsingborg, Sweden



c.date_ship >=  dateadd(mm,datediff(mm,0,getdate())-3,0) and
c.date_ship <  dateadd(mm,datediff(mm,0,getdate())-2,0)

CODO ERGO SUM

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources