calculate date
I am working on a case statement, but I am unsure I am getting the logic from correct from the pseudo code. I was wondering if someone can give me a hand and let me know if my case seems to be doing what expected based on the words.
That is:
ContractOpenStartDate = Equal to TODAY if in the middle of contract OR if contract has not started yet OR NULL if Contract is closed
And also...
ContractOpenEndDate = ALWAYS EndDate or NULL if contract is closed.
Thank you very much for your help.
That is:
ContractOpenStartDate = Equal to TODAY if in the middle of contract OR if contract has not started yet OR NULL if Contract is closed
CASE
WHEN [sbo_wcnd].[ContractStart] <= @Today AND [sbo_wcnd].[ContractEnd] >= @Today
THEN @Today
WHEN [sbo_wcnd].[ContractStart] > @Today
THEN [sbo_wcnd].[ContractStart]
WHEN [sbo_wcnd].[ContractEnd] < @Today
THEN NULL
END AS ContractOpenStartDate
And also...
ContractOpenEndDate = ALWAYS EndDate or NULL if contract is closed.
CASE
WHEN [sbo_wcnd].[ContractStart] <= @Today AND [sbo_wcnd].[ContractEnd] >= @Today
THEN @Today
WHEN [sbo_wcnd].[ContractStart] > @Today
THEN [sbo_wcnd].[ContractStart]
WHEN [sbo_wcnd].[ContractEnd] < @Today
THEN NULL
END AS ContractOpenStartDate
Thank you very much for your help.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
What you are trying to do here is Correct as per the statements you have given.
The only thing I'm confused is you have placed the same code twice.
The only thing I'm confused is you have placed the same code twice.
my bad
, CASE
WHEN @Today < [sbo_wcnd].[ContractEnd]
THEN [sbo_wcnd].[ContractEnd]
ELSE NULL
END AS [ContractOpenEndDate]
, CASE
WHEN @Today < [sbo_wcnd].[ContractEnd]
THEN [sbo_wcnd].[ContractEnd]
ELSE NULL
END AS [ContractOpenEndDate]
ContractOpenEndDate looks ok as is
For ContractOpenStartDate you could use BETWEEN and ELSE NULL
For ContractOpenStartDate you could use BETWEEN and ELSE NULL
CASE
WHEN @Today BETWEEN [sbo_wcnd].[ContractStart] AND [sbo_wcnd].[ContractEnd]
THEN @Today
WHEN [sbo_wcnd].[ContractStart] > @Today
THEN [sbo_wcnd].[ContractStart]
ELSE NULL
END AS ContractOpenStartDate
Premium Content
You need an Expert Office subscription to comment.Start Free Trial