July 27, 2014 at 3:08 pm
I want to create the following scenario. I have a table that stores employees working on projects and their project hours by week, but now I also need a running total per week for each of those projects. For example take a look below:
EmployeeID, Project, Sunday, Monday, Tuesday,....Saturday, ProjectHours, TotalProjectHoursPerWeek(this is the column I am trying to derive), FiscalWeek
101, ProjectABC, 5,5,5,...5, 20, 40,25
102, ProjectXYZ 4,4,4,....4, 20 ,40,25
103,ProjectQWE, 2,2,2,...2, 8, 32,26
104, ProjectPOP, 6,6,6,...6, 24, 32,26
What I have tried so far:
Correlated Subquery:
SELECT EmployeeID,Project, Sunday, Monday,....Saturday, ProjectHours, SELECT(SUM(ProjectHours) FROM dbo.TableABC ap GROUP BY FiscalWeek),
FROM
dbo.TableABC a
I got this to work one time before, but now I am getting the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I am stumped as I have been trying to figure out for hours about whats going on. If anyone knows how to fix this or a workaround to produce the desired results, please let me know as soon as possible as I have project deadline Tuesday. Thank you.
July 27, 2014 at 5:07 pm
That doesn't look like a running total, merely a grouped sum.
Try this:
SELECT
EmployeeID
, Project
, Sunday
, Monday
,....Saturday
, ProjectHours
, SUM(ProjectHours) OVER(Partition BY FiscalWeek) AS TotalProjectHoursPerWeek
, FiscalWeek
FROM
dbo.TableABC a
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
July 27, 2014 at 5:45 pm
Awesome that did the trick! I knew it was something very simple, but I spent hours on it. I am a beginner developer and not too familiar with the OVER and partition clause. I will keep in my notebook from now on, thank you so much, you're a godsent! txtPost_CommentEmoticon(':-D');
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply