Null Value or 1753-01-01

Reply

JagjitSingh

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

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

Reply

mgebhard

All-Star

53081 Points

23648 Posts

Re: Null Value or 1753-01-01

Nov 02, 2018 05:50 PM|mgebhard|LINK

JagjitSingh

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

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.

Reply

PatriceSc

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