Solved

SQL Group Query

Posted on 2013-10-24
4
179 Views
Last Modified: 2013-12-02
I have the query below which is working Ok but I would like to modify it so thate where I am explicitly stating eg  WHEN TIMESHEET.ProjectId = 1 etc.    I would like these to be automatically generated.  So if i add a new project i dont need to a add to modify this query.


SELECT   @LineManagers as LineManagers ,
 Staff.FirstName, Staff.SURNAME, dbo.ufn_GetLineManager(Staff.StaffId)as Linemanager,
SUM(CASE WHEN TIMESHEET.ProjectId = 1 THEN TIMESHEET.TIME ELSE 0 END) as REGINF,
SUM(CASE WHEN TIMESHEET.ProjectId = 2 THEN TIMESHEET.TIME ELSE 0 END) as ODTC,
SUM(CASE WHEN TIMESHEET.ProjectId = 3 THEN TIMESHEET.TIME ELSE 0 END) as VBI,
SUM(CASE WHEN TIMESHEET.ProjectId = 5 THEN TIMESHEET.TIME ELSE 0 END) as YOUTH,
SUM(CASE WHEN TIMESHEET.ProjectId = 6 THEN TIMESHEET.TIME ELSE 0 END) as UP,
SUM(CASE WHEN TIMESHEET.ProjectId = 8 THEN TIMESHEET.TIME ELSE 0 END) as JOINT,
SUM(CASE WHEN TIMESHEET.ProjectId = 13 THEN TIMESHEET.TIME ELSE 0 END) as COMPRJ,
SUM(CASE WHEN TIMESHEET.ProjectId = 14 THEN TIMESHEET.TIME ELSE 0 END) as SVA,
SUM(CASE WHEN TIMESHEET.ProjectId = 15 THEN TIMESHEET.TIME ELSE 0 END) as TBAP,
SUM(CASE WHEN TIMESHEET.ProjectId = 17 THEN TIMESHEET.TIME ELSE 0 END) as WPFG,
SUM(CASE WHEN TIMESHEET.ProjectId = 25 THEN TIMESHEET.TIME ELSE 0 END) as Enterprise
FROM         Staff FULL OUTER JOIN
TIMESHEET ON Staff.StaffId = TIMESHEET.StaffId
WHERE [Date] >=@StartDate
AND [Date]<=@EndDate
AND LinemanagerId IN (@LineManagers)
And [current] = 1
GROUP BY Staff.FirstName, Staff.SURNAME,  dbo.ufn_GetLineManager(Staff.StaffId)
ORDER BY Staff.FirstName, Staff.SURNAME

Open in new window

0
Comment
Question by:Kevin Robinson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39596784
hi,

but how you defined column name ?
i.e.
REGINF,ODTC,VBI

do u have master for it?
0
 
LVL 3

Author Comment

by:Kevin Robinson
ID: 39596791
Project table

ProjectId      ProjectCode
1      GENERAL (REGINF)
2      ODTC
3      VISP (VBI)
5      YOUTH
6      UP
8      JOINT
11      2012
13      COMPRJ
14      SVA
15      TB (AP)
17      WPFG
19      AP
20      BIG
24      UNI
25      Enterprise
0
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 500 total points
ID: 39596904
hi,

if projectid is limited then i suggest follow current method.

complete dynamic is may not be possible.

you can use pivot table, check out

http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39598007
>explicitly stating eg  WHEN TIMESHEET.ProjectId = 1 etc.

:: blank stare ::

Huh?
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question