December 30, 2015 at 6:18 am
In example below I want to increase all salaries in *PayRoll* by the percentage found in *Increase*, starting from Increase Date in *Increase* and going forward.
This logic works fine if you have a single increase, but in my example of the 3 increases it will obviously only apply the last one instead of compounding (i.e. will not apply 1st increase, then apply 2nd increase on top of 1st and 3rd on top of 2nd). I understand exactly why set-based command will not work but want to know best solution to problem (currently can only provide RBAR solution with cursor or loop). I look forward to your suggestions...
IF OBJECT_ID('PayRoll') IS NOT NULL
DROP TABLE PayRoll
GO
CREATE TABLE [dbo].[PayRoll]
(
[PayRollID] [INT] IDENTITY(1, 1) NOT NULL,
[EmployeeNo] [INT] NOT NULL,
[EmployeeName] [VARCHAR](8) NOT NULL,
[Month] [DATE] NOT NULL,
[Salary] [MONEY] NOT NULL,
CONSTRAINT [PK_PayRoll] PRIMARY KEY CLUSTERED ( [PayRollID] ASC )
)
GO
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-01-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-02-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-03-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-04-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-05-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-06-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-07-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-08-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-09-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-10-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-11-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (123, N'John Doe', CAST(N'2016-12-01' AS Date), 5000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-01-01' AS Date), 6000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-02-01' AS Date), 6000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-03-01' AS Date), 6000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-04-01' AS Date), 6000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-05-01' AS Date), 6000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-06-01' AS Date), 6000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-07-01' AS Date), 6000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-08-01' AS Date), 6000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-09-01' AS Date), 6000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-10-01' AS Date), 6000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-11-01' AS Date), 6000)
INSERT [dbo].[PayRoll] ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES (456, N'Jane Doe', CAST(N'2016-12-01' AS Date), 6000)
GO
IF OBJECT_ID('Increase') IS NOT NULL
DROP TABLE Increase
GO
CREATE TABLE [dbo].[Increase]
(
[IncreaseID] [INT] IDENTITY(1, 1) NOT NULL,
[IncreaseDate] [DATE] NOT NULL,
[IncreasePercent] [MONEY] NOT NULL,
CONSTRAINT [PK_Increase] PRIMARY KEY CLUSTERED ( [IncreaseID] ASC )
)
GO
INSERT [dbo].[Increase] ([IncreaseDate], [IncreasePercent]) VALUES (CAST(N'2016-04-01' AS Date), 5.0000)
INSERT [dbo].[Increase] ([IncreaseDate], [IncreasePercent]) VALUES (CAST(N'2016-09-01' AS Date), 10.0000)
INSERT [dbo].[Increase] ([IncreaseDate], [IncreasePercent]) VALUES (CAST(N'2016-11-01' AS Date), 7.0000)
GO
SELECT 'Before Update' AS [Description],
*
FROM [dbo].[PayRoll]
ORDER BY [EmployeeNo],
[Month]
UPDATE p
SET p.[Salary] = p.[Salary] * ( 1 + ( i.IncreasePercent / 100 ) )
FROM [dbo].[PayRoll] p
INNER JOIN [dbo].[Increase] i
ON i.IncreaseDate <= p.[Month]
SELECT 'Increases Expected' AS [Description],
*
FROM [dbo].[Increase]
ORDER BY [IncreaseDate]
SELECT 'After Update' AS [Description],
*
FROM [dbo].[PayRoll]
ORDER BY [EmployeeNo],
[Month]
December 30, 2015 at 11:49 am
You can use the Quirky Update method discussed here: http://qa.sqlservercentral.com/articles/T-SQL/68467/[/url]
-- Itzik Ben-Gan 2001
December 30, 2015 at 4:48 pm
Thank you so much Alan. A fascinating concept. I might be overly tired but just cannot figure out how to apply the concept to my situation. But will continue figuring it out over the next few days.
Appreciate your time and input!
December 31, 2015 at 1:45 pm
The QuirkyUpdate method is the fastest by far. But if you want to try something that is perhaps better documented and with more easily google-able tutorials available online, you can do this using SQL Server's built-in windowing functions, such as OVER(), and LEAD/LAG, etc.
http://sqlwithmanoj.com/tag/unbounded-preceding/
You can specify how many rows to look back, etc. Hope this helps.
December 31, 2015 at 5:30 pm
robert-819720 (12/30/2015)
Thank you so much Alan. A fascinating concept. I might be overly tired but just cannot figure out how to apply the concept to my situation. But will continue figuring it out over the next few days.Appreciate your time and input!
I wanted to come back and put together a couple examples of how to solve and I realized that this is a trickier problem then I first thought.
First, I am going to assume that your update is producing the wrong results. They way I am interpreting this problem is: as of 2016-01-01 EmployeeNo 123 has a salary of $5,000. He get's a 5% raise in April his salary becomes $5250. He gets a 10% raise in September his salary should go from $5250 to $5775. Then a 7% raise in November.... his salary should go from $5775 to $6179.25.... Your UPDATE query does not produce these results but I think that's what you are looking for.
I'm going to see if someone else can come up with something better but I put together a solution that works and is pretty fast. (even if I'm not understanding this correctly, the solution will be a good example of how to use the Quirky Update). My solution is broken up into 4 parts:
1. Recreate your sample data using temp tables. I'm on a server where I can't create perm tables at the moment, sorry. The benefit is that anyone can copy/paste this code and execute it as is...
2. Create some indexes that will make my solution run without any sorts in the query plan AND makes sure that the Quirky Update produces the correct results
3. Creates a staging table that we'll run the quirky update against
4. Uses a set-based loop to perform the Quirky Update for each employee
(note: Set based loops are not a bad thing)
Here goes (note my comments):
USE tempdb
GO
/****************************************************************************************
STEP #1: Create the sample data
****************************************************************************************/
IF OBJECT_ID('tempdb..#PayRoll') IS NOT NULL DROP TABLE #PayRoll;
IF OBJECT_ID('tempdb..#Increase') IS NOT NULL DROP TABLE #Increase;
CREATE TABLE tempdb..#PayRoll
(
[PayRollID] [INT] IDENTITY(1, 1) NOT NULL,
[EmployeeNo] [INT] NOT NULL,
[EmployeeName] [VARCHAR](8) NOT NULL,
[Month] [DATE] NOT NULL,
[Salary] [MONEY] NOT NULL,
-- Let's make this nonclustered, I've got a better clustered index in mind:
CONSTRAINT [PK_PayRoll] PRIMARY KEY NONCLUSTERED (PayRollID ASC)
);
CREATE TABLE #Increase
(
[IncreaseID] [INT] IDENTITY(1, 1) NOT NULL,
[IncreaseDate] [DATE] NOT NULL,
[IncreasePercent] [MONEY] NOT NULL,
CONSTRAINT [PK_Increase] PRIMARY KEY CLUSTERED ( [IncreaseID] ASC )
);
GO
INSERT #PayRoll ([EmployeeNo], [EmployeeName], [Month], [Salary]) VALUES
(123, N'John Doe', CAST(N'2016-01-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-02-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-03-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-04-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-05-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-06-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-07-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-08-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-09-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-10-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-11-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-12-01' AS Date), 5000),
(456, N'Jane Doe', CAST(N'2016-01-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-02-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-03-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-04-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-05-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-06-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-07-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-08-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-09-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-10-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-11-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-12-01' AS Date), 6000);
INSERT #Increase ([IncreaseDate], [IncreasePercent]) VALUES
(CAST(N'2016-04-01' AS Date), 5.0000),
(CAST(N'2016-09-01' AS Date), 10.0000),
(CAST(N'2016-11-01' AS Date), 7.0000);
GO
/****************************************************************************************
STEP #2: Build indexes to avoid a sort
****************************************************************************************/;
-- to prevent a sort operator in the index plan for the query below
CREATE INDEX nc_payroll_empno ON #PayRoll(EmployeeNo);
-- will be used by the second insert query
CREATE UNIQUE INDEX uq_increase_dp ON #increase(IncreaseDate) INCLUDE (IncreasePercent);
CREATE UNIQUE CLUSTERED INDEX uq_payroll_empmo ON #payroll(EmployeeNo, [Month]);
/****************************************************************************************
STEP #3: 2 Use Quirky Update to put the correct Salary Calculations in a new table
****************************************************************************************/;
IF OBJECT_ID('tempdb..#PayRollNew') IS NOT NULL DROP TABLE #PayRollNew;
IF OBJECT_ID('tempdb..#emps') IS NOT NULL DROP TABLE #emps;
-- Get All Employees
-- Note: the nc_payroll_empno index prevents a sort here
SELECT rn = ROW_NUMBER() OVER (ORDER BY EmployeeNo), EmployeeNo
INTO #emps
FROM
(
SELECT DISTINCT EmployeeNo
FROM #PayRoll
) E;
-- Note: uq_payroll_empno gets you an *unsorted* Clustered index scan
-- uq_increase_dp gets you a nonclustered index seek (also not sorted)
SELECT
PayRollID = ISNULL(PayRollID,0), -- ISNULL to make set columns as not null
EmployeeNo = ISNULL(EmployeeNo,0),
EmployeeName = ISNULL(EmployeeName,''),
[Month] = ISNULL([Month],getdate()),
Salary = ISNULL(Salary,0),
EmpMoNo = ISNULL(ROW_NUMBER() OVER(PARTITION BY EmployeeNo ORDER BY [Month]),0),
IncreasePercent = ISNULL(IncreasePercent,0),
IncreaseFlag = IIF([MONTH]=IncreaseDate,1,0)
INTO #PayRollNew
FROM #PayRoll p
OUTER APPLY
(
SELECT TOP (1) IncreaseDate, IncreasePercent
FROM #Increase i
WHERE i.IncreaseDate <= p.[Month]
ORDER BY IncreaseDate DESC
) i;
-- !!! THIS CLUSTERED INDEX IS REQUIRED FOR THE QUIRKY UPDATE TO WORK CORRECTLY !!!
ALTER TABLE #PayRollNew ADD CONSTRAINT pk_X PRIMARY KEY (EmployeeNo, [Month]);
/****************************************************************************************
STEP #4: A set based loop that does the quirky update for each employee
****************************************************************************************/;
-- What we have before
SELECT Note = 'Old Table', *
FROM #PayRoll;
-- PERFORM THE QUIRKY UPDATE FOR EACH EMPLOYEE
DECLARE @i int = 1,
@salary money;
WHILE @i <= (SELECT COUNT(*) FROM #emps)
BEGIN
UPDATE #PayRollNew
SET @salary = Salary = ISNULL(@salary,Salary)+IIF(IncreaseFlag=0,0,@salary*(IncreasePercent/100))
FROM #PayRollNew WITH (TABLOCKX) --!! RQUIRED FOR PERMINANT TABLE
WHERE EmployeeNo = (SELECT EmployeeNo FROM #emps WHERE rn = @i)
OPTION (MAXDOP 1);
SELECT @i += 1, @salary = NULL; -- go to next record, reset the @salry variable
END
GO
-- Get rid of old columns
ALTER TABLE #PayRollNew DROP COLUMN EmpMoNo;
ALTER TABLE #PayRollNew DROP COLUMN IncreasePercent;
ALTER TABLE #PayRollNew DROP COLUMN IncreaseFlag;
-- The new table
SELECT Note = 'New Table', *
FROM #PayRollNew;
-- Itzik Ben-Gan 2001
January 1, 2016 at 7:36 am
Fascinating and brilliant solution Alan. I really value your effort on this.
I do believe that there is one significant caveat with the Quirky Update (just love the name it has been given) - and that is that according to my understanding it does not calculate based on the new/latest row value. In other words, let us say that we had a scenario where John Doe happened to start with 10,000 for May only (and 5,000 all other months). In this case your code would result in 5,250 for May instead of expected 10,500 (10,000 + 5%).
From what I see in my specific scenario where this is a reality I would not be able to use the Quirky Update but would have no option but some sort of RBAR (e.g. Cursor) option.
Interested in hearing your comment on this, but thanks so much again.
January 1, 2016 at 7:43 am
On the suggestion of using SQL Server's built-in windowing functions, such as OVER(), and LEAD/LAG, whilst I am using this on a 2008R2 version, I am still very interested to see how this could possibly be applied in 2012 onward, and will do some research to see if I can figure out how to achieve this with what you suggest. Thanks so much for the pointers...
January 1, 2016 at 10:21 am
Since you posted in the SQL2014 forum I assumed you had the latest version. But 2008 also has windowing functions which you should look into and understand. Very powerful. As you can see, even in the proposed solution, the OVER() clause is being used.
Also, just skimming over the data again, I see that "Increase" is not tied to any particular EmployeeID. Is that correct? Seems odd.
Finally, I would also consider storing people's adjusted (new) salaries somewhere in a table. It seems quite an important piece of information that Employee #123's salary changed from X to Y on so and so date. Worth storing somewhere permanently, rather than solely calculated on the fly each time based on data from an arbitrary old date. I realize that doesn't address your specific question, but maybe this is a good opportunity to ask why rather than how. 🙂 Good luck!
January 1, 2016 at 3:08 pm
Thank you. Will check this further then.
In context what I provide here is just a simplified version to illustrate the problem at hand, but I intend using this for a budget/forecast system with provisional values rather than actuals spanning 24 months hence across the board adjustments for annual increases etc. rather than specific individual adjustments. As we are dealing with 24 months we might well have 2 annual increases applied.
The principle also applies to CAPEX as well as salaries (e.g a cost center might have specific months higher or lower than others). Hope this helps.
January 4, 2016 at 7:29 am
robert-819720 (1/1/2016)
Fascinating and brilliant solution Alan. I really value your effort on this.I do believe that there is one significant caveat with the Quirky Update (just love the name it has been given) - and that is that according to my understanding it does not calculate based on the new/latest row value. In other words, let us say that we had a scenario where John Doe happened to start with 10,000 for May only (and 5,000 all other months). In this case your code would result in 5,250 for May instead of expected 10,500 (10,000 + 5%).
From what I see in my specific scenario where this is a reality I would not be able to use the Quirky Update but would have no option but some sort of RBAR (e.g. Cursor) option.
Interested in hearing your comment on this, but thanks so much again.
bit late...but possibly another way (edit could do with some tidying up......and consideration for larger data sets that do not conform to the consecutive payrollid as provided in sample data)
USE tempdb
GO
IF OBJECT_ID('tempdb..#PayRoll') IS NOT NULL DROP TABLE #PayRoll;
IF OBJECT_ID('tempdb..#Increase') IS NOT NULL DROP TABLE #Increase;
CREATE TABLE #PayRoll
(
PayRollID INT IDENTITY(1, 1) NOT NULL,
EmployeeNo INT NOT NULL,
EmployeeName VARCHAR(8) NOT NULL,
Month DATE NOT NULL,
Salary MONEY NOT NULL,
);
CREATE TABLE #Increase
(
IncreaseID INT IDENTITY(1, 1) NOT NULL,
IncreaseDate DATE NOT NULL,
IncreasePercent MONEY NOT NULL,
);
INSERT #PayRoll (EmployeeNo, EmployeeName, Month, Salary) VALUES
(123, N'John Doe', CAST(N'2016-01-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-02-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-03-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-04-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-05-01' AS Date), 10000),
(123, N'John Doe', CAST(N'2016-06-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-07-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-08-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-09-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-10-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-11-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-12-01' AS Date), 5000),
(456, N'Jane Doe', CAST(N'2016-01-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-02-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-03-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-04-01' AS Date), 12000),
(456, N'Jane Doe', CAST(N'2016-05-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-06-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-07-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-08-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-09-01' AS Date), 500),
(456, N'Jane Doe', CAST(N'2016-10-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-11-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-12-01' AS Date), 6000);
INSERT #Increase (IncreaseDate, IncreasePercent) VALUES
(CAST(N'2016-04-01' AS Date), 5.0000),
(CAST(N'2016-09-01' AS Date), 10.0000),
(CAST(N'2016-11-01' AS Date), 7.0000);
GO
with cte as (
SELECT IncreaseID,
IncreaseDate,
IncreasePercent,
1 + IncreasePercent/100 as cumpc
FROM #Increase AS a
WHERE(IncreaseID = 1)
UNION ALL
SELECT b.IncreaseID,
b.IncreaseDate,
b.IncreasePercent,
cte.cumpc *(1 + b.IncreasePercent/100)
FROM #Increase AS b
inner join cte on b.IncreaseId = cte.IncreaseId +1
)
, cte2 as (
SELECT p.PayRollID,
p.EmployeeNo,
p.EmployeeName,
p.Month,
p.Salary,
cte.IncreaseDate,
cte.cumpc
FROM #PayRoll AS p
LEFT OUTER JOIN cte ON p.Month = cte.IncreaseDate
)
, cte3 as (
SELECT PayRollID
,EmployeeNo
,EmployeeName
,Month
,Salary
,IncreaseDate
,cumpc
,s=salary * ISNULL(CAST(cumpc AS MONEY), x)
FROM cte2 a
OUTER APPLY
(
SELECT TOP 1 x = cumpc
FROM cte2 b
WHERE b.cumpc IS NOT NULL
AND b.payrollid < a.payrollid
AND b.EmployeeNo = a.EmployeeNo
ORDER BY payrollid DESC
) b )
UPDATE cte3
SET salary = s
WHERE s IS NOT NULL
SELECT * FROM #PayRoll;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 5, 2016 at 9:37 pm
Wow, This last one blows my mind as well. Still trying to understand how the update of the CTE results in #PayRoll changing but from what I see so far this appears to work? Another one to spend some time examining...
Thank you all!
January 5, 2016 at 11:21 pm
J Livingston, how are you able to reference "cte" from within the definition of cte?
inner join cte on b.IncreaseId = cte.IncreaseId +1
January 5, 2016 at 11:31 pm
January 5, 2016 at 11:34 pm
Ah okay, yes, I should have figured out on my own that it was a recursive CTE. Thanks.
January 6, 2016 at 12:02 am
Here is another solution, i borrowed 1st part of the solution from J Livingston SQL
i hope he don't mind it. For the sake of keeping it simple and all following solution can do the work as well. i have avoided Update statement.
Following is the code:
USE tempdb
GO
IF OBJECT_ID('tempdb..#PayRoll') IS NOT NULL DROP TABLE #PayRoll;
IF OBJECT_ID('tempdb..#Increase') IS NOT NULL DROP TABLE #Increase;
CREATE TABLE #PayRoll
(
PayRollID INT IDENTITY(1, 1) NOT NULL,
EmployeeNo INT NOT NULL,
EmployeeName VARCHAR(8) NOT NULL,
Month DATE NOT NULL,
Salary MONEY NOT NULL,
);
CREATE TABLE #Increase
(
IncreaseID INT IDENTITY(1, 1) NOT NULL,
IncreaseDate DATE NOT NULL,
IncreasePercent MONEY NOT NULL,
);
INSERT #PayRoll (EmployeeNo, EmployeeName, Month, Salary) VALUES
(123, N'John Doe', CAST(N'2016-01-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-02-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-03-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-04-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-05-01' AS Date), 10000),
(123, N'John Doe', CAST(N'2016-06-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-07-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-08-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-09-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-10-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-11-01' AS Date), 5000),
(123, N'John Doe', CAST(N'2016-12-01' AS Date), 5000),
(456, N'Jane Doe', CAST(N'2016-01-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-02-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-03-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-04-01' AS Date), 12000),
(456, N'Jane Doe', CAST(N'2016-05-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-06-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-07-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-08-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-09-01' AS Date), 500),
(456, N'Jane Doe', CAST(N'2016-10-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-11-01' AS Date), 6000),
(456, N'Jane Doe', CAST(N'2016-12-01' AS Date), 6000);
INSERT #Increase (IncreaseDate, IncreasePercent) VALUES
(CAST(N'2016-04-01' AS Date), 5.0000),
(CAST(N'2016-09-01' AS Date), 10.0000),
(CAST(N'2016-11-01' AS Date), 7.0000);
GO
;
with cte as (
SELECT IncreaseID,
IncreaseDate,
IncreasePercent,
1 + IncreasePercent/100 as cumpc
FROM #Increase AS a
WHERE(IncreaseID = 1)
UNION ALL
SELECT b.IncreaseID,
b.IncreaseDate,
b.IncreasePercent,
cte.cumpc *(1 + b.IncreasePercent/100)
FROM #Increase AS b
inner join cte on b.IncreaseId = cte.IncreaseId +1
)
, cte2 as (
select IncreaseID
, IncreaseDate as startdate
, LEAD(IncreaseDate, 1, '3000-12-31') OVER ( Order by IncreaseDate) AS EndDate
, IncreasePercent
, cumpc
from cte
)
select *, salary = ISNULL(p.Salary * c.cumpc, p.salary)
from #PayRoll P
left join cte2 c on p.Month >= c.startdate and p.Month < c.EndDate
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply