May 31, 2010 at 4:57 am
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 Spattah
SELECT
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 sd
LEFT JOIN SM_Project p on p.Project_Dim_Key = sd.Project_Dim
LEFT JOIN SM_Calendar c on sd.Sales_Date = c.Calendar_Key
WHERE c.Date BETWEEN @startdate and @enddate
GROUP BY c.App, p.ProjectNr, p.Project_Name
May 31, 2010 at 6:31 am
spattah
Without table definition(s), sample data it is difficult to provide an answer to your request. Please refer to the first link in my signature block for information as to how to post a question and receive tested answers.
June 1, 2010 at 12:39 am
I think it is because of difference in time stamps, please host some sample data as per Ron's response or use convert function in your group by.
Nag
Nag
------------------------------------------------
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
June 2, 2010 at 1:51 pm
spattah (5/31/2010)
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 Spattah
SELECT
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 sd
LEFT JOIN SM_Project p on p.Project_Dim_Key = sd.Project_Dim
LEFT JOIN SM_Calendar c on sd.Sales_Date = c.Calendar_Key
WHERE c.Date BETWEEN @startdate and @enddate
GROUP BY c.App, p.ProjectNr, p.Project_Name
It would help a lot if you would post an example of what the intended resultset would look like. You mentioned that you want to query a date range, but you are expecting one row per project. To do this, you need to remove the date column from the GROUP BY clause. If you still want to see the effective date range for each project, then you can aggregate the date using MIN and MAX.
For example:
SELECT
min(c.App) AS Begin_Date,
max(c.App) AS End_Date,
p.ProjectNr AS ProjectID,
p.Project_Name AS ProjectName,
SUM(sd.Margin) AS Margin,
SUM(sd.Turnover) AS Turnover,
...
...
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply