Problem query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Problem query

Author

Topic

CSharpNewbie
Starting Member

39 Posts

Posted - 2009-03-16 : 19:55:44

Hi,
I am facing issues trying to write a query.

My tables are layed out as follows:

tblTicketIssues.TicketID
tblTicketIssues.RequesterID

tblPersonnelProfile.PersonnelID
tblPersonnelProfile.FirstName
tblPersonnelProfile.LastName

tblTicketAttribute.TicketID
tblTicketAttribute.Attribute
tblTicketAttribute.AttributeValue

I have to display the following fields:

TicketID, RequesterFullName, UrgentPriorityID, MediumPriorityID, LowPrioritytID

This is the part that is challenging:
If tblTicketAttribute.Attribute= "Urgent" then the value from tblTicketAttribute.AttributeValue is displayed in UrgentPriority column
If tblTicketAttribute.Attribute= "Medium" then the value from tblTicketAttribute.AttributeValue is displayed in MediumPriority column
If tblTicketAttribute.Attribute= "Low" then the value from tblTicketAttribute.AttributeValue is displayed in LowPriority column

The values in tblTicketAttribute.Attribute include "Urgent", "Medium", "Low", "Over30", "Over60", "Over90", "Closed"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-17 : 01:14:10

try like this

select ticketid , ISNULL(ejd.firstname,'') + ISNULL(' ' + ejd.lastname, '')as RequesterFullName,
case when Attribute= 'Urgent' THEN AttributeValue END AS UrgentPriorityID,
case when Attribute= 'Medium' THEN AttributeValue END AS MediumPriorityID,
case when Attribute= 'Low' THEN AttributeValue END AS LowPrioritytID,
from tablename

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-17 : 11:37:32

[code]SELECT ti.TicketID,
COALESCE(pp.FirstName+ ' ','') +COALESCE(pp.LastName,'') AS RequesterFullName,
ta.UrgentPriorityID,ta.MediumPriorityID,ta.LowPriorityID
FROM tblTicketIssues ti
INNER JOIN tblPersonnelProfile pp
ON pp.PersonnelID=ti.RequestedID
INNER JOIN
(SELECT TicketID,
MAX(CASE WHEN Attribute='Urgent' THEN Attribute_Value ELSE NULL END) AS UrgentPriorityID,
MAX(CASE WHEN Attribute='Medium' THEN Attribute_Value ELSE NULL END) AS MediumPriorityID,
MAX(CASE WHEN Attribute='Low' THEN Attribute_Value ELSE NULL END) AS LowPriorityID
FROM tblTicketAttribute
GROUP BY TicketID)ta
ON ta.TicketID=ti.TicketID
[/code]

CSharpNewbie
Starting Member

39 Posts

Posted - 2009-03-19 : 12:47:12

I have a modification to this query:

If tblTicketAttribute.Attribute= "Closed" then <do not display record, move on to next record>

Any suggestions?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-03-19 : 13:01:01

FROM tblTicketAttribute WHERE Attribute <> 'Closed'

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 13:12:28

quote:
Originally posted by CSharpNewbie

I have a modification to this query:

If tblTicketAttribute.Attribute= "Closed" then <do not display record, move on to next record>

Any suggestions?

you mean each ticketid group containing atleast a closed attribute valued record should be excluded ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources