SQLTeam.com | Weblogs | Forums

Removing duplicate date based on ID


#1

Hi Everyone,

I am fetching an extra unwanted record that have matching date/time based on customer id and status_code..
Any idea on how I could retrieve a single record based on customer_code and avoid the duplicate

SELECT PB.CUSTOMER_CODE , APS.STATUS_CODE, APS.CUSTOMER_TYPE, PTA.ADMIT_DATE

FROM --CLAUSE

WHERE
(OD.DATASET_NAME = 'b') AND (OD.DATASET_ID = '3')
AND (PTA.ADMIT_DATE BETWEEN CONVERT(DATETIME, '2015-07-01', 120) AND CONVERT(DATETIME, '20150731 23:59:59'))
AND (PTS.STATUS_DATE BETWEEN CONVERT(DATETIME, '2015-07-01', 120) AND CONVERT(DATETIME, '20150731 23:59:59'))
AND (APS.TYPE IN ('A', 'X'))
AND (APS.CUSTOMER_TYPE IN ('A'))
AND (PTS.STATUS_CODE IN ('A40','A41','A42','A43'))

results --

30832 A42 A 2015-07-10 00:00:00.000
26393 A41 A 2015-07-13 00:00:00.000
24245 A41 A 2015-07-14 00:00:00.000
30863 A40 A 2015-07-17 00:00:00.000
29620 A40 A 2015-07-14 00:00:00.000
29620 A40 A 2015-07-20 00:00:00.000
24218 A41 A 2015-07-21 00:00:00.000
26393 A41 A 2015-07-13 00:00:00.000
19204 A41 A 2015-07-30 00:00:00.000

Thanks!
M


#2

select distinct ...


#3

I tried Distinct but that would exclude which have separate dates

30832 A42 A 2015-07-10 00:00:00.000
26393 A41 A 2015-07-13 00:00:00.000
24245 A41 A 2015-07-14 00:00:00.000
30863 A40 A 2015-07-17 00:00:00.000
29620 A40 A 2015-07-14 00:00:00.000
29620 A40 A 2015-07-20 00:00:00.000
24218 A41 A 2015-07-21 00:00:00.000
26393 A41 A 2015-07-13 00:00:00.000
19204 A41 A 2015-07-30 00:00:00.000


#4

no it won't


#5

Your right :sweat_smile:


#6

I guess my question would be, why do you have such duplicates in a table to begin with? Is it an import staging table?