calculate date

metropia
metropia used Ask the Experts™
on
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

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

Open in new window


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

Open in new window

A contract is closed if its end date is in the past.

Thank you very much for your help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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.

Author

Commented:
my bad

                  ,      CASE
                              WHEN @Today < [sbo_wcnd].[ContractEnd]
                                    THEN [sbo_wcnd].[ContractEnd]
                              ELSE NULL
                        END      AS [ContractOpenEndDate]
Commented:
Looks Good metropia
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
ContractOpenEndDate looks ok as is
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

Open in new window

Top Expert 2013
Commented:
@PortletPaul: "ELSE NULL" is only required for aesthetic purposes. When omitted it will be NULL by default

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial