Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Group Query

Posted on 2013-10-24
4
Medium Priority
?
186 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 2000 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

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

581 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