metropia
asked on
calculating dates
I need to calculate a filed named: ContractOpenBeginDate
The concetp of the field is as follows:
ContractOpenBeginDate = to Today's date if in the middle of contract (start & end dates) OR if contract has not started yet OR NULL if Contract is closed (contract end date is in the past)
The fields I will use are: Today (GETDATE()), ContractStartDate, ContractEndDate
I know this is a CASE statement, but I need help in writing the code if possible because I have not used these type of combinations before.
Any help would be great.
Thank you very much fro your help.
The concetp of the field is as follows:
ContractOpenBeginDate = to Today's date if in the middle of contract (start & end dates) OR if contract has not started yet OR NULL if Contract is closed (contract end date is in the past)
The fields I will use are: Today (GETDATE()), ContractStartDate, ContractEndDate
I know this is a CASE statement, but I need help in writing the code if possible because I have not used these type of combinations before.
Any help would be great.
Thank you very much fro your help.
ASKER
sorry my bad.
use today if today falls in the middle of contract start and contract end date
use contract start date if that date is greater than today (future)
use null if contract is closed, or its end date is in the past.
use today if today falls in the middle of contract start and contract end date
use contract start date if that date is greater than today (future)
use null if contract is closed, or its end date is in the past.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I came up with this statement but not sure if it is syntactically, logiaclly correct to the concept:
, [ContractOpenStartDate] =
(CASE
WHEN @Today BETWEEN [vsol].[ContractStartDate] AND [vsol].[ContractEndDate]
THEN @Today
ELSE
CASE
WHEN [vsol].[ContractStartDate] > @Today THEN [vsol].[ContractStartDate]
WHEN [vsol].[ContractEndDate] < @Today THEN NULL
END
END)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I meant to say "would be considered to be 12-31-2014 00:00:00" in the above comment.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much for the solution and all the good recommendations :)
when would the ContractOpenBeginDate not be today's date?