Build the calendar table for current year including weeknum divided to semester

• sunitha 20026

  Mr or Mrs. 500

  Points: 509

  More actions

  May 23, 2017 at 8:04 pm

  #321622

  for the below query i need divide the semesters starting from 1 to 26 .. first semester as 1 to 26 weeks and first semester should start from again 1 to 26

  Any help is greating appreciated

     Declare @StartDate datetime

  Declare @CutoffDate datetime

    SET @StartDate = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)

  SET @CutoffDate = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1)

    SELECT Day , [Week nu], Semester ,[Semester Week nu] INTO     #currentDates FROM ( SELECT Day = DATEADD(DAY, rn - 1,        @StartDate) ,DATEPART(WEEK,DATEADD(DAY, rn - 1,     @StartDate)) [Week nu] , CASE WHEN MONTH(DATEADD(DAY, rn - 1,     @StartDate) ) <= 6 THEN 'First Semester' Else 'Second Semester'      end as Semester ,DATEPART(WEEK,DATEADD(DAY, rn - 1,     @StartDate)) as [Semester Week nu] FROM ( SELECT TOP    (DATEDIFF(DAY, @StartDate, @CutoffDate)) rn = ROW_NUMBER() OVER    (ORDER BY s1.[object_id]) FROM sys.all_objects AS s1 CROSS JOIN     sys.all_objects AS s2 ORDER BY s1.[object_id] ) AS x ) AS y;---

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  May 24, 2017 at 2:51 am

  #1943940

  SELECT
     Day
,    CASE
        WHEN [Semester Week nu] > 26 THEN [Semester Week nu] -26
        ELSE [Semester Week nu]
    END AS SemesterWeekNo
FROM #currentDates

  John