December 9, 2011 at 2:12 am
Hi,
select
ixo_rlt_code AS role_name,
ind_membership_id_ext AS mbr_id,
ixo_start_date AS start_dt,
ixo_end_date AS end_dt
from co_individual_x_organization ixo
join co_organization org on ixo_org_cst_key = org_cst_key
join co_individual ind on ixo_ind_cst_key = ind_cst_key
join co_individual_ext ind_ext on ind_cst_key = ind_cst_key_ext
where org_ogt_code in ('TRF')
and ixo_delete_flag = 0
and org_delete_flag = 0
and ind_delete_flag = 0
and ind_membership_id_ext in (2480940,5585218,5607247,6313170)
order by 1,2
My above sql query is giving me duplicate records for the mentioned id's
I want to fetch the distinct records for the mentioned id's without using distinct clause.
So can you provide me the logic for the how to show the distinct records.
The mentioned sql is my source SQL and will populate my destination table.
the primary key for my destination table is on ixo_rlt_code,
ind_membership_id_ext,ixo_start_date
below records are coming from my sql
role_name mbr_id start_dt end_dt
Sponsor Counselor24809402011-07-01 00:00:002012-06-30 00:00:00
Sponsor Counselor24809402011-07-01 00:00:002012-06-30 00:00:00
Sponsor Counselor55852182011-07-01 00:00:002012-06-30 00:00:00
Sponsor Counselor55852182011-07-01 00:00:002012-06-30 00:00:00
Sponsor Counselor56072472011-07-01 00:00:002012-06-30 00:00:00
Sponsor Counselor56072472011-07-01 00:00:002012-06-30 00:00:00
Sponsor Counselor63131702011-07-01 00:00:002012-06-30 00:00:00
Sponsor Counselor63131702011-07-01 00:00:002012-06-30 00:00:00
So can you help me out for this issue.
Regards,
Kiran
December 9, 2011 at 2:19 am
you could but a group by or a distinct in the query to rollup the data so that the duplicates are removed.
group by
select
ixo_rlt_code AS role_name,
ind_membership_id_ext AS mbr_id,
ixo_start_date AS start_dt,
ixo_end_date AS end_dt
from co_individual_x_organization ixo
join co_organization org on ixo_org_cst_key = org_cst_key
join co_individual ind on ixo_ind_cst_key = ind_cst_key
join co_individual_ext ind_ext on ind_cst_key = ind_cst_key_ext
where org_ogt_code in ('TRF')
and ixo_delete_flag = 0
and org_delete_flag = 0
and ind_delete_flag = 0
and ind_membership_id_ext in (2480940,5585218,5607247,6313170)
group by ixo_rlt_code,ind_membership_id_ext,ixo_start_date,ixo_end_date
order by 1,2
distinct
select distinct
ixo_rlt_code AS role_name,
ind_membership_id_ext AS mbr_id,
ixo_start_date AS start_dt,
ixo_end_date AS end_dt
from co_individual_x_organization ixo
join co_organization org on ixo_org_cst_key = org_cst_key
join co_individual ind on ixo_ind_cst_key = ind_cst_key
join co_individual_ext ind_ext on ind_cst_key = ind_cst_key_ext
where org_ogt_code in ('TRF')
and ixo_delete_flag = 0
and org_delete_flag = 0
and ind_delete_flag = 0
and ind_membership_id_ext in (2480940,5585218,5607247,6313170)
order by 1,2
December 9, 2011 at 2:22 am
Why? What do you think is wrong with DISTINCT?
SELECT role_name, mbr_id, start_dt, end_dt
FROM (SELECT ixo_rlt_code AS role_name, ind_membership_id_ext AS mbr_id, ixo_start_date AS start_dt, ixo_end_date AS end_dt
FROM co_individual_x_organization ixo
JOIN co_organization org ON ixo_org_cst_key = org_cst_key
JOIN co_individual ind ON ixo_ind_cst_key = ind_cst_key
JOIN co_individual_ext ind_ext ON ind_cst_key = ind_cst_key_ext
WHERE org_ogt_code IN ('TRF') AND ixo_delete_flag = 0 AND org_delete_flag = 0
AND ind_delete_flag = 0 AND ind_membership_id_ext IN (2480940, 5585218, 5607247, 6313170)) workQuery
GROUP BY role_name, mbr_id, start_dt, end_dt
ORDER BY 1, 2
SELECT ixo_rlt_code AS role_name, ind_membership_id_ext AS mbr_id, ixo_start_date AS start_dt, ixo_end_date AS end_dt
FROM co_individual_x_organization ixo
JOIN co_organization org ON ixo_org_cst_key = org_cst_key
JOIN co_individual ind ON ixo_ind_cst_key = ind_cst_key
JOIN co_individual_ext ind_ext ON ind_cst_key = ind_cst_key_ext
WHERE org_ogt_code IN ('TRF') AND ixo_delete_flag = 0 AND org_delete_flag = 0
AND ind_delete_flag = 0 AND ind_membership_id_ext IN (2480940, 5585218, 5607247, 6313170)
GROUP BY ixo_rlt_code, ind_membership_id_ext, ixo_start_date, ixo_end_date
ORDER BY 1, 2
SELECT DISTINCT ixo_rlt_code AS role_name, ind_membership_id_ext AS mbr_id, ixo_start_date AS start_dt, ixo_end_date AS end_dt
FROM co_individual_x_organization ixo
JOIN co_organization org ON ixo_org_cst_key = org_cst_key
JOIN co_individual ind ON ixo_ind_cst_key = ind_cst_key
JOIN co_individual_ext ind_ext ON ind_cst_key = ind_cst_key_ext
WHERE org_ogt_code IN ('TRF') AND ixo_delete_flag = 0 AND org_delete_flag = 0
AND ind_delete_flag = 0 AND ind_membership_id_ext IN (2480940, 5585218, 5607247, 6313170)
ORDER BY 1, 2
If you run the below, you can check to see which is best. Run 10 times and average out the execution times.
PRINT '========== BASELINE =========='
SET STATISTICS TIME ON
SELECT COUNT(*)
FROM co_individual_x_organization ixo
JOIN co_organization org ON ixo_org_cst_key = org_cst_key
JOIN co_individual ind ON ixo_ind_cst_key = ind_cst_key
JOIN co_individual_ext ind_ext ON ind_cst_key = ind_cst_key_ext
WHERE org_ogt_code IN ('TRF') AND ixo_delete_flag = 0 AND org_delete_flag = 0
AND ind_delete_flag = 0 AND ind_membership_id_ext IN (2480940, 5585218, 5607247, 6313170)
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
--HOLDING VARIABLE TO REMOVE DISPLAY TIME FROM QUERY TIME
DECLARE @Holder VARCHAR(MAX)
PRINT '========== SubQuery =========='
SET STATISTICS TIME ON
SELECT @Holder = role_name
FROM (SELECT role_name, mbr_id, start_dt, end_dt
FROM (SELECT ixo_rlt_code AS role_name, ind_membership_id_ext AS mbr_id, ixo_start_date AS start_dt, ixo_end_date AS end_dt
FROM co_individual_x_organization ixo
JOIN co_organization org ON ixo_org_cst_key = org_cst_key
JOIN co_individual ind ON ixo_ind_cst_key = ind_cst_key
JOIN co_individual_ext ind_ext ON ind_cst_key = ind_cst_key_ext
WHERE org_ogt_code IN ('TRF') AND ixo_delete_flag = 0 AND org_delete_flag = 0
AND ind_delete_flag = 0 AND ind_membership_id_ext IN (2480940, 5585218, 5607247, 6313170)) workQuery
GROUP BY role_name, mbr_id, start_dt, end_dt) b
ORDER BY role_name, mbr_id
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== GROUP BY =========='
SET STATISTICS TIME ON
SELECT @Holder = role_name
FROM (SELECT ixo_rlt_code AS role_name, ind_membership_id_ext AS mbr_id, ixo_start_date AS start_dt, ixo_end_date AS end_dt
FROM co_individual_x_organization ixo
JOIN co_organization org ON ixo_org_cst_key = org_cst_key
JOIN co_individual ind ON ixo_ind_cst_key = ind_cst_key
JOIN co_individual_ext ind_ext ON ind_cst_key = ind_cst_key_ext
WHERE org_ogt_code IN ('TRF') AND ixo_delete_flag = 0 AND org_delete_flag = 0
AND ind_delete_flag = 0 AND ind_membership_id_ext IN (2480940, 5585218, 5607247, 6313170)
GROUP BY ixo_rlt_code, ind_membership_id_ext, ixo_start_date, ixo_end_date) a
ORDER BY role_name, mbr_id
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== DISTINCT =========='
SET STATISTICS TIME ON
SELECT @Holder = role_name
FROM (SELECT DISTINCT ixo_rlt_code AS role_name, ind_membership_id_ext AS mbr_id, ixo_start_date AS start_dt, ixo_end_date AS end_dt
FROM co_individual_x_organization ixo
JOIN co_organization org ON ixo_org_cst_key = org_cst_key
JOIN co_individual ind ON ixo_ind_cst_key = ind_cst_key
JOIN co_individual_ext ind_ext ON ind_cst_key = ind_cst_key_ext
WHERE org_ogt_code IN ('TRF') AND ixo_delete_flag = 0 AND org_delete_flag = 0
AND ind_delete_flag = 0 AND ind_membership_id_ext IN (2480940, 5585218, 5607247, 6313170)) a
ORDER BY role_name, mbr_id
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
December 9, 2011 at 2:25 am
Thanks Sir,
its working....
I was trying the same in my SSIS package but was not working properly...
I have modified my source sql..
Thanks for your help
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply