In the below code i want if Inactive date is not Null and 1753-01-01 then its value should get stored in variable
DECLARE Cursor_Employee CURSOR FOR
SELECT A.EmployeeId,A.EmploymentDate,A.FixedDate,A.InActiveDate,
B.[ Type] , B.[ Duration] , B.BaseAmount , Isnull(B.Rate_1,0) , Isnull(B.Rate_2,0)
from [Test] as A
inner join [Test1] as B on A.EmployeeID = B.EmployeeID
OPEN cursor_Employee
Fetch Next From cursor_Employee Into @EmployeeID , @EmploymentDate , @FixedDate , @InActiveDate , @Type ,
@Duration , @BaseAmount , @Rate1 , @Rate2
While @@Fetch_Status = 0 Begin
Set @CAmount = 0
If @Type = 'S'
Begin
Set @CAmount = (@BaseAmount * (IsNull(@Rate1,0)/100))
End
Update [Test1] set Amount = @CAmount where EmployeeID = @EmployeeID
Fetch Next From cursor_Employee Into @EmployeeID , @EmploymentDate , @FixedDate , @InActiveDate , @Type , @Duration , @BaseAmount , @Rate1 , @Rate2
End
CLOSE cursor_Employee
DEALLOCATE cursor_Employee
END
In the below code i want if Inactive date is not Null and 1753-01-01 then its value should get stored in variable
DECLARE Cursor_Employee CURSOR FOR
SELECT A.EmployeeId,A.EmploymentDate,A.FixedDate,A.InActiveDate,
B.[ Type] , B.[ Duration] , B.BaseAmount , Isnull(B.Rate_1,0) , Isnull(B.Rate_2,0)
from [Test] as A
inner join [Test1] as B on A.EmployeeID = B.EmployeeID
OPEN cursor_Employee
Fetch Next From cursor_Employee Into @EmployeeID , @EmploymentDate , @FixedDate , @InActiveDate , @Type ,
@Duration , @BaseAmount , @Rate1 , @Rate2
While @@Fetch_Status = 0 Begin
Set @CAmount = 0
If @Type = 'S'
Begin
Set @CAmount = (@BaseAmount * (IsNull(@Rate1,0)/100))
End
Update [Test1] set Amount = @CAmount where EmployeeID = @EmployeeID
Fetch Next From cursor_Employee Into @EmployeeID , @EmploymentDate , @FixedDate , @InActiveDate , @Type , @Duration , @BaseAmount , @Rate1 , @Rate2
End
CLOSE cursor_Employee
DEALLOCATE cursor_Employee
END
Thanks
The requirement is not clear. I assume that you want to check if @InActiveDate is NULL or 1753-01-01. That's a basic IF or CASE statement.
IF @InActiveDate IS NOT NULL AND @InActiveDate <> '1753-1-1'
The problem is the logical design is not sound. @InActiveDate must have some value NULL, 1753-01-01, or otherwise as that's how you wrote the code. @InActiveDate is not used. What relevance does @InActiveDate have in the overall design?
The 1753-1-1 date is usually from C# code where the value is set the value to zero or
DateTime.MinValue before invoking an SQL parameter query.
It seems as though you have over arching design issues that should be addressed and resolve before moving forward.
Do you mean you don't want to loop on those values ? Then you could just include that criteria in the where clause for the SQL statement (an,d as pointed already you should fix using an empty string to populate a date which gives the lowest possible date
value).
Also it seems a cursor is not needed. You can have a FROM clause in an update statement which allows to update a table from another table or from any SELECT query that could provide the new value(s) - such as GROUP BY query if you want to get a SUM. etc...
For now it seems you want something such as (untested) :
UPDATE Test1 SET Amount=CASE WHEN type='S' THEN BaseAmount*ISNULL(rate_1,0)/100 ELSE 0 END
FROM Test1 JOIN Test ON Test.EmployeeId=Test1.Employee1
WHERE InActiveDate IS NOT NULL -- AND InactiveDate<>'17530101' but the idea would be to not let this kind of incorrect data go into your db, I assume it should be null instead
Member
504 Points
1776 Posts
Null Value or 1753-01-01
Nov 02, 2018 05:04 PM|JagjitSingh|LINK
Hi
In the below code i want if Inactive date is not Null and 1753-01-01 then its value should get stored in variable
Thanks
All-Star
53081 Points
23648 Posts
Re: Null Value or 1753-01-01
Nov 02, 2018 05:50 PM|mgebhard|LINK
The requirement is not clear. I assume that you want to check if @InActiveDate is NULL or 1753-01-01. That's a basic IF or CASE statement.
The problem is the logical design is not sound. @InActiveDate must have some value NULL, 1753-01-01, or otherwise as that's how you wrote the code. @InActiveDate is not used. What relevance does @InActiveDate have in the overall design?
The 1753-1-1 date is usually from C# code where the value is set the value to zero or DateTime.MinValue before invoking an SQL parameter query.
It seems as though you have over arching design issues that should be addressed and resolve before moving forward.
All-Star
48570 Points
18079 Posts
Re: Null Value or 1753-01-01
Nov 02, 2018 08:06 PM|PatriceSc|LINK
Hi,
Do you mean you don't want to loop on those values ? Then you could just include that criteria in the where clause for the SQL statement (an,d as pointed already you should fix using an empty string to populate a date which gives the lowest possible date value).
Also it seems a cursor is not needed. You can have a FROM clause in an update statement which allows to update a table from another table or from any SELECT query that could provide the new value(s) - such as GROUP BY query if you want to get a SUM. etc... For now it seems you want something such as (untested) :
UPDATE Test1 SET Amount=CASE WHEN type='S' THEN BaseAmount*ISNULL(rate_1,0)/100 ELSE 0 END
FROM Test1 JOIN Test ON Test.EmployeeId=Test1.Employee1
WHERE InActiveDate IS NOT NULL -- AND InactiveDate<>'17530101' but the idea would be to not let this kind of incorrect data go into your db, I assume it should be null instead
See https://docs.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-2017#OtherTables
All-Star
52683 Points
15720 Posts
Re: Null Value or 1753-01-01
Nov 02, 2018 08:44 PM|oned_gk|LINK
case when not(inactive is null or inactive='1753-01-01') then thevalue end
Suwandi - Non Graduate Programmer