SQL Group Query

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

LVL 3
Kevin RobinsonPrivate VB.NET ContractorAsked:
Who is Participating?
 
Bhavesh ShahConnect With a Mentor Lead AnalysistCommented:
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
 
Bhavesh ShahLead AnalysistCommented:
hi,

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

do u have master for it?
0
 
Kevin RobinsonPrivate VB.NET ContractorAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>explicitly stating eg  WHEN TIMESHEET.ProjectId = 1 etc.

:: blank stare ::

Huh?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.