Query related to Dates

• sqlenthu 89358

  Hall of Fame

  Points: 3069

  More actions

  May 22, 2018 at 2:59 pm

  #338737

  Hi SQL Masters,

  I have a query related to dates. I have one table named dbo.Calender. I have attached one excel file. Here calendar sheet contains sample data for some dates of Q4 2018. From this table i want to have a result of three columns like this:
  LookupDate       PreviousYearBD        PreviousYear2BD

  Second sheet in attached excel contains the two scenarios of output containing boundary condition.
  Scenario 1
  1. First Column Date is lookup BD
  2. Second & third are corresponding previous years BD
  3. If any of previous year BDs finish before lookup BD (CurrentYearBD) at the end, then the last date will be repeated like 3/31/2015

  Scenario 2
  1. First Column Date is lookup BD
  2. Second & third are corresponding previous years BD
  3. If any of previous year have more BDs than lookup BD (CurrentYearBD), then secondlast BD should be ignored from previous years. In short,last quarter BD should match last quarter BD.

  Please let me know away to do it in single query.