Generate dynamic calculated columns in my query

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

Transact-SQL (2005)

Generate dynamic calculated columns in my query

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2010-04-06 : 05:41:37

Hi,

I have a table something like this

month productId price1
3 1 10
3 2 20
3 3 30
4 1 15
4 2 15
4 3 35

I want to display this table in a format to compare the price changes of each product month to month. The output should look something like this:

productid lastmonthprice thismonthprice difference
1 10 15 5
2 20 15 -5
3 30 35 5

Whats the best way to achive this?

Thanks

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 06:01:55

Do you have a year column as well. The reason I ask is that When thisMonth = 1 (Jan), lastMonth will = 12 (Dec) of the previous year.

There are 10 types of people in the world, those that understand binary, and those that don't.

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2010-04-06 : 06:13:52

Hi,

That shouldn't matter, the monthId doesn't actually work that way, its just sequential for each month that goes on.

Thanks

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-04-06 : 06:19:20

Try This!

select lm.productId,lm.price as last_month ,cm.price as current_month,
lm.price-cm.price from table_name lm inner join
table_name cm on lm.productId=cm.productId and lm.month=3 and
cm.month=4

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-06 : 06:20:09

you mean 4 doesnt mean april and 3 does not mean march ???

Vaibhav T

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-04-06 : 06:23:03

quote:
Originally posted by vaibhavktiwari83

you mean 4 doesnt mean april and 3 does not mean march ???

Vaibhav T

Ya it does't mean!

Just a seq 3,4...12,13.... so on!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-06 : 06:27:47

quote:
Originally posted by senthil_nagore

Try This!

select lm.productId,lm.price as last_month ,cm.price as current_month,
lm.price-cm.price from table_name lm inner join
table_name cm on lm.productId=cm.productId and lm.month=3 and
cm.month=4

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

More generalize


SELECT	a.ProductID, 
		a.Price1 LastMonthPrice, 
		b.Price1 ThisMonthPrice, 
		a.Price1 - b.Price1 [difference] 
FROM	testtable a
INNER JOIN 
( 
	SELECT	ProductID, Month, Price1 FROM 
	(
		SELECT	ProductID, Month, Price1, ROW_NUMBER() OVER(Partition by ProductID Order BY Month Desc) RowNo
		FROM	testtable 
	) d
	WHERE	RowNo = 1
) b
ON		a.ProductID = b.ProductID
WHERE	a.Month = b.month - 1

Vaibhav T

theboyholty
Posting Yak Master

226 Posts

Posted - 2010-04-06 : 06:30:34

This is a much shorter query and will be even more so when you strip out the temp table stuff and replece with your own table names

CREATE TABLE #PRODUCTS (
MonthNum int,
ProductID int,
Price decimal)

INSERT INTO #PRODUCTS (MonthNum,ProductID,Price) VALUES (3,1,10)
INSERT INTO #PRODUCTS (MonthNum,ProductID,Price) VALUES (3,2,20)
INSERT INTO #PRODUCTS (MonthNum,ProductID,Price) VALUES (3,3,30)
INSERT INTO #PRODUCTS (MonthNum,ProductID,Price) VALUES (4,1,15)
INSERT INTO #PRODUCTS (MonthNum,ProductID,Price) VALUES (4,2,15)
INSERT INTO #PRODUCTS (MonthNum,ProductID,Price) VALUES (4,3,35)

SELECT
p.ProductID
,p2.Price as LastMonthPrice
,p.Price as ThisMonthPrice
,p.Price - p2.Price as [Difference]
FROM #PRODUCTS p
INNER JOIN #PRODUCTS p2 ON p.ProductID=p2.ProductID AND p.MonthNum-1=p2.MonthNum

DROP TABLE #PRODUCTS

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-04-06 : 06:31:22

Sounds Good!

But need to decide according to the Requirement!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 06:35:20

Something like this:

SELECT m1.Month, m1.ProductID, ISNULL(m2.Price1, 0) AS LastMonthPrice,
		m1.Price1, 0 AS ThisMonthPrice, 
		m1.Price1 - ISNULL(m2.Price1, 0) AS Difference
FROM table m1
LEFT JOIN table m2
	ON m1.ProductID = m2.ProductID
		AND m1.Month = m2.Month + 1

You can use a where clause here to filter out the months/productIds that you want.

There are 10 types of people in the world, those that understand binary, and those that don't.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-06 : 06:41:10

quote:
Originally posted by DBA in the making

Something like this:
SELECT m1.Month, m1.ProductID, ISNULL(m2.Price1, 0) AS LastMonthPrice,
m1.Price1, 0 AS ThisMonthPrice,
m1.Price1 - ISNULL(m2.Price1, 0) AS Difference
FROM table m1
LEFT JOIN table m2
ON m1.ProductID = m2.ProductID
AND m1.Month = m2.Month + 1

You can use a where clause here to filter out the months/productIds that you want.

There are 10 types of people in the world, those that understand binary, and those that don't.

This will not give desired output...
this is giving each record with computed column

Vaibhav T

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-04-06 : 06:43:27

quote:
Originally posted by DBA in the making

Something like this:
SELECT m1.Month, m1.ProductID, ISNULL(m2.Price1, 0) AS LastMonthPrice,
m1.Price1, 0 AS ThisMonthPrice,
m1.Price1 - ISNULL(m2.Price1, 0) AS Difference
FROM table m1
LEFT JOIN table m2
ON m1.ProductID = m2.ProductID
AND m1.Month = m2.Month + 1

You can use a where clause here to filter out the months/productIds that you want.

There are 10 types of people in the world, those that understand binary, and those that don't.

Does't have much difference then theboyholty's Query

The Left join and Isnull() are unnecessary!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-06 : 06:43:28

quote:
Originally posted by theboyholty

This is a much shorter query and will be even more so when you strip out the temp table stuff and replece with your own table names

CREATE TABLE #PRODUCTS (
MonthNum int,
ProductID int,
Price decimal)

INSERT INTO #PRODUCTS (MonthNum,ProductID,Price) VALUES (3,1,10)
INSERT INTO #PRODUCTS (MonthNum,ProductID,Price) VALUES (3,2,20)
INSERT INTO #PRODUCTS (MonthNum,ProductID,Price) VALUES (3,3,30)
INSERT INTO #PRODUCTS (MonthNum,ProductID,Price) VALUES (4,1,15)
INSERT INTO #PRODUCTS (MonthNum,ProductID,Price) VALUES (4,2,15)
INSERT INTO #PRODUCTS (MonthNum,ProductID,Price) VALUES (4,3,35)

SELECT
p.ProductID
,p2.Price as LastMonthPrice
,p.Price as ThisMonthPrice
,p.Price - p2.Price as [Difference]
FROM #PRODUCTS p
INNER JOIN #PRODUCTS p2 ON p.ProductID=p2.ProductID AND p.MonthNum-1=p2.MonthNum

DROP TABLE #PRODUCTS

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

simpler query good one

Vaibhav T

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 07:00:05

quote:
Originally posted by vaibhavktiwari83

quote:
Originally posted by DBA in the making

Something like this:
SELECT m1.Month, m1.ProductID, ISNULL(m2.Price1, 0) AS LastMonthPrice,
m1.Price1, 0 AS ThisMonthPrice,
m1.Price1 - ISNULL(m2.Price1, 0) AS Difference
FROM table m1
LEFT JOIN table m2
ON m1.ProductID = m2.ProductID
AND m1.Month = m2.Month + 1

You can use a where clause here to filter out the months/productIds that you want.

There are 10 types of people in the world, those that understand binary, and those that don't.

This will not give desired output...
this is giving each record with computed column

Vaibhav T

Which is why I said to use a where clause to filter the desired records.

quote:
Originally posted by senthil_nagore
Does't have much difference then theboyholty's Query

The Left join and Isnull() are unnecessary!

The left join/insull ensures that the first month will be displayed.

There are 10 types of people in the world, those that understand binary, and those that don't.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 07:03:10

quote:
Originally posted by vaibhavktiwari83
simpler query good one

It's almost identical to mine, except mine also includes the first month.

There are 10 types of people in the world, those that understand binary, and those that don't.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-06 : 07:08:01

What do you mean by first month

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-06 : 07:16:55

[code]
SELECT t.month, t.productId, t1.price1 as lastmonthprice,t.price1 as thismonthprice,
t.price1 - t1.price1 AS [difference]
FROM Table t
CROSS APPLY (SELECT TOP 1 price1
FROM Table
WHERE month < t.month
AND productId=t.productId
ORDER BY month DESC)t1
[/code]
This will work even if your sequential monthno has gaps in between

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 07:17:16

quote:
Originally posted by vaibhavktiwari83

What do you mean by first month

I mean the month for which there is no preceding month. It still has a month price, it's preceding month's price is 0 in my query. It would be completely omitted from theboyholty's query. Aside from that, the 2 queries are virtually identical.

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources