Calculare pozitie zi in cursul unei luni

Situatie

Mai jos aveti o procedura ce are patru parametrii:

– data de inceput a unei luni

– data de sfarsit a aceleiasi luni

– un parametru ce primeste valori de la 1 la 5, care reprezinta pozitia in luna a unei zile

– un parametru care reprezinta numele zilei, si poate lua valorile: Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday

In functie de parametrul 3 si parametrul 4, procedura returneaza a x-a (parametrul 3) data din luna care este ziua aleasa in parametrul 4.

Spre exemplu sintaxa select dbo.BD_GetPosDayMonth (’06/01/2021′, ’06/30/2021′, 5, ‘Tuesday’) va returna data de 06/29/2021 care este a 5-a ocurenta in luna a zilei de marti. In cazul in care luna nu ar fi avut a 5-a ocurenta a zilei de marti, procedura ar fi returnat-o pe ultima, adica pe a 4-a.

Solutie

select dbo.BD_GetPosDayMonth (’06/01/2021′, ’06/30/2021′, 5, ‘Tuesday’)

ALTER FUNCTION dbo.BD_GetPosDayMonth (@start_date DATETIME,@end_date DATETIME,@When INT,@DayofWeek NVARCHAR(20))
RETURNS NVARCHAR(10)
AS

BEGIN
DECLARE @datecounter DATETIME
SET @datecounter = @start_date
DECLARE @MonthName NVARCHAR(20)
SET @MonthName = DATENAME(month,@start_date)
DECLARE @wd AS INT
IF @DayofWeek=’Monday’
BEGIN
SET @wd = 0
END
IF @DayofWeek=’Tuesday’
BEGIN
SET @wd = 1
END
IF @DayofWeek=’Wednesday’
BEGIN
SET @wd = 2
END
IF @DayofWeek=’Thursday’
BEGIN
SET @wd = 3
END
IF @DayofWeek=’Friday’
BEGIN
SET @wd = 4
END
IF @DayofWeek=’Saturday’
BEGIN
SET @wd = 5
END
IF @DayofWeek=’Sunday’
BEGIN
SET @wd = 6
END
DECLARE @date AS DATETIME

WHILE @datecounter <= @end_date
BEGIN
IF (SELECT DATENAME(MONTH, @datecounter))=@MonthName
BEGIN
IF @When = 1
BEGIN
SET @date = (SELECT CASE WHEN DATEPART(day,DATEADD(wk, DATEDIFF(wk,@wd, DATEADD(dd,6-DATEPART(day,@datecounter),@datecounter)), @wd)) > 7 Then DATEADD(wk, DATEDIFF(wk,@wd, DATEADD(dd,6-DATEPART(day,@datecounter),@datecounter)), @wd)-7
ELSE DATEADD(wk, DATEDIFF(wk,@wd, dateadd(dd,6-DATEPART(day,@datecounter),@datecounter)), @wd) END)
END
IF @When = 2
BEGIN
SET @date = (SELECT CASE WHEN DATEPART(day,DATEADD(wk, DATEDIFF(wk,@wd, DATEADD(dd,12-DATEPART(day,@datecounter),@datecounter)), @wd)) > 14 Then DATEADD(wk, DATEDIFF(wk,@wd, DATEADD(dd,12-DATEPART(day,@datecounter),@datecounter)), @wd)-7
ELSE DATEADD(wk, DATEDIFF(wk,@wd, dateadd(dd,12-DATEPART(day,@datecounter),@datecounter)), @wd) End)
END
IF @When = 3
BEGIN
SET @date = (SELECT CASE WHEN DATEPART(day,DATEADD(wk, DATEDIFF(wk,@wd, DATEADD(dd,18-DATEPART(day,@datecounter),@datecounter)), @wd)) > 21 Then DATEADD(wk, DATEDIFF(wk,@wd, DATEADD(dd,18-DATEPART(day,@datecounter),@datecounter)), @wd)-7
ELSE DATEADD(wk, DATEDIFF(wk,@wd, dateadd(dd,18-DATEPART(day,@datecounter),@datecounter)), @wd) End)
END
IF @When = 4
BEGIN
SET @date = (SELECT CASE WHEN DATEPART(day,DATEADD(wk, DATEDIFF(wk,@wd, DATEADD(dd,24-DATEPART(day,@datecounter),@datecounter)), @wd)) > 28 Then DATEADD(wk, DATEDIFF(wk,@wd, DATEADD(dd,24-DATEPART(day,@datecounter),@datecounter)), @wd)-7
ELSE DATEADD(wk, DATEDIFF(wk,@wd, dateadd(dd,24-DATEPART(day,@datecounter),@datecounter)), @wd) END)
END
IF @When = 5
BEGIN
SET @date = (SELECT CASE WHEN DATEPART(day,DATEADD(wk, DATEDIFF(wk,@wd, DATEADD(dd,30-DATEPART(day,@datecounter),@datecounter)), @wd)) > 28 then DATEADD(wk, DATEDIFF(wk,@wd, DATEADD(dd,30-DATEPART(day,@datecounter),@datecounter)), @wd)
ELSE DATEADD(wk, DATEDIFF(wk,@wd, dateadd(dd,24-DATEPART(day,@datecounter),@datecounter)), @wd) END)
END
END
SET @datecounter = DATEADD(month,1,@datecounter)
END
RETURN CAST(CONVERT(NVARCHAR,@date,101) as nvarchar(10))
END

Tip solutie

Permanent

Follow Us