Merge Problem

[leiela]

Hi i need idea's of how to change the following. Im trying to Build a history table Which updates the current history data when a none critical field is changed, but inserts a new row when a critical field changes or a new service is added.

My problem is when a customer changes thier QTY .. it's fine it adds a new row, (QTY is Critical) however if later down the line they change the quantity back to it's origional value, the merge statement finds a match and doens't insert a new row to reflect it. Idealy the merge would only compare the service table to the last row in the history table. Is there any way to do this?


MERGE Service_history AS Target
USING
(
Select [load_date] = getDate()
,[update_date] = getDate()
,[ServiceId] = sd.[ServiceId]
,[ServiceKey] = sd.[ServiceKey]
,[EquipmentKey] = sd.[EquipmentKey]
,[ServiceType] = sd.[ServiceType]
,[ActivityId] = sd.[ActivityId]
,[Quantity] = sd.[Quantity]
,[DeliveryDate] = sd.[DeliveryDate]
,[PickupDate] = sd.[PickupDate]
,[CustomerKey] = sd.[CustomerKey]


FROM [service] sd
Left Join Customer] C
On SD.CustomerKey = C.CustomerKey
AND SD.DepotID = C.DepotID

) AS Source

ON (


ON Target.ServiceKey = Source.ServiceKey
AND Target.[Quantity] = Source.[Quantity]
AND Target.[DeliveryDate] = source.[DeliveryDate]
AND Target.[PickupDate] = source.[PickupDate]



)

WHEN MATCHED THEN
UPDATE
Set
Target.[update_date] = Source.[update_date]
,Target.[ServiceKey] = Source.[ServiceKey]
,Target.[DeliveryDate] = Source.[DeliveryDate]
,Target.[PickupDate] = Source.[PickupDate]
,Target.[ServiceId] = sd.[ServiceId]
,Target.[EquipmentKey] = sd.[EquipmentKey]
,Target.[ServiceType] = sd.[ServiceType]
,Target.[ActivityId] = sd.[ActivityId]
,Target.[CustomerKey] = sd.[CustomerKey]




WHEN NOT MATCHED BY TARGET THEN
INSERT (

[load_date]
,[update_date]
,[ServiceId] = sd.[ServiceId]
,[ServiceKey] = sd.[ServiceKey]
,[EquipmentKey] = sd.[EquipmentKey]
,[ServiceType] = sd.[ServiceType]
,[ActivityId] = sd.[ActivityId]
,[Quantity] = sd.[Quantity]
,[DeliveryDate] = sd.[DeliveryDate]
,[PickupDate] = sd.[PickupDate]
,[CustomerKey] = sd.[CustomerKey]


)

VALUES (

[load_date]
,[update_date]
,[ServiceId] = sd.[ServiceId]
,[ServiceKey] = sd.[ServiceKey]
,[EquipmentKey] = sd.[EquipmentKey]
,[ServiceType] = sd.[ServiceType]
,[ActivityId] = sd.[ActivityId]
,[Quantity] = sd.[Quantity]
,[DeliveryDate] = sd.[DeliveryDate]
,[PickupDate] = sd.[PickupDate]
,[CustomerKey] = sd.[CustomerKey]
)