Solved

SQL Group Query

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

Expert Comment

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

:: blank stare ::

Huh?
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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

752 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