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: Tuesday, August 19, 2014 3:49 PM
Points: 4, Visits: 30
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: Today @ 12:00 AM
Points: 1,787, Visits: 5,725
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


  • MMGrid Addin
  • MMNose Addin


  • 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: Tuesday, August 19, 2014 3:49 PM
    Points: 4, Visits: 30
    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