CODEWITHSUNDEEP
sqlsql-server
michNik
michNik

Reputation: 7

How to find the 3rd business day of a month (Saturday and Sunday are weekend)?

I need to find next final pay period under few conditions. The maturity is 3 months, but when the last payday is till the 3st business day of the current month. Оtherwise is the maturity 4 months. And Saturday and Sunday are not working days. For example: now in August 2019 are 3.08 and 4.08 not working day and when the customer pay the tax till 5.08(Monday - this is the 3st business day for a August) the next payday period is till end of Oktober 2019. Otherwise when the day is 6.08 is the period till end of November

IF @Schema = '1000' 
BEGIN

SET @PayPeriod = 3
IF @Payday < DATEADD(DAY, CASE (DATEPART(WEEKDAY, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Payday), 0)) + @@DATEFIRST) % 7 
                                        WHEN 6 THEN 2
                                        WHEN 7 THEN 1 ELSE 0 END,
                                        DATEADD(MONTH, DATEDIFF(MONTH, 0, Payday), 3)) 
SET @PayPeriod = EOMONTH(@Payday, @PayPeriod-1)
ELSE SET @PayPeriod = EOMONTH(@Payday, @PayPeriod)
            END

And here is the result. After 06.08.2019 must be the payperiod end of November

2019-08-01 2019-10-31

2019-08-02 2019-10-31

2019-08-03 2019-10-31

2019-08-04 2019-11-30 2019-08-05 2019-11-30 here is the problem!

2019-08-06 2019-11-30

Upvotes: 1

Views: 1371

Answers (2)

ivanochkah
ivanochkah

Reputation: 93

This should work for you:

declare @payday as datetime = '20190602'

;with cte as ( 
    select datefromparts(year(@payday), month(@payday), 1) as monthday
    union all
    select dateadd(day,  1, monthday)
    from cte
    where day(monthday) < 10

    )
select 
    case 
        when count(*) <= 3 then eomonth(@payday, 3)
        else eomonth(@payday, 4)
    end as MaturityEnd
from cte
where datepart(weekday, monthday) not in (7, 1)
 and monthday <= @payday

Here we generate first couple of dates for the same month as in payment date and count business days smaller than payment date. Maturity end date is calculated based on the count of days.

Upvotes: 1

Hogan
Hogan

Reputation: 70538

It is not totally clear what you want from your question. I believe what you want is:

  • Give a date @@DATEFIRST find a date of maturity
  • 3 months later unless
  • that date falls in the first 3 business days of a month than then
  • 4 months later

Is that correct?

Also what does EOMONTH do? The use of it in your example does not seem to make sense compared to the stated requirements.

Upvotes: 0

Related Questions