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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-02-04 : 19:49:30
|
Amit writes "Hello,
I'm hoping you can help with this little jam. I've got a table that is set up to track inventory usage by month. I need to aggregate this usage information in several ways (ex. year to date, rolling 12 month, rolling 24 month). I have developed the following queries to perform this task:
YTD
select part_number, site_number, sum(usage_amount) as ytd from eai_usage where (year(getdate()) = year(usage_month + '/01/' + usage_year)) group by part_number, site_number
12 Month
select part_number, site_number, sum(usage_amount) as rolling_one_year from eai_usage where (datediff(mm, getdate(), (usage_month + '/' + '01/' + usage_year)) >= -12) group by part_number, site_number
24 Month
select part_number, site_number, sum(usage_amount) as rolling_two_years from eai_usage where (datediff(mm, getdate(), (usage_month + '/' + '01/' + usage_year)) >= -24) group by part_number, site_number
Individually, this works great, I'm trying to figure out a way to consolidate this all into one SQL statement. I would like to display the result set somewhat as follows, with any empty values showing up as null:
part site YTD 12 Month 24 Month
I will be performing this query from ASP code so I'm trying to make this as "ASP friendly" as possible, and eventually I will need to pass in more advanced where conditions (ex. where part_number = 'xxx', where site_number = 'aaa')
Any advice would be greatly appreciated!
Thanks for your time!
Amit" |
|
|
|
|
|
|
|
Performing multiple