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 |
|
spattah
Starting Member
2 Posts |
 Posted - 2010-05-31 : 07:11:10
|
| I'm trying to create a query where I get aggregated rows for every project in a choosen time-interval (see query below). The problem lies in grouping the dates. If I choose an interval of 5 days the query gives me a a row for every day per project instead of one aggregated row per project. Does someone have any tips how to handle this problem?Thanks in advance,Kind regards SpattahSELECT c.App AS Date,p.ProjectNr AS ProjectID,p.Project_Name AS ProjectName,SUM(sd.Margin) AS Margin,SUM(sd.Turnover) AS Turnover,FROM Sales_Daily sdLEFT JOIN SM_Project p on p.Project_Dim_Key = sd.Project_DimLEFT JOIN SM_Calendar c on sd.Sales_Date = c.Calendar_KeyWHERE c.Date BETWEEN @startdate and @enddateGROUP BY c.App, p.ProjectNr, p.Project_Name |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-05-31 : 07:30:53
|
If you are retrieving rows only for the needed time-interval then I think you don't need to group by c.App.And while you are grouping Projects your c.App in the select list should aggregated for example max(c.App) as Date.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
spattah
Starting Member
2 Posts |
Posted - 2010-05-31 : 07:55:25
|
| Thanks for the answer. I'm not sure what you mean though. How should I set that c.app aggregate function? And the interval should have both a minimum and maximum. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-05-31 : 08:34:34
|
Then please provide sample data and wanted output.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
date aggregation