Two Columns in CASE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Two Columns in CASE

Author

Topic

dneil
Starting Member

12 Posts

Posted - 2010-01-07 : 06:21:17

Hello,

The members of this forum we so helpful before when I had a problem - I'm now hoping you can all help again!

I am selecting job numbers from a table and want to include names of the primary and secondary consultants assigned to that job. The information isn't stored in the table as a column for primary and secondary, however. Rather, it is stored using numerical values which correspond to the consultant's relationship to that job, where 9 is primary consultant and 10 is the secondary. For example:

Job # UserRelationship UserId
1 9 Joe Bloggs
1 10 Mickey Mouse
2 9 Goofy
2 10 Alan Sugar

etc. I need the result set as follows:

Job # Primary Consultant Secondary Consultant
1 Joe Bloggs Mickey Mouse
2 Goofy Alan Sugar

I have attempted using case:

----------------------------
CASE
WHEN dbo.PlacementConsultants.UserRelationshipId=9 THEN UserId
ELSE 'NULL'
END AS 'Primary Consultant',

CASE
WHEN dbo.PlacementConsultants.UserRelationshipId=10 THEN UserId
ELSE 'NULL'
END AS 'Secondary Consultant'
-----------------------------

but of course this produces:

Job # Primary Consultant Secondary Consultant
1 Joe Bloggs NULL
1 NULL Mickey Mouse
2 Goofy NULL
2 NULL Alan Sugar

I need a single job on a single line with both the primary and secondary on that same line.

How do I go about doing this? Can I write a single CASE statement to produce the two columns I need on the same line? Or is there another method?

Thanks in advance for anyone's help!

Regards,

Dominic Neil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 06:29:30

