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 |
|
scioroianu
Starting Member
1 Post |
 Posted - 2004-06-30 : 14:04:53
|
Hi everybody,I have a very simple table:CREATE TABLE [dbo].[test] ( [RecordID] [int] NOT NULL , [ProjectID] [int] NULL , [TotalHours] [int] NULL , [ProjectStartDate] [datetime] NULL , [ProjectEndDdate] [datetime] NULL ) ON [PRIMARY]GOThis holds each project and the total hours and the start and end date.What I need to obtain is how many hours for each project are assigned for each day and each week.The problem is that I need this report to show me even for the days for which I don't have projects - it needs to display data for all year days. Same story for each week of the year.The number of hours per hour and week are obtained by dividing the total hours to each projects number of days - but this is the last problem.To do grouping per day and week would be the short story.I would like this to came from a single query or even stored procedure and I am out of ideas.The stored procedure idea is gone..I was intending to use a 'while' and go let's say through one year, for example, for each day...it will execute for each day. But how to get the data combined in a new recordset and available to ADO? It is not possible or I don't know about it..I've thought that a cursor would work, but no chance there neither - the cursor is not available to ADO and other APIs.Thanks a lot for your help |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-06-30 : 14:56:10
|
| How is a stored procedure accessible, but 'while' is not?? I'm not sure I understand your limitations...Corey |
 |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-07-01 : 03:39:09
|
| So build a table with all the days of the year in it and then join to this to get each day... Can be done in a set-based query then... |
|
|
|
|
|
|
Grouping per day and week