SQL Code Needed to fix duplicates

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

SQL Server 2012 Forums

Transact-SQL (2012)

SQL Code Needed to fix duplicates

Author

Topic

Kapital123
Starting Member

31 Posts

Posted - 2015-04-28 : 03:30:18

Hi All,

After running the code below:

Select distinct
F_ISIN,
CPARTY_SHORT_NAME,
CPARTY_LONG_NAME,
FIRST_COUPON_FROM,
FIRST_COUPON_PAYMENT from Port_ABC
where F_ISIN = 'AU0000KFWHE0'

I get the following Output (excuse me but I had to populate the each column vertically down the page as this forum puts my formatting out of whack... visualize it as 5 columns across):

F_ISIN
AU0000KFWHE0
AU0000KFWHE0
AU0000KFWHE0

CPARTY_SHORT_NAME
KFW US
2534Z GR
2534Z GR

CPARTY_LONG_NAME
Kfw International Inc
Kreditanstalt Feur
Kreditanstalt Feur

FIRST_COUPON_FROM
NULL
2005-05-13
NULL

FIRST_COUPON_PAYMENT
NULL
2005-05-13
NULL

As you can see from running the code above that in PORT_ABC for this distinct F_ISIN that the four fields (ex F_ISIN) above have different values. To be absolutely certain that we can fix this error, we may need a separate code for fixing CPARTY_SHORT_NAME + CPARTY_LONG_NAME and FIRST_COUPON_FROM + FIRST_COURPON_PAYMENT. The logic for the former would require to look up the max(FROM_DATE) (Yes my database includes a date parameter called FROM_DATE) and populate all historical values with these values. For the latter, each ISIN should only ever have one unique FIRST_COUPON_FROM and FIRST_COUPON_PAYMENT and all NULL values for this distinct F_ISIN should be populated with these values. So the end product if I were to rerun the code above should be only one row of data.

I hope this is clear. I appreciate any assistance.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2015-04-28 : 12:15:24

It is unclear. Could you post sample data and tell us what returned data you expect from the query ?

Kapital123
Starting Member

31 Posts

Posted - 2015-04-28 : 18:19:41

It should look as follows (again I have written the column outputs down the page as this site doesn't look like it accommodates a table format very well):

F_ISIN
AU0000KFWHE0

CPARTY_SHORT_NAME
2534Z GR

CPARTY_LONG_NAME
Kreditanstalt Feur

FIRST_COUPON_FROM
2005-05-13

FIRST_COUPON_PAYMENT
2005-05-13

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources