Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 24th, 2007, 05:39 AM
Registered User
 
Join Date: May 2007
Location: , , .
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



Reply With Quote
  #2 (permalink)  
Old May 28th, 2007, 05:00 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
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
Reply With Quote
  #3 (permalink)  
Old May 31st, 2007, 01:56 AM
Registered User
 
Join Date: May 2007
Location: , , .
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
Reply With Quote
  #4 (permalink)  
Old May 31st, 2007, 02:07 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
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
Reply With Quote
  #5 (permalink)  
Old June 2nd, 2007, 04:35 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 12:00 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.