How can I find last day of a season (using start date and day of the week)?-CodePudding

I have some problems in finding the last day of week in a season. All I have is first date of a class.

Every course can use just in its season; For example you want to enroll in a course that have started on '2022-05-03'. you can participate in this course till end of spring 2022.(class is held one day a week).

Is there any function to find date of last Monday in spring 2022?

I have this table.

CREATE TABLE Class(
Class_ID BIGINT,
c_InstrumentID BIGINT NOT NULL,
c_StudentID BIGINT,
c_InstructorID BIGINT NOT NULL,
c_InstituteId BIGINT NOT NULL,
c_TermSeason NVARCHAR(10),
c_TermYear INT,
c_TimeOfClass TIME NOT NULL,
c_DayOfClass NVARCHAR(30),
c_Eligibility INT,
c_RemainingSession INT,
CONSTRAINT cons_Season CHECK(c_TermSeason IN ('Spring', 'Summer', 'Fall', 'Winter')),
CONSTRAINT cons_TimeClass CHECK(c_TimeOfClass BETWEEN '08:30:00' AND '20:30:00'),
CONSTRAINT cons_RemainSession CHECK (c_RemainingSession BETWEEN -1 AND 13),--Update CONSTRAINT,
FOREIGN KEY(c_InstrumentID) REFERENCES Instrument(Instrument_ID) ON DELETE NO ACTION,
FOREIGN KEY(c_StudentID) REFERENCES Student(Student_ID) ON DELETE NO ACTION,
FOREIGN KEY(c_InstructorID) REFERENCES Instructor(Instructor_ID) ON DELETE NO ACTION,
FOREIGN KEY(c_InstituteId) REFERENCES Institute(Institute_ID) ON DELETE NO ACTION,
PRIMARY KEY (Class_ID)
)

the trigger of class for columns that are null.

GO
CREATE OR ALTER TRIGGER DateTime_Class ON Class
AFTER INSERT
AS
BEGIN
    DECLARE @month_OfClass INT;
    SET @month_OfClass = (SELECT DATEPART(MONTH, newC.First_Session) FROM inserted newC)
    UPDATE Class
    SET c_TermYear = DATEPART(YEAR, First_Session),
        c_TermSeason = (
            SELECT CASE
                WHEN @month_OfClass IN (1,2,12) THEN 'Winter'
                WHEN @month_OfClass IN (3,4,5) THEN 'Spring'
                WHEN @month_OfClass IN (6,7,8) THEN 'Summer'
                WHEN @month_OfClass IN (9,10,11) THEN 'Fall'
            END),
        c_DayOfClass = (SELECT DATENAME(WEEKDAY,First_Session) FROM inserted)
        --,Final_Session = ?? What do I have to do here? 
        -- For Example if my class is on Monday and first session of class is on 2022-02-02
        --How can i find final Session by having first session, season, year and day of week
        --Consider class in December 2021 must ended in February 2022 
    WHERE Class_ID = (SELECT newC.Class_ID FROM inserted newC);
    
    IF(NOT EXISTS(SELECT * FROM Class C JOIN inserted newC ON newC.Class_ID = C.Class_ID
        WHERE DATEDIFF(MINUTE, 0, C.c_TimeOfClass) % DATEDIFF(MINUTE, 0, '00:30:00') = 0))
        ROLLBACK TRAN
END;

CodePudding user response：

You can find the day of the week number of the last day of the season by using the "DATEPART" function. By using "DATEADD" function you can find the day of the week you want

SELECT DATEPART(weekday ,'2022-06-30') --- RETURN 5 For Thursday

I want to find Monday. Monday is the second day of the week. To find the date of the last Monday, we need to go back 3 days from the date written

SELECT DATEADD(day ,-3 ,'2022-06-30') -- RETURN '2022-06-27'

CodePudding user response：

To answer your specific sql-server does not have a function which returns start and end dates for seasons - you have to write this yourself. The rest of the question boils down to give me the date of the most recent monday prior or equal to a given date

Consider creating a season table for example

DROP TABLE T;
CREATE TABLE T
(SEASON INT, YEAR INT, STARTDT DATE,ENDDT DATE);
GO
INSERT INTO T VALUES
(1,2020,NULL,NULL),(2,2020,NULL,NULL),(3,2020,NULL,NULL),(4,2020,NULL,NULL),
(1,2021,NULL,NULL),(2,2021,NULL,NULL),(3,2021,NULL,NULL),(4,2021,NULL,NULL),
(1,2022,NULL,NULL),(2,2022,NULL,NULL),(3,2022,NULL,NULL),(4,2022,NULL,NULL),
(1,2023,NULL,NULL),(2,2023,NULL,NULL),(3,2023,NULL,NULL),(4,2023,NULL,NULL),
(1,2024,NULL,NULL),(2,2024,NULL,NULL),(3,2024,NULL,NULL),(4,2024,NULL,NULL),
(1,2025,NULL,NULL),(2,2025,NULL,NULL),(3,2025,NULL,NULL),(4,2025,NULL,NULL)


UPDATE T
 SET STARTDT = CONCAT(YEAR,'-03-20'),
     ENDDT = CONCAT(YEAR,'-06-21')
 WHERE SEASON = 1;

Then calculating the previous monday is reasonably straightforward

SELECT * ,
        DATENAME(WEEKDAY,ENDDT),
        DATEPART(WEEKDAY,ENDDT),
        CASE WHEN DATEPART(WEEKDAY,ENDDT) = 1 THEN -6 ELSE 2 -DATEPART(WEEKDAY,ENDDT) END DIFF,
        CASE WHEN DATEPART(WEEKDAY,ENDDT) = 1 THEN 
             DATEADD(DAY,-6,ENDDT)
        ELSE DATEADD(DAY, 2 -DATEPART(WEEKDAY,ENDDT),ENDDT) 
        END  PREVMONDAY,
        CASE WHEN DATEPART(WEEKDAY,ENDDT) = 1 THEN 
             DATENAME(WEEKDAY,DATEADD(DAY,-6,ENDDT))
        ELSE DATENAME(WEEKDAY,DATEADD(DAY, 2 -DATEPART(WEEKDAY,ENDDT),ENDDT)) 
        END  PREVMONDAY
FROM T
WHERE SEASON = 1;

SEASON      YEAR        STARTDT    ENDDT                                                 DIFF        PREVMONDAY PREVMONDAY
----------- ----------- ---------- ---------- ------------------------------ ----------- ----------- ---------- ------------------------------
1           2020        2020-03-20 2020-06-21 Sunday                         1           -6          2020-06-15 Monday
1           2021        2021-03-20 2021-06-21 Monday                         2           0           2021-06-21 Monday
1           2022        2022-03-20 2022-06-21 Tuesday                        3           -1          2022-06-20 Monday
1           2023        2023-03-20 2023-06-21 Wednesday                      4           -2          2023-06-19 Monday
1           2024        2024-03-20 2024-06-21 Friday                         6           -4          2024-06-17 Monday
1           2025        2025-03-20 2025-06-21 Saturday                       7           -5          2025-06-16 Monday

(6 row(s) affected)