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

Well, I can at least give you an idea :-)

I haven't been able to test the code but the idea should work. First, you find a list of all the different (param_name, param_value) pairs for each report, which includes the duplicates:

select s.report_id,P.param_name,PV.param_value
from Scheduled S
join Parameters P
    on S.report_id = P.report_id
join Parametervalues PV on
    S.id = PV.sched_id
    P.id = PV.param_id
group by s.report_id, p.param_name, pv.param_value

Then you just enclose the above statement within another select that groups by just report_id+param_name and count how many lines for each pair:

select report_id, param_name
from
(select s.report_id,P.param_name,PV.param_value
from Scheduled S
join Parameters P
    on S.report_id = P.report_id
join Parametervalues PV on
    S.id = PV.sched_id
    P.id = PV.param_id
group by s.report_id, p.param_name, pv.param_value) C
group by c.report_id, c.param_name
having count(*) > 1

Regards,
Palli

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.