August 28, 2010 at 1:59 pm
I need to determine the weekly stock amounts. I know the weekly inputs and outputs. I made a pivot table to get the products and weekly inputs/outputs. the pivot works well but at any week, the amount of stock is equal (weekly input - weekly output) + previous weeks inputs/outputs. inputs are (+), outputs are (-) so they are summable. hete's pivot code:
DECLARE @columns VARCHAR(8000)
SELECT @columns = COALESCE(@columns + ',[' + cast(WeekOffset as varchar) + ']',
'[' + cast(WeekOffset as varchar)+ ']')
FROM HaftalikStokRaporu_HaftalikStoklar_View
GROUP BY WeekOffset
order by Weekoffset
--select @columns
DECLARE @query VARCHAR(8000)
SET @query = '
SELECT urun, ' + @columns + '
FROM
(SELECT urun, Miktar, WeekOffset
FROM HaftalikStokRaporu_HaftalikStoklar_View ) AS p
PIVOT
(
SUM (Miktar)
FOR WeekOffset IN
( ' + @columns +' )
) AS pvt
WHERE urun like ''' + @UrunAdi + '''
ORDER BY urun;
'
--EXECUTE(@query)
print @query
this produces following sample:
urun 5217 5663 5693 5694
%5 Dekstroz 500ml 3 7 2 5
Abilify 10mg 16 7 7 5
Abilify 15mg 30 7 3 5
Abilify 30mg 30 7 3 5
Accolate 20mg -61 7 4 5
now I can explain simpler way:
5693's total must be 7+3+2
5694's total must be 7+3+5..... this goes on for every column
..... and for every row.
in other words, SUM function in pivot code should be something like:
SUMOFPREVIOSROWS(...)
its a bit confusing but changing pivor code or more simpler, creating a view to chage column values to sum off all previous colums are welcome.
But my friend, remember this: column number are varies. for all summing must be done with code. meaning SUM(column5217 + 5694 +....) is forbidden
August 28, 2010 at 2:09 pm
Since you have no data that we can actually play with, all I can do is give you a suggestion... see the following article...
http://qa.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
August 28, 2010 at 2:49 pm
wow, quick answer. yes I have no data, sorry I know it would be easy. okay, let me put in that way. here's sample data:
Product Col1 col2 col3 col4
a 1 2 3 4
b 2 3 4 5
c 3 3 4 4
that would be transformed to this:
Product Col1 col2 col3 col4
a 1 3 (1+2) 6 (3+3) 10 (6+4)
b 2 5 (2+3) 9 (5+4) 14 (9+5)
c 3 6 (3+3) 10 (6+4) 14 (10+4)
each cell is sum of itself and left cell. I can take it go on from here. forget pivots, etc. I temporarly solved the problem by loading data to grid in client side (spread component, like excel) and added cells with code. but my friend, its slow as a hell. I rellay want to make this calculations in sql server side.
thanks.
August 29, 2010 at 7:03 am
aykut canturk (8/28/2010)
wow, quick answer. yes I have no data, sorry I know it would be easy. okay, let me put in that way. here's sample data:Product Col1 col2 col3 col4
a 1 2 3 4
b 2 3 4 5
c 3 3 4 4
that would be transformed to this:
Product Col1 col2 col3 col4
a 1 3 (1+2) 6 (3+3) 10 (6+4)
b 2 5 (2+3) 9 (5+4) 14 (9+5)
c 3 6 (3+3) 10 (6+4) 14 (10+4)
each cell is sum of itself and left cell. I can take it go on from here. forget pivots, etc. I temporarly solved the problem by loading data to grid in client side (spread component, like excel) and added cells with code. but my friend, its slow as a hell. I rellay want to make this calculations in sql server side.
thanks.
What happened to the "previous row" requirement? That's the data I'm looking for. And please help me help you better... take a look at the article at the first link in my signature line below for how to post some good test data. Thanks.
--Jeff Moden
August 29, 2010 at 8:44 am
no my friend, forget about everything I said before and just consider my last post. I managed my pivot code and my only concern is now "sum of itself and left cell". other rows and cells are irrelevant now.
(and it not previous row, its previous cell)
August 29, 2010 at 10:35 am
Can't you just add up the columns then?
SELECT
Product,
col1,
col1 + col2,
col1 + col2 + col3,
col1 + col2 + col3 + col4
FROM YourTransformedTable
August 29, 2010 at 1:53 pm
yes actually. but column count and column names are changing all the time. so I beleive a kind of "for each column..." structure needed. and the the sql statement you wrote must be created by stored proc. that is what I cannot do because of knowledge lack.
August 30, 2010 at 7:57 am
aykut canturk (8/29/2010)
yes actually. but column count and column names are changing all the time. so I beleive a kind of "for each column..." structure needed. and the the sql statement you wrote must be created by stored proc. that is what I cannot do because of knowledge lack.
You keep changing and adding requirements. Let me know when you have a set of requirements properly hammered out.
--Jeff Moden
August 30, 2010 at 3:37 pm
I'm not changing requirements my friend. just clearifying the problem for you.
any way, thanks for your intrerest.
September 1, 2010 at 9:32 am
If you haven't figured out how to do this. Here's the code --
Use tempdb
go
-- create the above table
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'temp_model' AND type = 'U')
DROP TABLE temp_model
CREATE TABLE temp_model (
category VARCHAR(100),
value1 INT,
value2 INT,
value3 INT,
value4 INT)
go
INSERT INTO temp_model
SELECT 'a', 1, 2, 3, 4
UNION ALL
SELECT 'b', 2, 3, 4, 5
UNION ALL
SELECt 'c', 3, 3, 4, 4
go
------------------------------------------------
-- 1. get the column numbers and column orders
DECLARE @tableName VARCHAR(100),
@sql VARCHAR(8000),
@columnOrder INT, -- original table column order
@NestedColumnOrder INT, -- nested column order for value increase from prior column
@maxColumnOrder INT, -- original table max column numbers
@maxNestedColumnOrder INT, -- nested column max column numbers for each new column, this is to take care of new value of each column
@columnName VARCHAR(100), -- that is the column name in as statement, same as original table column
@columnValue VARCHAR(100), -- that's the new_column name used in value increase
@columnValue_prior VARCHAR(8000)
-- that's the prior columns so we can do value1 + value2 + value3
SET @tableName = 'temp_model'
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'temp_work1' AND type = 'U' )
DROP TABLE temp_work1
SELECT a.name, a.colorder
INTO temp_work1
FROM syscolumns a INNER JOIN sysindexes b ON
a.id = b.id
WHERE b.name = @tableName AND b.indid <2
-- cross join to get the columns we need for the new table
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'temp_work2' AND type = 'U' )
DROP TABLE temp_work2
SELECT a.*, b.name as new_column, b.colorder as new_order
INTO temp_work2
FROM temp_work1 a INNER JOIN temp_work1 b ON
a.colorder = b.colorder
WHERE a.colorder = 1 -- take care of first column
UNION ALL
SELECT a.*, b.name as new_column, b.colorder as new_order
FROM temp_work1 a INNER JOIN temp_work1 b
ON b.colorder <= a.colorder and b.colorder <> 1 -- the first column don't need the replication
ORDER BY a.colorder, b.colorder
-- now will loop through to create the sql select statement looks like
/*
SELECT name,
value1,
value1 + value2 as value2,
value1 + value2 + value3 as value3,
value1 + value2 + value3 + value4 as value4
into finaltable
from temp_model
*/
-- select * from temp_work2
SET @sql = ''
SET @columnOrder = 1
SELECT @maxColumnOrder = MAX(colorder)
FROM temp_work2
WHILE @columnOrder <= @maxColumnOrder
BEGIN
SELECT @maxNestedColumnOrder = MAX(new_order),
@NestedColumnOrder = MIN(new_order)
FROM temp_work2
WHERE colorder = @columnOrder
SET @columnValue_prior = ''
SELECT @columnName = name
FROM temp_work2
WHERE colorder = @columnOrder
WHILE @NestedColumnOrder<= @maxNestedColumnOrder
BEGIN
SELECT @columnValue = new_column
FROM temp_work2
WHERE new_order = @NestedColumnOrder
AND
colorder = @columnOrder
IF @NestedColumnOrder < @maxNestedColumnOrder
SET @columnValue = @columnValue + '+ '
SET @columnValue_prior = @columnValue_prior + @columnValue
SET @NestedColumnOrder = @NestedColumnOrder + 1
END
IF @columnOrder = 1 -- start of statement
SET @sql = 'SELECT ' + @columnValue_prior + ' AS ' + @columnName
ELSE
SET @sql = @sql + ', ' + @columnValue_prior + ' AS ' + @columnName
SET @columnOrder = @columnOrder + 1
END
-- complete the sql
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'finalTable' AND type = 'U')
DROP TABLE finalTable
SET @sql = @sql + ' INTO finalTable FROM temp_model'
EXEC (@sql)
go
SELECT *
FROM finalTable
September 1, 2010 at 10:37 am
woow... thank you my friend. you saved my day. I could not imagine this.
thank you for million times.
have a nice day.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply