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 2000 Forums  Transact-SQL (2000)  Timephasing Query

Author  Topic  pauloke Starting Member 1 Post Posted - 2008-02-22 : 05:48:00 Hi, I am trying to query a derived table within a dynamic query. The derived table is structrued as such Task Name | Baseline Work | Actual Work | Baseline Finish Date | Actual Finish Date Task A 100 101 01/01/2008 02/01/2008 Task B 156 132 11/02/2008 08/02/2008 Task C 200 100 21/02/2008 null ... and so on Given that a user selects a start and end date to be used for the query how would I create a timephased query (Timephased by week) such that the information in the derived table is aggregated based on certain criteria. For example: If the user selects a date range 01/01/2008 - 29/02/2008 The resulting table would be: Week Ending | Baseline Work | Actual Work 06/01/2008 100 101 13/01/2008 100 101 20/01/2008 100 101 27/01/2008 100 101 03/01/2008 100 101 10/02/2008 100 233 17/02/2008 256 233 ... and so on As you can see the baseline work value is summed if the baseline finish date is less than the week ending and the actual work is summed if the actual finish date has passed the week ending. This is a simplified view of what I am trying to achieve but essentially this is it. I need to be able to do this in a dynamic query and not in a stored procedure as this will be run by multiple users on multiple databases. I would be able to deploy the stored procedure on all database. Your help would be much apprecaited and I hope what I have here makes sense. The query will be run on a SQL Server 2000 Database Paul