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