T-SQL Challenge - 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)

T-SQL Challenge

Author

Topic

JRobinson
Starting Member

3 Posts

Posted - 2007-10-17 : 15:47:42

We have this basic SELECT statement:

SELECT product_id, we_date, sum(demand_units)
FROM weekly_transactions
WHERE demand_units > 0
GROUP BY product_id, we_date
ORDER BY product_id, we_date

However, for each Product and WE_DATE, we also want the demand units for the previous 10 weeks. So far week ending 9/23/2007, we want the demand_units for that week PLUS the demand_units for the previous 10 weeks. I have NOT idea how to pull this off! Can anyone out there help me?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-17 : 15:50:43

This is not an article, so I am moving the thread.

I'm not sure how this is even a challenge. Are you giving away prizes for solutions?

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-17 : 15:50:47

moved from article discussion, since it has nothing to do with articles.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-17 : 15:51:17

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-10-17 : 16:28:59

thanks for moving the thread.

-ec

JRobinson
Starting Member

3 Posts

Posted - 2007-10-17 : 16:33:19

Sorry, I posted it in the wrong place. New to the forum.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 01:39:03

[code]SELECT product_id,
we_date,
sum(demand_units),
sum(case when we_date >= dateadd(day, datediff(day, 70, current_timestamp), 0) then demand_units else 0 end) AS Last70days
FROM weekly_transactions
WHERE demand_units > 0
GROUP BY product_id,
we_date
ORDER BY product_id,
we_date[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources