Author |
Topic |
gennaroalpha7
Starting Member
3 Posts |
Posted - 2014-01-22 : 12:34:33
|
Hi -I would like help with a possible SQL query. Please review the scenario.I have an assignment field that people (assignments)assign people to work on a ticket. The ticket can have multiple or single assignments, meaning how many technicians work on the ticket. I am trying to get records/tickets that only have a single assignment. Is it possible to isolate these tickets with a query, since a ticket with multiple or single assignments is still under the same ticket number.Here are a couple of my fields.Ticket field: CallLog.CallIDAssignment field: Asgnmnt.AssigneeData Example:Open Date Ticket # 1st Level Assignee DateAssign Ticket Owner2013-12-01 1125474 Mel Lim 2013-12-01 LIMM01If you need more information please let me know. Thanks.G |
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-01-22 : 16:45:41
|
Had you also posted the query, producing your required output, we could have given you a much better answer.Assuming the ticket field is also in assignment table and is called CallID, you could do something like this, to find tickets with only one assignment:select CallID from Asgnmnt group by CallID having count(*)=1 |
|
|
gennaroalpha7
Starting Member
3 Posts |
Posted - 2014-01-22 : 17:03:58
|
Here are the table/fields:The Open Date field is from table/field: CallLog.RecvdDateThe Ticket number field is from table/field: CallLog.CallIDThe 1st Level Assignee field is from table/field: Asgnmnt.AssigneeThe Date Assign field is from table/field: Asgnmnt.DateAssignThe Time Assign field is from table/field: Asgnmnt.TimeAssignThe Ticket Owner field is from table/field: CallLog.TrackerThe Group Name field is from table/field: Asgnmnt.GroupNameCallLog.CallID is the PrimaryHere's how the tables are related...Between the two (CallLog and Asgnmnt) tables, here's how they are related. CallIDandDTLastModHere's a data example from the table/field Asgnmnt.DTLastMod DTLastMod1,385,921,662 |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-01-22 : 17:13:30
|
Maybe this:select cl.RecvdDate ,cl.CallID ,a2.Assignee ,a2.DateAssign ,a2.TimeAssign ,cl.Tracker ,a2.GroupName from (select CallID from Asgnmnt group by CallID having count(*)=1 ) as a1 inner join Asgnmnt as a2 on a2.CallID=a1.CallID inner join CallLog as c on c.CallID=a2.CallID and c.DTLastMod=a2.DTLastMod |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2014-01-23 : 02:44:18
|
rohit kumar, please start a new thread for your question KH[spoiler]Time is always against us[/spoiler] |
|
|
gennaroalpha7
Starting Member
3 Posts |
Posted - 2014-01-23 : 11:20:14
|
Hi - I received this error message, whick indicates that the table does not exist, it does. Any Ideas about this error?Failed to retrieve data from the database.Details: 42S02:[Microsoft][ODBC SQL Server Driver][SQL Server]Invalidobject name 'Asgnmnt'.[Database Vender Code: 208]Thanks. |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-01-23 : 12:28:54
|
quote: Originally posted by gennaroalpha7 Hi - I received this error message, whick indicates that the table does not exist, it does. Any Ideas about this error?Failed to retrieve data from the database.Details: 42S02:[Microsoft][ODBC SQL Server Driver][SQL Server]Invalidobject name 'Asgnmnt'.[Database Vender Code: 208]Thanks.
Try this, where you replace databasename with the actual name of you database:select cl.RecvdDate ,cl.CallID ,a2.Assignee ,a2.DateAssign ,a2.TimeAssign ,cl.Tracker ,a2.GroupName from (select CallID from databasename.dbo.Asgnmnt group by CallID having count(*)=1 ) as a1 inner join databasename.dbo.Asgnmnt as a2 on a2.CallID=a1.CallID inner join databasename.dbo.CallLog as c on c.CallID=a2.CallID and c.DTLastMod=a2.DTLastMod |
|
|
gennaroalpha7
Starting Member
3 Posts |
Posted - 2014-01-23 : 16:10:38
|
Thanks so much.... |
|
|
|