I have an historical price table with starting periods in integer format of calendar year, month and week, and endperiods in the same format. If the ending period is NULL, then that is the currently active list price. . .
I have two merge processes,
a merge to identify changed prices against the endperiod is null field, and then to update that field with the end period file.
Then a second merge to insert the new record when there is no matching null field. . .
The first works, the second doesn't work as I expect it should. . it tries to insert a field when there is a null field. . .
comments and suggestions welcome! :)
First Merge to End period an old price:
DECLARE @ENDINGWEEK int
SET @ENDINGWEEK = 20140703 --(SELECT rw.PeriodWeek FROM dbo.REPORT_WEEK rw WHERE rw.REPORTWEEK_ID = 1)
MERGE dbo.LABOR_STD_RATES as TARGET
USING (SELECT tmp.Price_List_Name, pl.Price_List_Location, tmp.Item_Name, tmp.Price
FROM dbo.tmp_PRICE_LIST tmp
INNER JOIN dbo.LABOR_PIDS lp ON tmp.Item_Name = lp.Item_Nbr
INNER JOIN dbo.PRICE_LISTS pl ON tmp.Price_List_Name = pl.Price_List_Name
WHERE (tmp.Price_List_Name Like '% ELE PRICE LIST'
OR tmp.Price_List_Name Like '% SPK PRICE LIST'
OR tmp.Price_List_Name Like '% SUP PRICE LIST'
OR tmp.Price_List_Name Like '% TIME SOLN PRICE LIST')
AND pl.Price_List_Location IS Not NULL
AND pl.Active = 'Y'
AND (tmp.Price_List_Name like 'US DIST 202 %') --OR tmp.Price_List_Name like 'US DIST 434 %')
) AS SOURCE
ON (TARGET.Price_List_Name = SOURCE.Price_List_Name AND TARGET.Labor_PID = SOURCE.Item_Name)
WHEN MATCHED AND target.End_PeriodWeek IS NULL AND TARGET.Hourly_Rate <> SOURCE.Price
THEN UPDATE SET TARGET.End_PeriodWeek = @ENDINGWEEK, TARGET.Validated = GETDATE()
;
GO
Now after I end period a particular price, there is no record in the table with a End_PerioWeek value IS NULL. . . so I merge on that and when not matched, then insert the record. . .
DECLARE @FOLLOWINGWEEK int
SET @FOLLOWINGWEEK = 20140704 --(SELECT rw.PeriodWeek_Following FROM dbo.REPORT_WEEK rw WHERE rw.REPORTWEEK_ID = 1)
MERGE dbo.LABOR_STD_RATES as TARGET
USING (SELECT tmp.Price_List_Name, pl.Price_List_Location, tmp.Item_Name, tmp.Price, Null As "EndPeriodWeek"
FROM dbo.tmp_PRICE_LIST tmp
INNER JOIN dbo.LABOR_PIDS lp ON tmp.Item_Name = lp.Item_Nbr
INNER JOIN dbo.PRICE_LISTS pl ON tmp.Price_List_Name = pl.Price_List_Name
WHERE (tmp.Price_List_Name Like '% ELE PRICE LIST'
OR tmp.Price_List_Name Like '% SPK PRICE LIST'
OR tmp.Price_List_Name Like '% SUP PRICE LIST'
OR tmp.Price_List_Name Like '% TIME SOLN PRICE LIST')
AND tmp.Price_List_Name NOT LIKE 'GSA%'
AND pl.Active = 'Y'
AND (tmp.Price_List_Name like 'US DIST 202 %')-- OR tmp.Price_List_Name like 'US DIST 421 %')
) AS SOURCE
ON (TARGET.Price_List_Name = SOURCE.Price_List_Name AND TARGET.Labor_PID = SOURCE.Item_Name AND SOURCE.EndPeriodWeek = Target.End_PeriodWeek)
WHEN NOT MATCHED BY TARGET
THEN INSERT (Start_PeriodWeek
, Price_List_Name
, strDistrict
, Labor_PID
, Hourly_Rate
, Validated)
VALUES (@FOLLOWINGWEEK
, Source.Price_List_Name
, Source.Price_List_Location
, Source.Item_Name
, Source.Price
, GETDATE()
)
;
GO
So i assume that the null match isn't working properly, workarounds?
thanks in advance,
sportsguy
MS Access 20 years, SQL hack
Merge statement not working with a null field
Posted - 2014-07-28 : 14:44:10
