Getting rid of duplicates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Getting rid of duplicates

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-02-01 : 10:06:45

When I joined a table to my stored procedure I'm getting duplicates.


Select
Case
when o.sort = 'x' then 'Area ' + right (o.reg,1)
else o.reg 
end AS reg1
, 

o.area, o.sort, o.reg, o.region,o.dist, o.doc, o.rpt, 
o.[dec 09], o.[dec 09 rank], o.[dec 09 icnt],o.[dec 09 tcnt],
o.[jan 10], o.[jan 10 rank], o.[jan 10 icnt],o.[jan 10 tcnt],
o.[feb 10], o.[feb 10 rank], o.[feb 10 icnt],o.[feb 10 tcnt],
o.[mar 10], o.[mar 10 rank], o.[mar 10 icnt],o.[mar 10 tcnt],
o.[apr 10], o.[apr 10 rank], o.[apr 10 icnt],o.[apr 10 tcnt],
o.[may 10], o.[may 10 rank], o.[may 10 icnt],o.[may 10 tcnt],
o.[jun 10], o.[jun 10 rank], o.[jun 10 icnt],o.[jun 10 tcnt],
o.[jul 10], o.[jul 10 rank], o.[jul 10 icnt],o.[jul 10 tcnt],
o.[aug 10], o.[aug 10 rank], o.[aug 10 icnt],o.[aug 10 tcnt],
o.[sep 10], o.[sep 10 rank], o.[sep 10 icnt],o.[sep 10 tcnt],
o.[oct 10], o.[oct 10 rank], o.[oct 10 icnt],o.[oct 10 tcnt],
o.[nov 10], o.[nov 10 rank], o.[nov 10 icnt],o.[nov 10 tcnt],
o.[dec 10], o.[dec 10 rank], o.[dec 10 icnt],o.[dec 10 tcnt],
n.[jan 11], n.[jan 11 rank], n.[jan 11 icnt],n.[jan 11 tcnt]

from iclaimspivotold o
join ribinfo n on n.reg=o.reg
where o.rpt = '2' and (o.sort = @Region or o.sort='1') or (o.rpt = '2' and o.region= @Region  and o.sort = 'x' and o.doc='000')

Here are the results


