Solved

SQL Group Query

Posted on 2013-10-24
4
173 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
  • 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 65

Expert Comment

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

:: blank stare ::

Huh?
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

776 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