Here is the problem I am trying to solve:
I am updating a table to load historical costs for projects. One of the fields is employee hourly rate. This rate can change over time. For example, the rate can be 50 for January through June, then change to 60 July through December. I need to be able to load the rate of 50 for all time entries from Jan-Jun then load 60 for Jul-Dec for the same employee. I can't quite get it to work. I have been using date ranges that are hard coded but I need to do this without hard coding dates in the query. This is my current query that works but is not sustainable:
BEGIN TRANSACTION
Declare @rateMonth nvarchar(10) = '12019' --If loading a date range, this variable must be update accordingly
INSERT INTO RequiredTable (UserID, UserName, TotalEntered, Rate, ProjectID, ProjectName, StartDate, EndDate)
SELECT a.UserID, a.UserName, a.TotalEntered, b.Rate, a.ProjectID, a.ProjectName, a.StartDate, a.EndDate
FROM Table1 a
JOIN Table2 b ON a.UserID = b.Emp_id
JOIN TableRawData c ON a.UserID = c.UserID
WHERE startdate BETWEEN '1/1/2019' AND '1/31/2019' --change date range accordingly
AND @rateMonth = CONCAT(RTRIM(b.cal_Month),RTRIM(b.cal_Year))
--Verify the update
SELECT * FROM RequiredTable
ORDER BY UserID
COMMIT TRANSACTION --COMMIT TRANSACTION if it looks good otherwise ROLLBACK TRANSACTION