Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Issue With Producing Running Total Column In Select Clause Expand / Collapse
Author
Message
Posted Sunday, July 27, 2014 3:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:22 AM
Points: 5, Visits: 47
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.
Post #1596660
Posted Sunday, July 27, 2014 5:07 PM This worked for the OP Answer marked as solution


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 5:57 PM
Points: 1,939, Visits: 6,470
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);


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw
  • Post #1596666
    Posted Sunday, July 27, 2014 5:45 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Wednesday, October 15, 2014 10:22 AM
    Points: 5, Visits: 47
    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('');
    Post #1596668
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse