Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 24th, 2007, 05:39 AM
Registered User
 
Join Date: May 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default duplicate records

Hi,

I have 3 tables Scheduled, parameters, parametervalues. help me to identify duplicate scheduled instances. To be a duplicate all of the following must be the same:

RID (ReportID)
Delivery Method (PDF or Excel)
Frequency
All parameter values must be identical

the delivery method and the frequency for the particular RID is stored in the Scheduled table. so there is no problem with these 2 columns.

for the parameter values the data is stored in the parameters and
parametervalues table. these 2 tables are joined using Scheduled table.

example

Scheduled

id report_id delivery_method freq_id
555 95 2 2
560 95 2 2
565 96 2 2
566 96 2 2

Paramerters

id report_id param_name
107 95 @loc
108 95 @Cust
110 96 @Cust
111 96 @Vendor

Parametervalues

id param_id sched_id param_value

1361 107 555 REPUBLIC TITLE DALLAS
1362 108 555 HUD TX
1381 107 560 REPUBLIC TITLE DALLAS
1382 108 560 HUD TX
1111 110 565 HUD CO
1112 111 565 Sample
1113 110 566 HUD CO
1114 110 566 Sample2

the parametervalues.param_id = parameters.id and parametervalues.sched_id = scheduled.id

now i want to find out if all of the parameter values must be identical. now in this case for report_id = 95 the 2 different schedules(555,560) the param @loc is same i.e "REPUBLIC TITLE DALLAS", and the second param @Cust = HUD TX is same. so the 2 schedules are same i.e. duplicate.

but in the case of report_id = 96, the param @vendor is different for 2 schedules... so it not a duplicate.

please help me to find out the duplicate.

Thank you



 
Old May 28th, 2007, 05:00 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

IMO, you need to create a list of ROWs of all variables that you wish to consider for duplicates using join between these tables and then look for count(*) > 1 grouped by all the variable columns in question in order.

Cheers

_________________________
- Vijay G
Strive for Perfection
 
Old May 31st, 2007, 01:56 AM
Registered User
 
Join Date: May 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks for your reply.

if we query using group by and having it will pull records where any one parameter value matched for the same report_id.

example:
report param_name param_value
_id

61 @Customer HOME LOANS
61 Product SUPER
61 ShowChart NO
61 @StartDate 5/12/2006
61 @EndDate 6/12/2006
61 @Division NMN
61 @UseBizDaysOnly YES
61 @SubtractDaysInLegal YES
61 Customer Location ALL
61 @Customer COMMERCE MORTGAGE
61 Product SUPER
61 ShowChart NO
61 @StartDate 10/12/2006
61 @EndDate 13/12/2006
61 @Division NMN
61 @UseBizDaysOnly YES
61 @SubtractDaysInLegal YES
61 Customer Location ALL

when we query using group by having the result will be

report param_name param_value
_id
61 @Division NMN
61 @SubtractDaysInLegal YES
61 @UseBizDaysOnly YES
61 Customer Location ALL
61 Product SUPER
61 ShowChart NO

because these parameter values are repeated. but there should be no record for this report_id. only if all the parameter values matches for a particular report_id, it should return the report_id and values.

if you get any idea please let me know.

thanks for your time.


Quote:
quote:Originally posted by happygv
 IMO, you need to create a list of ROWs of all variables that you wish to consider for duplicates using join between these tables and then look for count(*) > 1 grouped by all the variable columns in question in order.

Cheers

_________________________
- Vijay G
Strive for Perfection
 
Old May 31st, 2007, 02:07 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Here, how do you identify a COMPLETE set of rows that pertains to one Report against which you will compare the other complete set to see if it is duplicate or not?

_________________________
- Vijay G
Strive for Perfection
 
Old June 2nd, 2007, 04:35 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Vanitha,

When you post a problem like "if we query using group by and having it will pull records"... it would really be a lot easier to help you if you also posted the offending code.

--Jeff Moden





Similar Threads
Thread Thread Starter Forum Replies Last Post
Duplicate Records in a table i_shahid SQL Language 2 February 7th, 2008 07:40 PM
duplicate records vanitha Reporting Services 2 May 31st, 2007 01:54 AM
Delete Duplicate Records prasanta2expert SQL Server 2000 9 December 15th, 2006 10:44 PM
Deleting duplicate records ninel SQL Server 2000 3 June 14th, 2006 02:12 PM
Duplicate Records mrookey Dreamweaver (all versions) 1 April 15th, 2005 11:23 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.