Search code examples
sql-serverssrs-2008

Date Calculation using DateAdd function not calculating correct date

I need to calculate six months based on below condition : If EPC Review Date is present then Six Month = EPC ReviewDate + 6M (disregard all other dates) If MDS and modified both present and MDS Review Date > Modified, then Six Month = MDS review date + 6M if MDS and Modified both present and Modified > MDS, then six months = Modified + 6M

I wrote the below code

,SIXMONTH = CASE WHEN (EPC.REVIEWDATE IS NOT NULL OR EPC.REVIEWDATE <> '') THEN 
                                            CONVERT(VARCHAR(10),DATEADD(MM,6,EPC.REVIEWDATE),101) 
                 WHEN ((EPC.REVIEWDATE IS NULL OR EPC.REVIEWDATE = '') AND (MODI.MODIReviewDate IS 
                         NULL OR MODI.MODIReviewDate = '')) THEN 
                                         CONVERT(VARCHAR(10),DATEADD(MM,6,MDS.MDSReviewDate),101)
                 WHEN ((EPC.REVIEWDATE IS NULL OR EPC.REVIEWDATE = '') AND MDS.MDSReviewDate > 
                        MODI.MODIReviewDate) THEN 
                                          CONVERT(VARCHAR(10),DATEADD(MM,6,MDS.MDSReviewDate),101)
                WHEN ((EPC.REVIEWDATE IS NULL OR EPC.REVIEWDATE = '') AND (MDS.MDSReviewDate < 
                        MODI.MODIReviewDate)) THEN 
                                      CONVERT(VARCHAR(10),DATEADD(MM,6,MODI.MODIReviewDate),101)  
              END

But my conditions are not working correctly, and it's not calculating date correctly. If data is like below

EPC_revire Date    MDS_revieDate   Modi_reviewDate    SixMonth
NULL                04/27/2022       09/01/2021        03/01/2022 -- it should add 6 months to MDS
Null                11/10/2021       06/23/2022        05/10/2022 -- it should add 6 month to Modi
10/25/2021           07/21/2021      null              04/25/2022 -- it correctly added 6 months to EPC

Can anyone help, please.

Solution

  • Give this a try:

    SIXMONTH = CASE 
  WHEN EPC.REVIEWDATE IS NOT NULL THEN dateadd(month, 6, EPC.REVIEWDATE)
  WHEN MODI.MODIReviewDate > MDS.MDSReviewDate THEN dateadd(month, 6, MODI.MODIReviewDate)
  WHEN MODI.MODIReviewDate < MDS.MDSReviewDate THEN dateadd(month, 6, MDS.MDSReviewDate)
END

    I leave you to figure out what to do when MODI.MODIReviewDate = MDS.MDSReviewDate. If either of those dates (MODIReviewDate, MDSReviewDate) is null, the comparison will return UNKNOWN and fall through to the next branch (if present). This is why you don't need to keep repeating various comparisons.

    Note that you save a TRIVIAL amount of typing using the datepart abbreviation; using the complete datepart name makes the code much easier to read and understand. A little effort at formatting your code also helps.

    I also ignored the converting to varchar(10). Formatting belongs in the presentation layer.