NAT	00	1	NAT	0	000	000	2	34.0	10312	62023	182386	38.2	10270	99968	261375	37.3	10240	72388	194126	37.0	8858	75952	205022	37.4	8553	87701	234193	37.2	8309	69989	188248	38.1	8226	71082	186459	40.1	8248	90548	225632	40.5	8283	74147	183204	39.1	8106	72851	186515	37.9	7904	98113	258853	37.8	7898	121966	322568	37.6	8200	143187	380327	71.1	9739	103871	146108
BOS	00	A	BOS	A	000	000	2	30.5	9360	2697	8846	34.6	9421	4439	12811	34.3	9471	3163	9215	34.6	8261	3430	9906	35.8	8102	4059	11339	32.0	6675	2822	8813	34.3	7119	2960	8627	35.0	6669	3626	10357	36.6	7151	3171	8666	35.5	6957	3159	8909	34.9	6901	4465	12791	36.0	7316	5684	15784	34.8	7293	6584	18907	65.7	9379	4629	7044
Area 1	01	x	01	A	000	000	2	28.9	8859	877	3034	33.9	9222	1529	4512	35.4	9779	1094	3089	33.5	7933	1152	3436	35.3	7972	1362	3860	30.7	6261	876	2855	33.9	7002	975	2876	33.9	6292	1175	3469	36.0	6964	1056	2931	34.0	6452	1009	2968	33.1	6282	1440	4347	35.5	7176	1923	5421	33.9	7014	2151	6350	56.9	8718	1421	2498
Area 1	01	x	01	A	000	000	2	28.9	8859	877	3034	33.9	9222	1529	4512	35.4	9779	1094	3089	33.5	7933	1152	3436	35.3	7972	1362	3860	30.7	6261	876	2855	33.9	7002	975	2876	33.9	6292	1175	3469	36.0	6964	1056	2931	34.0	6452	1009	2968	33.1	6282	1440	4347	35.5	7176	1923	5421	33.9	7014	2151	6350	57.1	8734	1422	2492
Area 1	01	x	01	A	000	000	2	28.9	8859	877	3034	33.9	9222	1529	4512	35.4	9779	1094	3089	33.5	7933	1152	3436	35.3	7972	1362	3860	30.7	6261	876	2855	33.9	7002	975	2876	33.9	6292	1175	3469	36.0	6964	1056	2931	34.0	6452	1009	2968	33.1	6282	1440	4347	35.5	7176	1923	5421	33.9	7014	2151	6350	57.6	8785	1127	1955
Area 3	03	x	03	A	000	000	2	33.0	10058	936	2835	36.6	9913	1420	3876	36.2	9962	1067	2945	38.3	9193	1200	3133	38.3	8814	1377	3597	35.0	7647	972	2778	37.0	7920	1006	2718	38.1	7684	1233	3233	38.8	7782	1075	2769	38.5	7922	1133	2940	38.5	8085	1564	4064	36.8	7565	1893	5143	37.0	8014	2277	6158	48.4	7690	1151	2376
Area 3	03	x	03	A	000	000	2	33.0	10058	936	2835	36.6	9913	1420	3876	36.2	9962	1067	2945	38.3	9193	1200	3133	38.3	8814	1377	3597	35.0	7647	972	2778	37.0	7920	1006	2718	38.1	7684	1233	3233	38.8	7782	1075	2769	38.5	7922	1133	2940	38.5	8085	1564	4064	36.8	7565	1893	5143	37.0	8014	2277	6158	73.0	9886	1580	2163
Area 3	03	x	03	A	000	000	2	33.0	10058	936	2835	36.6	9913	1420	3876	36.2	9962	1067	2945	38.3	9193	1200	3133	38.3	8814	1377	3597	35.0	7647	972	2778	37.0	7920	1006	2718	38.1	7684	1233	3233	38.8	7782	1075	2769	38.5	7922	1133	2940	38.5	8085	1564	4064	36.8	7565	1893	5143	37.0	8014	2277	6158	70.4	9706	2045	2903
Area 3	03	x	03	A	000	000	2	33.0	10058	936	2835	36.6	9913	1420	3876	36.2	9962	1067	2945	38.3	9193	1200	3133	38.3	8814	1377	3597	35.0	7647	972	2778	37.0	7920	1006	2718	38.1	7684	1233	3233	38.8	7782	1075	2769	38.5	7922	1133	2940	38.5	8085	1564	4064	36.8	7565	1893	5143	37.0	8014	2277	6158	67.5	9517	1538	2278
Area 2	02	x	02	A	000	000	2	29.7	9125	884	2977	33.7	9172	1490	4423	31.5	8662	1002	3181	32.3	7494	1078	3337	34.0	7618	1320	3882	30.6	6235	974	3180	32.3	6433	979	3033	33.3	6051	1218	3655	35.1	6685	1040	2966	33.9	6415	1017	3001	33.4	6430	1461	4380	35.8	7273	1868	5220	33.7	6953	2156	6399	68.1	9559	1627	2389
Area 3	03	x	03	A	000	000	2	33.0	10058	936	2835	36.6	9913	1420	3876	36.2	9962	1067	2945	38.3	9193	1200	3133	38.3	8814	1377	3597	35.0	7647	972	2778	37.0	7920	1006	2718	38.1	7684	1233	3233	38.8	7782	1075	2769	38.5	7922	1133	2940	38.5	8085	1564	4064	36.8	7565	1893	5143	37.0	8014	2277	6158	62.1	9124	1801	2901
Area 1	01	x	01	A	000	000	2	28.9	8859	877	3034	33.9	9222	1529	4512	35.4	9779	1094	3089	33.5	7933	1152	3436	35.3	7972	1362	3860	30.7	6261	876	2855	33.9	7002	975	2876	33.9	6292	1175	3469	36.0	6964	1056	2931	34.0	6452	1009	2968	33.1	6282	1440	4347	35.5	7176	1923	5421	33.9	7014	2151	6350	62.2	9132	1400	2251
Area 2	02	x	02	A	000	000	2	29.7	9125	884	2977	33.7	9172	1490	4423	31.5	8662	1002	3181	32.3	7494	1078	3337	34.0	7618	1320	3882	30.6	6235	974	3180	32.3	6433	979	3033	33.3	6051	1218	3655	35.1	6685	1040	2966	33.9	6415	1017	3001	33.4	6430	1461	4380	35.8	7273	1868	5220	33.7	6953	2156	6399	64.3	9288	3728	5796
Area 1	01	x	01	A	000	000	2	28.9	8859	877	3034	33.9	9222	1529	4512	35.4	9779	1094	3089	33.5	7933	1152	3436	35.3	7972	1362	3860	30.7	6261	876	2855	33.9	7002	975	2876	33.9	6292	1175	3469	36.0	6964	1056	2931	34.0	6452	1009	2968	33.1	6282	1440	4347	35.5	7176	1923	5421	33.9	7014	2151	6350	65.5	9370	1606	2453
Area 2	02	x	02	A	000	000	2	29.7	9125	884	2977	33.7	9172	1490	4423	31.5	8662	1002	3181	32.3	7494	1078	3337	34.0	7618	1320	3882	30.6	6235	974	3180	32.3	6433	979	3033	33.3	6051	1218	3655	35.1	6685	1040	2966	33.9	6415	1017	3001	33.4	6430	1461	4380	35.8	7273	1868	5220	33.7	6953	2156	6399	66.4	9424	2106	3170
Area 2	02	x	02	A	000	000	2	29.7	9125	884	2977	33.7	9172	1490	4423	31.5	8662	1002	3181	32.3	7494	1078	3337	34.0	7618	1320	3882	30.6	6235	974	3180	32.3	6433	979	3033	33.3	6051	1218	3655	35.1	6685	1040	2966	33.9	6415	1017	3001	33.4	6430	1461	4380	35.8	7273	1868	5220	33.7	6953	2156	6399	37.6	5360	932	2480
Area 1	01	x	01	A	000	000	2	28.9	8859	877	3034	33.9	9222	1529	4512	35.4	9779	1094	3089	33.5	7933	1152	3436	35.3	7972	1362	3860	30.7	6261	876	2855	33.9	7002	975	2876	33.9	6292	1175	3469	36.0	6964	1056	2931	34.0	6452	1009	2968	33.1	6282	1440	4347	35.5	7176	1923	5421	33.9	7014	2151	6350	101.5	11152	2406	2371
Area 3	03	x	03	A	000	000	2	33.0	10058	936	2835	36.6	9913	1420	3876	36.2	9962	1067	2945	38.3	9193	1200	3133	38.3	8814	1377	3597	35.0	7647	972	2778	37.0	7920	1006	2718	38.1	7684	1233	3233	38.8	7782	1075	2769	38.5	7922	1133	2940	38.5	8085	1564	4064	36.8	7565	1893	5143	37.0	8014	2277	6158	107.2	11288	2041	1904
Area 3	03	x	03	A	000	000	2	33.0	10058	936	2835	36.6	9913	1420	3876	36.2	9962	1067	2945	38.3	9193	1200	3133	38.3	8814	1377	3597	35.0	7647	972	2778	37.0	7920	1006	2718	38.1	7684	1233	3233	38.8	7782	1075	2769	38.5	7922	1133	2940	38.5	8085	1564	4064	36.8	7565	1893	5143	37.0	8014	2277	6158	96.9	10983	3759	3881
Area 2	02	x	02	A	000	000	2	29.7	9125	884	2977	33.7	9172	1490	4423	31.5	8662	1002	3181	32.3	7494	1078	3337	34.0	7618	1320	3882	30.6	6235	974	3180	32.3	6433	979	3033	33.3	6051	1218	3655	35.1	6685	1040	2966	33.9	6415	1017	3001	33.4	6430	1461	4380	35.8	7273	1868	5220	33.7	6953	2156	6399	98.2	11013	2692	2742
Area 2	02	x	02	A	000	000	2	29.7	9125	884	2977	33.7	9172	1490	4423	31.5	8662	1002	3181	32.3	7494	1078	3337	34.0	7618	1320	3882	30.6	6235	974	3180	32.3	6433	979	3033	33.3	6051	1218	3655	35.1	6685	1040	2966	33.9	6415	1017	3001	33.4	6430	1461	4380	35.8	7273	1868	5220	33.7	6953	2156	6399	98.3	11016	2266	2305
Area 1	01	x	01	A	000	000	2	28.9	8859	877	3034	33.9	9222	1529	4512	35.4	9779	1094	3089	33.5	7933	1152	3436	35.3	7972	1362	3860	30.7	6261	876	2855	33.9	7002	975	2876	33.9	6292	1175	3469	36.0	6964	1056	2931	34.0	6452	1009	2968	33.1	6282	1440	4347	35.5	7176	1923	5421	33.9	7014	2151	6350	95.7	10946	3013	3147
Area 1	01	x	01	A	000	000	2	28.9	8859	877	3034	33.9	9222	1529	4512	35.4	9779	1094	3089	33.5	7933	1152	3436	35.3	7972	1362	3860	30.7	6261	876	2855	33.9	7002	975	2876	33.9	6292	1175	3469	36.0	6964	1056	2931	34.0	6452	1009	2968	33.1	6282	1440	4347	35.5	7176	1923	5421	33.9	7014	2151	6350	91.6	10778	2934	3204
Area 1	01	x	01	A	000	000	2	28.9	8859	877	3034	33.9	9222	1529	4512	35.4	9779	1094	3089	33.5	7933	1152	3436	35.3	7972	1362	3860	30.7	6261	876	2855	33.9	7002	975	2876	33.9	6292	1175	3469	36.0	6964	1056	2931	34.0	6452	1009	2968	33.1	6282	1440	4347	35.5	7176	1923	5421	33.9	7014	2151	6350	89.4	10675	1833	2051
Area 2	02	x	02	A	000	000	2	29.7	9125	884	2977	33.7	9172	1490	4423	31.5	8662	1002	3181	32.3	7494	1078	3337	34.0	7618	1320	3882	30.6	6235	974	3180	32.3	6433	979	3033	33.3	6051	1218	3655	35.1	6685	1040	2966	33.9	6415	1017	3001	33.4	6430	1461	4380	35.8	7273	1868	5220	33.7	6953	2156	6399	85.1	10507	1864	2190
Area 2	02	x	02	A	000	000	2	29.7	9125	884	2977	33.7	9172	1490	4423	31.5	8662	1002	3181	32.3	7494	1078	3337	34.0	7618	1320	3882	30.6	6235	974	3180	32.3	6433	979	3033	33.3	6051	1218	3655	35.1	6685	1040	2966	33.9	6415	1017	3001	33.4	6430	1461	4380	35.8	7273	1868	5220	33.7	6953	2156	6399	85.2	10510	1730	2030
Area 1	01	x	01	A	000	000	2	28.9	8859	877	3034	33.9	9222	1529	4512	35.4	9779	1094	3089	33.5	7933	1152	3436	35.3	7972	1362	3860	30.7	6261	876	2855	33.9	7002	975	2876	33.9	6292	1175	3469	36.0	6964	1056	2931	34.0	6452	1009	2968	33.1	6282	1440	4347	35.5	7176	1923	5421	33.9	7014	2151	6350	85.2	10510	1839	2158
Area 3	03	x	03	A	000	000	2	33.0	10058	936	2835	36.6	9913	1420	3876	36.2	9962	1067	2945	38.3	9193	1200	3133	38.3	8814	1377	3597	35.0	7647	972	2778	37.0	7920	1006	2718	38.1	7684	1233	3233	38.8	7782	1075	2769	38.5	7922	1133	2940	38.5	8085	1564	4064	36.8	7565	1893	5143	37.0	8014	2277	6158	84.0	10445	2462	2932
Area 2	02	x	02	A	000	000	2	29.7	9125	884	2977	33.7	9172	1490	4423	31.5	8662	1002	3181	32.3	7494	1078	3337	34.0	7618	1320	3882	30.6	6235	974	3180	32.3	6433	979	3033	33.3	6051	1218	3655	35.1	6685	1040	2966	33.9	6415	1017	3001	33.4	6430	1461	4380	35.8	7273	1868	5220	33.7	6953	2156	6399	84.0	10445	2235	2661
Area 2	02	x	02	A	000	000	2	29.7	9125	884	2977	33.7	9172	1490	4423	31.5	8662	1002	3181	32.3	7494	1078	3337	34.0	7618	1320	3882	30.6	6235	974	3180	32.3	6433	979	3033	33.3	6051	1218	3655	35.1	6685	1040	2966	33.9	6415	1017	3001	33.4	6430	1461	4380	35.8	7273	1868	5220	33.7	6953	2156	6399	79.7	10251	1654	2075

