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

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

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.

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

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