[code]select [job#],
max(case when UserRelationship=9 then userid else null end) as [primary consultant],
max(case when UserRelationship=10 then userid else null end) as [secondary consultant]
from yourtable
group by [job#]
[/code]

dneil
Starting Member

12 Posts

Posted - 2010-01-07 : 06:35:19

Thankyou again visakh16!

That works perfectly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 06:36:18

welcome

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-01-07 : 06:41:12

Hi

what is the difference these two...


max(case when UserRelationship=9 then userid else null end) as [primary consultant],
max(case when UserRelationship=10 then userid else null end) as [secondary consultant]


CASE WHEN max(ID) = 9 then userid else null end as  [primary consultant],
CASE WHEN max(ID) = 10 then userid else null end as [secondary consultant]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 06:57:06

quote:

Originally posted by WoodHouse

Hi

what is the difference these two...


max(case when UserRelationship=9 then userid else null end) as [primary consultant],
max(case when UserRelationship=10 then userid else null end) as [secondary consultant]


CASE WHEN max(ID) = 9 then userid else null end as  [primary consultant],
CASE WHEN max(ID) = 10 then userid else null end as [secondary consultant]

in second case you're not aggregating that why the values get into different rows. max() aggregates the values onto single row

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-01-07 : 08:12:44

quote:
Originally posted by visakh16

quote:
Originally posted by WoodHouse

Hi

what is the difference these two...

max(case when UserRelationship=9 then userid else null end) as [primary consultant],
max(case when UserRelationship=10 then userid else null end) as [secondary consultant]

CASE WHEN max(ID) = 9 then userid else null end as [primary consultant],
CASE WHEN max(ID) = 10 then userid else null end as [secondary consultant]

in second case you're not aggregating that why the values get into different rows. max() aggregates the values onto single row

CREATE TABLE #TEMP(ID INT, EMP INT)

INSERT INTO #TEMP VALUES(10,1)
INSERT INTO #TEMP VALUES(20,1)
INSERT INTO #TEMP VALUES(30,2)
INSERT INTO #TEMP VALUES(40,2)
INSERT INTO #TEMP VALUES(50,3)

SELECT EMP,
CASE WHEN MAX(ID) = 9 THEN 100 ELSE NULL END AS [PRIMARY CONSULTANT],
CASE WHEN MAX(ID) = 40 THEN 200 ELSE NULL END AS [SECONDARY CONSULTANT]
FROM #TEMP
GROUP BY EMP

i think above query is aggregating if am in wrong pls correct me..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 09:11:09

quote:
Originally posted by WoodHouse

quote:
Originally posted by visakh16

quote:
Originally posted by WoodHouse

Hi

what is the difference these two...

max(case when UserRelationship=9 then userid else null end) as [primary consultant],
max(case when UserRelationship=10 then userid else null end) as [secondary consultant]

CASE WHEN max(ID) = 9 then userid else null end as [primary consultant],
CASE WHEN max(ID) = 10 then userid else null end as [secondary consultant]

in second case you're not aggregating that why the values get into different rows. max() aggregates the values onto single row

CREATE TABLE #TEMP(ID INT, EMP INT)

INSERT INTO #TEMP VALUES(10,1)
INSERT INTO #TEMP VALUES(20,1)
INSERT INTO #TEMP VALUES(30,2)
INSERT INTO #TEMP VALUES(40,2)
INSERT INTO #TEMP VALUES(50,3)

SELECT EMP,
CASE WHEN MAX(ID) = 9 THEN 100 ELSE NULL END AS [PRIMARY CONSULTANT],
CASE WHEN MAX(ID) = 40 THEN 200 ELSE NULL END AS [SECONDARY CONSULTANT]
FROM #TEMP
GROUP BY EMP

i think above query is aggregating if am in wrong pls correct me..

once you're grouping by emp you need to apply aggregates on other fields

dneil
Starting Member

12 Posts

Posted - 2010-01-08 : 04:16:36

Ok, a new development!

I am trying to integrate this into a larger query, which counts the amount of hours worked on the job. The above solution seems to be doubling the amount of hours worked when both the primary and secondary consultant are present (in the very few occasions where there is only one consultant this isn't happening).

I can see that it will have something to do with counting the number of hours worked twice because of the two UserRelationshipId rows. Is there a way to recify this?

Below is my full query so hopefully the problem is evident:
-------------------------
SELECT dbo.Placements.PlacementId,
dbo.Placements.Description,
CASE
WHEN dbo.Placements.SectorId=51 THEN 'Gov/Corp'
WHEN dbo.Placements.SectorId=55 THEN 'Gov/Corp'
WHEN dbo.Placements.SectorId=56 THEN 'Gov/Corp'
ELSE 'Private'
END AS 'Sector',

ISNULL (SUM(dbo.TimesheetHours.HoursWorked),'0') AS 'Hours Worked' ,
max(case when dbo.PlacementConsultants.UserRelationshipId=9 then dbo.Users.Username+ ' ' +dbo.Users.Surname else null end) as [primary consultant],
max(case when dbo.PlacementConsultants.UserRelationshipId=10 then dbo.Users.Username+ ' ' +dbo.Users.Surname else null end) as [secondary consultant],
'£'+ CONVERT(varchar(12), (CONVERT(decimal(7,2), ROUND(SUM(dbo.TimesheetHours.ChargeRate*dbo.TimesheetHours.HoursWorked),2)))) AS 'Charged',
'£'+ CONVERT(varchar(12), (CONVERT(decimal(7,2), ROUND(SUM(dbo.TimesheetHours.PayRate*dbo.TimesheetHours.HoursWorked),2)))) AS 'Paid',
'£'+ CONVERT(varchar(12), (CONVERT(decimal(7,2), ROUND(SUM(dbo.TimesheetHours.ChargeRate*dbo.TimesheetHours.HoursWorked)-SUM(dbo.TimesheetHours.PayRate*dbo.TimesheetHours.HoursWorked),2)))) AS 'Margin'

FROM dbo.Users
RIGHT OUTER JOIN dbo.PlacementConsultants ON
dbo.Users.UserId=dbo.PlacementConsultants.UserId
RIGHT OUTER JOIN dbo.Placements ON
dbo.PlacementConsultants.PlacementId=dbo.Placements.PlacementId
LEFT JOIN dbo.Timesheets ON
dbo.Placements.PlacementId=dbo.Timesheets.PlacementId
LEFT JOIN dbo.TimesheetHours ON
dbo.Timesheets.TimesheetId=dbo.TimesheetHours.TimesheetId

GROUP BY dbo.Placements.PlacementId, dbo.Placements.Description, dbo.Placements.SectorId

ORDER BY PlacementId
-----------------------------

An example of the result set. The hours worked and subsequent monetary values have been doubled:

12422 Accountancy CA Private 4.00 Dipti Shah Frayne Saunders £187.04 £94.48 £92.56
12423 English KS2 Junior Primary Private 8.00 Rose Vane Jan Ellis £272.00 £137.20 £134.80
12424 Science KS4 GCSE Gov/Corp 8.00 Jane Taylor-Bryan Mary Swindale £283.20 £139.68 £143.52
12425 Maths KS4 GCSE Alice Souch Gov/Corp 6.00 Rory Mann Becky Wharf £216.00 £104.76 £111.24

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-08 : 10:39:41

this may be because you may have one to many relationship existing b/w some of involved tables. can you spot any such tables? also if you want somebody else to help, you need to give some sample data from all your involved tables.

dneil
Starting Member

12 Posts

Posted - 2010-01-11 : 04:54:26

I can't tell, but I am new to SQL and there are so many little nuances I am learning as I go. Perhaps I am missing something obvious.

dbo.Users - users of our recruitment software, sales consultants, etc
---------
UserId UserName Surname
1 Ian Cranstone
2 Jane Ellis
3 Amanda Murray
4 Jackie Vane

dbo.PlacementConsultants (as initial topic question)
---------
PlacementConsultantId PlacementId UserId UserRelationshipId
47 33 1 9
48 33 9 10
61 41 10 9
62 41 8 10

dbo.Placements
---------
PlacementId ClientId JobId ApplicantId Description SectorId
33 51 24 51 Maths KS4 52
41 65 31 73 English KS5 52
56 2 NULL 49 Common Entrance 11+ 52
93 50 25 89 Elecution 51

dbo.Timesheets - holds information on timesheets assigned to placements
---------
TimesheetId PlacementId PeriodStarting PeriodEnding
9784 33 01/06/2009 04:00:00 30/06/2009 00:00:00
9785 41 01/07/2009 00:00:00 31/07/2009 00:00:00
9786 56 01/08/2009 00:00:00 31/08/2009 00:00:00
9787 93 01/09/2009 00:00:00 30/09/2009 00:00:00

dbo.TimesheetHours - holds information on hours worked on timesheets
---------
TimesheetHourId TimesheetId WorkedOn HoursWorked PayRate ChargeRate
23 9784 01/06/2009 00:00:00 1 17.34 35.04
24 9784 08/06/2009 00:00:00 1 17.34 35.04
25 9785 02/07/2009 00:00:00 3 19.23 36.09
26 9786 23/08/2009 00:00:00 2 19.49 36.50
27 9787 15/09/2009 00:00:00 1 20.01 38.93
(note that TimesheetId appears more than once for many placements, based on how many periods of tuition took place)

These are the tables being queried. As I say, the problem occurs on lines where both primary and secondary consultant are present. Where one is NULL the hours are correct.

Thanks for any help you can offer.

Dominic Neil.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-11 : 10:40:39

where does info to determine primary and secondary consultant reside?

dneil
Starting Member

12 Posts

Posted - 2010-01-11 : 11:09:01

In the dbo.PlacementConsultants table. The UserId column defines the consultant and the UserRelationshipId column defines what role they hold, 9 being primary consultant and 10 being secondary.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-11 : 11:33:05

seems like this


SELECT dbo.Placements.PlacementId, 
dbo.Placements.Description, 
CASE 
WHEN dbo.Placements.SectorId=51 THEN 'Gov/Corp' 
WHEN dbo.Placements.SectorId=55 THEN 'Gov/Corp' 
WHEN dbo.Placements.SectorId=56 THEN 'Gov/Corp' 
ELSE 'Private' 
END AS 'Sector', 

ISNULL (SUM(dbo.TimesheetHours.HoursWorked),'0') AS 'Hours Worked' ,
p.[primary consultant],p.[secondary consultant],
'£'+ CONVERT(varchar(12), (CONVERT(decimal(7,2), ROUND(SUM(dbo.TimesheetHours.ChargeRate*dbo.TimesheetHours.HoursWorked),2)))) AS 'Charged', 
'£'+ CONVERT(varchar(12), (CONVERT(decimal(7,2), ROUND(SUM(dbo.TimesheetHours.PayRate*dbo.TimesheetHours.HoursWorked),2)))) AS 'Paid', 
'£'+ CONVERT(varchar(12), (CONVERT(decimal(7,2), ROUND(SUM(dbo.TimesheetHours.ChargeRate*dbo.TimesheetHours.HoursWorked)-SUM(dbo.TimesheetHours.PayRate*dbo.TimesheetHours.HoursWorked),2)))) AS 'Margin' 


FROM 
(
SELECT dbo.PlacementConsultants.PlacementId,
max(case when dbo.PlacementConsultants.UserRelationshipId=9 then dbo.Users.Username+ ' ' +dbo.Users.Surname else null end) as [primary consultant],
max(case when dbo.PlacementConsultants.UserRelationshipId=10 then dbo.Users.Username+ ' ' +dbo.Users.Surname else null end) as [secondary consultant], 
FROM dbo.Users
RIGHT OUTER JOIN dbo.PlacementConsultants ON
dbo.Users.UserId=dbo.PlacementConsultants.UserId
GROUP BY dbo.PlacementConsultants.PlacementId
)p
RIGHT OUTER JOIN dbo.Placements ON
p.PlacementId=dbo.Placements.PlacementId
LEFT JOIN dbo.Timesheets ON
dbo.Placements.PlacementId=dbo.Timesheets.PlacementId
LEFT JOIN dbo.TimesheetHours ON
dbo.Timesheets.TimesheetId=dbo.TimesheetHours.TimesheetId

GROUP BY dbo.Placements.PlacementId, dbo.Placements.Description, dbo.Placements.SectorId

ORDER BY dbo.Placements.PlacementId

dneil
Starting Member

12 Posts

Posted - 2010-01-12 : 04:34:07

That seems to produce the correct result set. I am going through looking at a random sample and checking they are correct, so far so good!

What did you do there, visakh16? From what I can see you have created custom columns (p.primary and p.secondary) defined by the SELECT subquery in the FROM clause?

If so then I understand how that works, defining and aggregating the primary and secondary consultant separately.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 04:38:26

quote:
Originally posted by dneil

That seems to produce the correct result set. I am going through looking at a random sample and checking they are correct, so far so good!

What did you do there, visakh16? From what I can see you have created custom columns (p.primary and p.secondary) defined by the SELECT subquery in the FROM clause?

If so then I understand how that works, defining and aggregating the primary and secondary consultant separately.

yup exactly. the problem with original query was that you were trying to do all together and with those multiple records one each for primary/secondary consultant you'll get duplicates returned for all others including TimesheetHours causing the measures to get doubled. In my case, i group and do aggregation separately and link to main query which prevents this multiple records in resultset and avoids double counting of measures

dneil
Starting Member

12 Posts

Posted - 2010-01-12 : 04:44:10

That's fantastic. You continue to be a great help!

I think I need to do more reading on subqueries. I have wanted to use them before but I have a hard time understanding where's appropriate to use them. Having such a good example of a problem I#ve had and why it's necessary will help.

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 04:46:46

you're welcome always

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources