SQL Query help

[gennaroalpha7]

Hi -

I would like help with a possible SQL query. Please review the scenario.

I have an assignment field that people 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 my fields.

Ticket field: CallLog.CallID

Assignment field: Asgnmnt.Assignee

Thanks.

G

 

[gmmastros]

You haven't given us enough information. Looks like you are using 2 tables (CallLog and Asgnmnt). How are these tables related?

What have you tried so far?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gk53]

it is not enough information, but something like
select C.CallID, assignNum
from CallLog C
left join
(select count(*) as assignNum, CallID
from Asgnmnt
group by CallID) A
on c.CallID = A.CallID

 

[gennaroalpha7]

Hi-

What information do you need?

The two tables are related through CallLog.CallID, the ticket number.

Thanks.

G

 

[gennaroalpha7]

Can someone please help with the above/below query. I am a beginning SQL learner. Please explain/review the above/below query for mistakes - I would like to try this query. If more information is needed please let me know and I can supply it.

Thanks.

Select CallLog.CallID, assignNum
from CallLog C
left join
(select count(*) as assignNum, CallID
from Asgnmnt
group by CallID) A
on CallLog.CallID = A.CallID

 

[imex]

Hi,

Can you post a small sample of data from the two tables and its expected result?

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[gennaroalpha7]

Here' the sample data with the field headings. The tickets listed have multiple assignments/technicians assigned to them. I am trying to print out tickets that have only one assignment/technician to the ticket. If you will, tickets that only one person worked on the ticket.


Open Date Ticket # 1st Level Assignee Date Assign Time Assign Ticket Owner Group Name

2013-12-01 01125474 Lois Lane 2013-12-01 10:12:46 LIMM01 BWHELPDESK

2013-12-01 01125487 Iron Man 2013-12-01 21:23:52 VAZduco BWHELPDESK


G.

 

[imex]

The sample data you posted is from which table?
And the sample data from another table?

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[gennaroalpha7]

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

Thank you.

Gennaro

 

[imex]

Try:

with CTE_A as
(
    select CallID 
    from Asgnmnt
    group by CallID
    having count(*) = 1
)

select
    cl.RecvdDate,
    cl.CallID,
    a.Assignee,
    a.DateAssign,
    a.TimeAssign,
    cl.Tracker,
    a.GroupName
from CTE_A as ct
inner join CallLog as cl
    on cl.CallID = ct.CallID
inner join Asgnmnt as a
    on a.CallID = ct.CallID

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[gennaroalpha7]

Can you explain it to me...it will help my learning of SQL tremendously.

Thanks.

Gennaro

 

[imex]

The query of the CTE was used to obtain the tickets only with a assignment ( Having count (*) = 1 ).
After simply one Join of the CTE with others tables to get the others columns needed.

About CTE:

-

http://msdn.microsoft.com/pt-br/library/ms175972.aspx

-

http://www.codeproject.com/Articles/265371/Common-Table-Expressions-CTE-in-SQL-SERVER-2008

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[gennaroalpha7]

Thanks for your help. Have a good one.

Gennaro

 

[gennaroalpha7]

Hi imex -

One more question please.

Do I insert the code exactly as you posted?

Thanks.

Gennaro

 

[imex]

Yes, and if any errors occur post the message.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[gennaroalpha7]

Hi imex-

Getting an error. Here it is...

Failed to retrieve data from the database.
Details: 42S02:[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid
object name 'Asgnmnt'.[Database Vender Code: 208]


FYI, Between the two tables, here's how they are related. I looked at the links between them.

CallID
and
DTLastMod

Thanks.

Gennaro

 

[gennaroalpha7]

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

DTLastMod

1,385,921,662

Thanks.

Gennaro

 

[gennaroalpha7]

imex -

FYI, I am using Crystal Reports to add the SQL code. I don't know if that matters.

thanks

Gennaro

 

[imex]

The error message indicates that Asgnmnt table does not exist in your database.

Believe it would be better to test this query using SQL Server Management Studio.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[gennaroalpha7]

Hi imex -

FYI, I added the Heatdb in front of the table(s) and it worked great.

Thanks.

Gennaro