Duplicates

Question

Duplicates

cdl_9009

SSC Enthusiast

Points: 173
More actions
August 27, 2013 at 6:29 am

#281722

When you have a temp table being populated. Can you join on that table so you don't get duplicates? I tried using distinct but even if the time stamp is off by just a little, it will add it because it is distinct.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Can you post the query you are using?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

HanShi SSC-Dedicated Points: 33507 More actions · Answer 2

Please, can you clarify what you are trying to accomplish? Where is the data to fill the temp-table coming from? How (on what columns) do you want to join the table with existing tables?

To get distinct values you can use a GROUP BY and use a MIN or MAX on the time column, to get a single result. Or you can convert the time column to a more granular date/time format.

SELECT {column1, ...}, MIN([time]) as 'time'

FROM #table

GROUP BY {column1, ...}

or

SELECT {column1, ...}, CONVERT(char(17), [time], 120) as 'time'

FROM #table

GROUP BY {column1, ...}, CONVERT(char(17), [time], 120)

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

cdl_9009 SSC Enthusiast Points: 173 More actions · Answer 3

INSERT INTO #PD_IN_ADD ( ADD_UID,

PLC_CODE,

PD_ID,

PDA_EFF_DT,

PDA_IN_SO,

CHG_BY,

CHG_DT,

PDNETA_SERV_TERM )

SELECT DISTINCT IPN.IPN_ADD_UID, --CHANGED THIS TO SELECT DISTINCT

IPD.IPD_PLC_CODE,

IPD.IPD_PD_ID,

IPD.IPD_CHG_DT,

IPN.IPN_SO_ID,

'AI IMPORT',

IPN.IPN_CHG_DT,

'N'

FROM INBOUND_PLANT_DETAIL IPD

JOIN INBOUND_PLANT_NETWORK IPN ON

IPD.IPN_TRANS = IPN.IPN_TRANS

WHERE IPD.IPD_PD_STATUS = 'PEND'

AND IPN.IPN_TRANS_STATUS = 'PEND'

AND IPD.IPD_PD_ID NOT IN ( SELECT PDA.PD_ID

FROM PD_ADD PDA

WHERE IPN.IPN_ADD_UID = PDA.ADD_UID

AND IPD.IPD_PD_ID = PDA.PD_ID )

This is an example of the results I get.

add_uid plc_code pd_id chg_dt so_id AI Import chg_dt N

6610 FCO 311075 46:23.2 24433 " " 46:21.3 ""

6610 FCO 311075 46:23.2 24536 " " 46:21.3 ""

PK = add_uid, plc_code, pd_id

HanShi SSC-Dedicated Points: 33507 More actions · Answer 4

According to your business rules: what value does IPN.IPN_CHG_DT need to be in the temp table?

The values are taken from the SELECT statement (and not generated on-the-spot), so the actual values are allready different in the original tables.

How are these values used from within the temp table? What are you using the temp table for?

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

cdl_9009 SSC Enthusiast Points: 173 More actions · Answer 5

This is addresses. I can only insert on on the addresses, when it gets to the next level, i will insert both of them. One is tied to a telephone, other is tied to an Internet.

Don't know if I am explaining very good.

cdl_9009 SSC Enthusiast Points: 173 More actions · Answer 6

I think I got it.

I put MIN(IPD_CHG_DT),

MIN(SO_ID)

MIN(IPN_CHG_DT)

We will find out if issues come up if I shouldn't of done that.