locked
Null Value or 1753-01-01 RRS feed

  • Question

  • User-1499457942 posted

    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

    Friday, November 2, 2018 5:04 PM

Answers

  • User475983607 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 2, 2018 5:50 PM
  • User-1716253493 posted

    case when not(inactive is null or inactive='1753-01-01') then thevalue end

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 2, 2018 8:44 PM

All replies

  • User475983607 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 2, 2018 5:50 PM
  • User753101303 posted

    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

    Friday, November 2, 2018 8:06 PM
  • User-1716253493 posted

    case when not(inactive is null or inactive='1753-01-01') then thevalue end

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 2, 2018 8:44 PM