Subselect inside Case in sql?-CodePudding

I have this pretty code of mine, written by using "if". I want to know if its possible to do just the same thing, using "case". Inside "WeekNumber" are just numbers from 1 to 53.

I want to subselect inside case function

declare @currYear int = datepart(year,getdate());
declare @date date = datefromparts(@currYear,1,1);
--declare @date date = '2022-01-01'

declare @firstDayOfWeekToLast   date = DATEADD(day,-datepart(weekday,@date)   2,@date)
declare @lastDayOfWeekToLast    date = DATEADD(day,-datepart(weekday,@date)   6,@date)

declare @firstDayOfWeekToNew    date = DATEADD(day,-datepart(weekday,@date) - 5,@date)
declare @lastDayOfWeekToNew     date = DATEADD(day,-datepart(weekday,@date) - 1,@date)


select DATEPART(WEEKDAY,@date)

if(DATEPART(weekday,@date) > 5)
    begin
        select  format(DATEADD(week,WeekNumber,@firstDayOfWeekToLast),'dd.MM.yyyy') as 'First day of Week',
                format(DATEADD(week,WeekNumber,@lastDayOfWeekToLast),'dd.MM.yyyy') as 'Last day of Week'
        from WEEKNUMBER
    end

else if(DATEPART(weekday,@date) = 1)
    begin
    select  format(DATEADD(week,WeekNumber-1,@firstDayOfWeekToLast),'dd.MM.yyyy') as 'First day of Week',
                format(DATEADD(week,WeekNumber-1,@lastDayOfWeekToLast),'dd.MM.yyyy') as 'Last day of Week'
        from WEEKNUMBER
    end

else
    begin
        select  format(DATEADD(week,WeekNumber,@firstDayOfWeekToNew),'dd.MM.yyyy') as 'First day of Week',
                format(DATEADD(week,WeekNumber,@lastDayOfWeekToNew),'dd.MM.yyyy') as 'Last day of Week'
        from WEEKNUMBER
    end

CodePudding user response：

I am not sure if I completely understand your question but you could try the query below.

declare @currYear int = datepart(year,getdate());
declare @date date = datefromparts(@currYear,1,1);
--declare @date date = '2022-01-01'

declare @firstDayOfWeekToLast   date = DATEADD(day,-datepart(weekday,@date)   2,@date)
declare @lastDayOfWeekToLast    date = DATEADD(day,-datepart(weekday,@date)   6,@date)

declare @firstDayOfWeekToNew    date = DATEADD(day,-datepart(weekday,@date) - 5,@date)
declare @lastDayOfWeekToNew     date = DATEADD(day,-datepart(weekday,@date) - 1,@date)

--The changes I made start below.
SELECT
WEEKNUMBER,
CASE
WHEN (DATEPART(weekday,@date) > 5) THEN format(DATEADD(week,WeekNumber,@firstDayOfWeekToLast),'dd.MM.yyyy') 
WHEN (DATEPART(weekday,@date) = 1) THEN format(DATEADD(week,WeekNumber-1,@firstDayOfWeekToLast),'dd.MM.yyyy')
ELSE format(DATEADD(week,WeekNumber,@firstDayOfWeekToNew),'dd.MM.yyyy')
END 'First day of Week',
CASE 
WHEN (DATEPART(weekday,@date) > 5) THEN format(DATEADD(week,WeekNumber,@lastDayOfWeekToLast),'dd.MM.yyyy')
WHEN (DATEPART(weekday,@date) = 1) THEN format(DATEADD(week,WeekNumber-1,@lastDayOfWeekToLast),'dd.MM.yyyy')
ELSE format(DATEADD(week,WeekNumber,@lastDayOfWeekToNew),'dd.MM.yyyy')
END 'Last day of Week'
FROM WEEKNUMBER