Hi mahesh213,
Check this sample query.
For 2nd condition you need to save the details for the Customer whose Salary is not deducted. Then you need to check every month and set the month count and by checking the count update the salary after 3 month.
CREATE TABLE #Customers(Id INT,Name VARCHAR(10),Salary INT,MonthCount INT)
INSERT INTO #Customers VALUES(1,'aaa',10000,0)
INSERT INTO #Customers VALUES(2,'bbb',8000,0)
INSERT INTO #Customers VALUES(3,'ccc',0,0)
INSERT INTO #Customers VALUES(4,'ddd',25000,0)
DECLARE @Counter INT, @TotalCount INT, @Salary INT, @Id INT, @Name VARCHAR(10),@MonthCount INT
SET @Counter = 1
SET @TotalCount = (SELECT COUNT(*) FROM #Customers)
WHILE (@Counter <= @TotalCount)
BEGIN
IF (GETDATE() = CAST(CAST(YEAR(GETDATE()) AS VARCHAR(4))+'/'+CAST(MONTH(GETDATE()) AS VARCHAR(2))+'/05' AS DATETIME))
BEGIN
SET @Id = (SELECT Id FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM #Customers) t WHERE t.RowId = @Counter)
SET @Name = (SELECT Name FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM #Customers) t WHERE t.RowId = @Counter)
SET @Salary = (SELECT Salary FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM #Customers) t WHERE t.RowId = @Counter)
SET @MonthCount = (SELECT MonthCount FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM #Customers) t WHERE t.RowId = @Counter)
IF @Salary > 0 AND @Salary <= 10000
BEGIN
UPDATE #Customers
SET Salary = (@Salary - @Salary*5/100),
MonthCount = 0
WHERE Id = @Id
END
ELSE IF @Salary > 10000
BEGIN
UPDATE #Customers
SET Salary = (@Salary - @Salary*8/100),
MonthCount = 0
WHERE Id = @Id
END
ELSE IF @Salary = 0
BEGIN
IF (@MonthCount = 3)
BEGIN
UPDATE #Customers
SET Salary = @Salary - ((@Salary*5/100)*3),
MonthCount = 0
WHERE Id = @Id
END
ELSE
BEGIN
UPDATE #Customers
SET MonthCount = @MonthCount + 1
WHERE Id = @Id
END
END
SET @Counter = @Counter + 1
CONTINUE;
END
END
SELECT * FROM #Customers
DROP TABLE #Customers
