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

Transact-SQL (2005)

Trouble Grouping

Author

Topic

meanmyrlin
Starting Member

15 Posts

Posted - 2009-02-18 : 16:52:28

Hello,

I am having some trouble with a seemingly simple query.

I have two tables:

Table1 has columns -

Month Year Price

Table2 has columns -

Month Year Period

I am trying to get a result that looks like -

Month Year ThreeMoPrice

Where ThreeMoPrice is the average of the prices for that month and the prices for the previous two months. I am trying to use Table2 to assist in grouping and I have spent hours trying to make this work. If the way to make this work does not include Table2 that is fine.

If anyone can help me I would really appreciate it. This just can't be as complicated as it seems...

Thank you for your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-18 : 16:56:54

Please show us some sample data, the expected output,a nd they query that you have been trying.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SQLforGirls
Starting Member

48 Posts

Posted - 2009-02-18 : 17:29:24

I'm going to make one big assumption, and that is that you have only 1 price listed per month in your table. If that is not the case, then you will need to do some additional work in your join to insure that you are only representing each month and value pair once when doing your average.

Let's assume you have this Table1:

create Table1
(Mo int,
Yr int,
Price numeric(9,2))

The first thing I would do is convert your Month and Year values into datetime. So Month = 1, Year = 2009 becomes: '1-1-2009' representing January, 2009.

SELECT convert(datetime, cast(mo as varchar(2))+'/1/'+cast(yr as varchar(4))) as priceMonth,
price
from Table1

Then join the resulting derived table to itself, matching up each month with itself and its previous 2 months.

select p1.priceMonth as month1, p2.priceMonth as month2, p2.price
from
(select convert(datetime, cast(mo as varchar(2))+'/1/'+cast(yr as varchar(4))) as priceMonth, price from Table1) p1
join
(select convert(datetime, cast(mo as varchar(2))+'/1/'+cast(yr as varchar(4))) as priceMonth, price from Table1) p2
on p2.priceMonth between dateadd(mm,-2,p1.priceMonth) and p1.priceMonth
order by 1

That will show you for each value in Month1, all the values for Month2 and Price that you will be averaging.

Those are just ways of seeing what you're doing in steps.
The final query is all you need:

select p1.priceMonth as month1, avg(p2.price) as threeMonthPrice
from
(select convert(datetime, cast(mo as varchar(2))+'/1/'+cast(yr as varchar(4))) as priceMonth, price from Table1) p1
join
(select convert(datetime, cast(mo as varchar(2))+'/1/'+cast(yr as varchar(4))) as priceMonth, price from Table1) p2
on p2.priceMonth between dateadd(mm,-2,p1.priceMonth) and p1.priceMonth
group by p1.priceMonth

Hope that helps.

meanmyrlin
Starting Member

15 Posts

Posted - 2009-02-18 : 17:32:13

It does. I didn't think of the converting to date/time option.

Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources