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;---