This stored procedure works fine without the joined table

Select
Case
when sort = 'x' then 'Area ' + right(reg,1)
else reg
end AS reg1
, *
from [dbo].[iClaimsPivot]
where rpt = @rpt and (sort = @Region or sort='1') or (rpt = @Rpt and region= @Region and sort = 'x' and doc='000')
order by sort, reg


NAT	1	NAT	28.6	8201	175287	613045	27.8	8073	136122	489203	27.9	8011	148364	531894	29.2	7087	182450	625195	30.5	6904	152316	499660	31.8	6873	155689	489864	33.5	6829	206842	617115	33.4	6875	169247	507273	33.3	6693	165435	496538	33.2	6798	215262	649104	32.9	6737	262585	798188	32.1	6706	297667	926441	36.4	5038	221048	607686	0	00	000	000	1
BOS	A	BOS	25.8	7538	7666	29657	25.3	7502	5865	23223	25.5	7383	6482	25374	27.8	6705	8132	29303	27.6	6131	6379	23129	29.5	6303	6653	22572	30.1	5989	8407	27903	31.2	6258	7204	23118	31.6	6324	7215	22797	31.4	6418	9554	30439	31.8	6485	12152	38217	30.8	6367	13630	44266	35.0	4737	9736	27811	A	00	000	000	1
Area 1	x	01	24.5	7249	2433	9925	24.8	7346	1867	7518	24.1	7000	2012	8361	26.2	6275	2490	9504	25.8	5655	1913	7401	28.7	6127	2093	7293	28.4	5602	2588	9122	29.8	5950	2219	7444	29.6	5856	2220	7505	29.1	5878	2909	9991	29.9	6080	3730	12475	29.4	6063	4222	14385	33.0	4204	2926	8876	A	01	000	000	1
Area 2	x	02	25.1	7410	2658	10593	22.9	6874	1925	8390	23.7	6882	2167	9130	26.3	6304	2761	10505	27.1	6002	2290	8460	27.9	5929	2276	8163	28.8	5681	2918	10122	30.4	6082	2536	8338	30.5	6084	2422	7946	30.6	6238	3316	10824	32.0	6534	4329	13516	29.6	6110	4721	15943	35.1	4764	3503	9989	A	02	000	000	1
Area 3	x	03	28.2	8125	2575	9139	28.3	8190	2073	7315	29.2	8355	2303	7883	31.0	7571	2881	9294	29.9	6723	2176	7268	32.1	6950	2284	7116	33.5	6829	2901	8659	33.4	6875	2449	7336	35.0	7156	2573	7346	34.6	7181	3329	9624	33.5	6898	4093	12226	33.6	7103	4687	13938	37.0	5205	3307	8946	A	03	000	000	1

What am I doing wrong?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-01 : 17:35:09

from iclaimspivotold o
join ribinfo n on n.reg=o.reg
where o.rpt = '2' and ((o.sort = @Region or o.sort='1') or (o.rpt = '2' and o.region= @Region and o.sort = 'x' and o.doc='000'))

You need your OR statement to be inside it's own parenthesis

Poor planning on your part does not constitute an emergency on my part.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-02-01 : 20:06:08

Wow thanks for the extra set of eyes I was stuck on that all day!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources