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
 Help with SQL query

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.CallID

Assignment field: Asgnmnt.Assignee

Data Example:

Open Date Ticket # 1st Level Assignee DateAssign Ticket Owner

2013-12-01 1125474 Mel Lim 2013-12-01 LIMM01

If 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
Go to Top of Page

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.RecvdDate

The Ticket number field is from table/field: CallLog.CallID

The 1st Level Assignee field is from table/field: Asgnmnt.Assignee

The Date Assign field is from table/field: Asgnmnt.DateAssign

The Time Assign field is from table/field: Asgnmnt.TimeAssign

The Ticket Owner field is from table/field: CallLog.Tracker

The Group Name field is from table/field: Asgnmnt.GroupName

CallLog.CallID is the Primary

Here's how the tables are related...

Between the two (CallLog and Asgnmnt) tables, here's how they are related.

CallID
and
DTLastMod


Here's a data example from the table/field Asgnmnt.DTLastMod

DTLastMod

1,385,921,662

Go to Top of Page

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
Go to Top of Page

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]

Go to Top of Page

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]Invalid
object name 'Asgnmnt'.[Database Vender Code: 208]


Thanks.
Go to Top of Page

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]Invalid
object 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
Go to Top of Page

gennaroalpha7
Starting Member

3 Posts

Posted - 2014-01-23 : 16:10:38
Thanks so much....
Go to Top of Page
   

- Advertisement -