SQLTeam.com | Weblogs | Forums

SQL select latest records


#1

Help. I am going around in circles.
I have a table which is used globally in our organisation and I need to extract the latest record for each

I have created a job to extract the data

USE ACTAMART
GO
DROP TABLE DBO.LATEST_SMO_PRICING

GO
USE ACTAMART
GO

SELECT DISTINCT SMO_PRICING.* INTO DBO.LATEST_SMO_PRICING

FROM
(
SELECT
FEE_CODE,
MAX(VALID_FROM) AS maxobject
FROM
ACTA.SMO_PRICING
GROUP BY
SALES_ORG,FEE_CODE
)
t1
INNER JOIN
ACTA.SMO_PRICING
ON t1.FEE_CODE = SMO_PRICING.FEE_CODE
AND t1.maxobject = SMO_PRICING.VALID_FROM

  WHERE SMO_PRICING.VARIANT_CONDITION IN ('daily_rate','half_daily_rate')

  GO 
  use ACTAMART 
  GO

However when I view the table that is created I am getting duplicate entries.

These FEE_CODES get updated on an annual basis.

Any help would be appreciated