Remove Consecutive Dates in Report

• Ben Pelech

  Newbie

  Points: 3

  More actions

  September 12, 2006 at 8:50 pm

  #116687

  Is there a way to remove consecutive dates from a report. I need the tables to maintain the dates but on the report only show something like 9/1/06-9/5/06 (and include consecutive business days... if one day was a Friday and the other a Monday)?  Please help

• Sreejith Sreedharan

  SSCertifiable

  Points: 5403

  More actions

  September 12, 2006 at 10:46 pm

  #660001

  Assuming that the work week is Monday to Sunday here is how you can do it.

  Declare @date1 datetime

  set @date1 ='9/3/2006'

  SET DATEFIRST 1

  select @date1 as date1,DateAdd(d,-1 * DatePart(dw,@date1)+1,@date1) as WeekBegin

  SET DATEFIRST 7

  Changing any date will get you to the Monday of the week. You can use this logic on the date columns to get what you want.

  Hope this helps

  Thanks

  Sreejith