I need help reducing redundant records

Question

I need help reducing redundant records

grasspicker61

SSC Enthusiast

Points: 106
More actions
August 31, 2010 at 9:50 am

#226431

Can anyone tell me where my error is, this query should be retreiving one data set for each "OCC_Ticket_ID"
USE [IRTHNet]
GO
/****** Object: StoredProcedure [dbo].[ReportSQLTicketScreeningAudit082510] Script Date: 08/31/2010 08:47:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ReportSQLTicketScreeningAudit082510]
(
@CompanyID int,
@DateFrom datetime,
@DateTo datetime,
@TimeZoneOffset int,
@user-id varchar (8000)
)
AS
BEGIN
-- convert the date from from the callers time zone to ours
select @DateFrom = dateadd(mi, -@TimeZoneOffset, @DateFrom)
-- add a day to the dateto and convert it from the callers time zone to ours
select @DateTo = DATEADD(dd, 1, @DateTo)
select @DateTo = dateadd(mi, -@TimeZoneOffset, @DateTo)
SELECT
TICKET.OCC_Ticket_IDAS 'Ticket #',
dateadd(mi, @TimeZoneOffset, TICKET.Received_DateTime)AS 'Received',
HOST.NameAS 'Call Center',
CDC.CDCAS 'Code',
PERSON.First_Name + ' ' + Last_NameAS 'User',
STAT_PRODUCTIVITY.ActionAS 'Action',
RESPONSE.NameAS 'Response',
TICKET_NOTE_HISTORY.NoteAS 'Notes'
FROM
TICKET WITH (NOLOCK)
INNER JOIN
HOST WITH (NOLOCK)ON TICKET.Host_ID = HOST.Host_ID
INNER JOIN
CDC WITH (NOLOCK) ON TICKET.CDC_ID = CDC.CDC_ID
INNER JOIN
TICKET_LOCATE_INFO WITH (NOLOCK) ON TICKET.Ticket_ID = TICKET_LOCATE_INFO.Ticket_ID
INNER JOIN
RESPONSE WITH (NOLOCK) ON TICKET_LOCATE_INFO.Response_ID = RESPONSE.Response_ID
INNER JOIN
TICKET_NOTE_HISTORY WITH (NOLOCK) ON TICKET_LOCATE_INFO.Ticket_Locate_Info_ID = TICKET_NOTE_HISTORY.Ticket_Locate_Info_ID
INNER JOIN
PERSON WITH (NOLOCK) ON TICKET_NOTE_HISTORY.Operator_ID = PERSON.Person_ID
INNER JOIN
STAT_PRODUCTIVITY WITH (NOLOCK) ON PERSON.Person_ID = STAT_PRODUCTIVITY.Person_ID
WHERE
(TICKET.Received_DateTime >= @DateFrom)
AND
(TICKET.Received_DateTime < @DateTo)
AND
(PERSON.Person_ID = @user-id)
ORDER BY
TICKET.OCC_Ticket_ID
RETURN
END

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

most likely you have multiple records for each ticket id in the additional tables like TICKET_LOCATE_INFO or any of the other tables.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

grasspicker61,

It would be helpful if you let everyone know what is not working correctly with the existing procedure. Are you getting error message? Invalid result? What's the problem?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

grasspicker61 SSC Enthusiast Points: 106 More actions · Answer 3

Sorry if I was unclear, when I run this script, it returns dozens of identical records. I wonder if it is a problem with my JOIN Statements, but am unsure, after trying several different methods.:w00t:

WayneS SSC Guru Points: 95461 More actions · Answer 4

grasspicker61 (8/31/2010)
Sorry if I was unclear, when I run this script, it returns dozens of identical records. I wonder if it is a problem with my JOIN Statements, but am unsure, after trying several different methods.:w00t:

No, your query looks good. However, like Sean mentioned, at least one of those tables has a 1->Many relationship, and at least one of those tables has > 1 entry for the join condition. If the result set is exactly duplicated (across all columns), then the most like candidate for this is (as Sean also mentioned) the TICKET_LOCATE_INFO table (since it is only used for JOINing to other tables - there isn't any data from that table being selected).

If every row is duplicated, then there are two ways to get rid of the duplicates:

1. On the tables with a 1->Many relationship, you need a further filter to get the appropriate record, or

2. Make the "SELECT" a "SELECT DISTINCT".

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Steve Jones - SSC Editor SSC Guru Points: 738632 More actions · Answer 5

I'd follow Wayne's advice. Try a distinct, but it the records aren't completely unique, you might need another filter.