Soru Döngü içermeyen SQL'de bugüne kadar iş günlerini ekle


Şu anda SQL veritabanımda belirli bir iş günü tarihini bir tarihe ekleyen bir işlev var. Perşembe günü olan bir tarih girip iki gün eklerseniz, sonraki Pazartesi gününü döndürür. Herhangi bir tatilden rahatsız değilim, sadece hafta sonları hariç tutuluyor.

Sorun şu ki, bu işlem şu anda bir while döngüsü kullanılarak yapılmakta ve bir tablo oluştururken bunu kullanan saklı yordamı yavaşlatıyor gibi görünmektedir. Bu hesaplamayı döngüler veya imleçler olmadan gerçekleştirmenin herhangi bir yolu olup olmadığını bilen var mı?

Sadece bilgi için, şu anki işlev:

ALTER FUNCTION [dbo].[AddWorkDaysToDate]
(   
@fromDate       datetime,
@daysToAdd      int
)
RETURNS datetime
AS
BEGIN   
DECLARE @toDate datetime
DECLARE @daysAdded integer

-- add the days, ignoring weekends (i.e. add working days)
set @daysAdded = 1
set @toDate = @fromDate

while @daysAdded <= @daysToAdd
begin
    -- add a day to the to date
    set @toDate = DateAdd(day, 1, @toDate)
    -- only move on a day if we've hit a week day
    if (DatePart(dw, @toDate) != 1) and (DatePart(dw, @toDate) != 7)
    begin
        set @daysAdded = @daysAdded + 1
    end
end

RETURN @toDate

END

29
2018-03-29 11:07


Menşei


Resmi tatillere ne dersin? - Alex
Daha kısa bir çözüm yok. Kod parçası iyi görünüyor, yürütmek için zaman almamalı. Belki başka bir şey yavaşlar ... Bu prosedürü kaldırarak test edebilir ve performansın iyileşip iyileşmediğini görebilirsin. - Dheer


Cevaplar:


Bu cevap, orijinali yanlış olduğu için kabul edildiğinden beri önemli ölçüde değiştirilmiştir. Yine de yeni sorguda kendime güveniyorum ve buna bağlı değil DATEFIRST


Bence bunu kapsamalı:

declare @fromDate datetime
declare @daysToAdd int

select @fromDate = '20130123',@DaysToAdd = 4

declare @Saturday int
select @Saturday = DATEPART(weekday,'20130126')

;with Numbers as (
    select 0 as n union all select 1 union all select 2 union all select 3 union all select 4
), Split as (
    select @DaysToAdd%5 as PartialDays,@DaysToAdd/5 as WeeksToAdd
), WeekendCheck as (
    select WeeksToAdd,PartialDays,MAX(CASE WHEN DATEPART(weekday,DATEADD(day,n.n,@fromDate))=@Saturday THEN 1 ELSE 0 END) as HitWeekend
    from
    Split t
        left join
    Numbers n
        on
            t.PartialDays >= n.n
group by WeeksToAdd,PartialDays
)
select DATEADD(day,WeeksToAdd*7+PartialDays+CASE WHEN HitWeekend=1 THEN 2 ELSE 0 END,@fromDate)
from WeekendCheck

Eklenecek zamanı birkaç haftaya ve bir hafta içinde birkaç güne ayırırız. Daha sonra, birkaç gün ekleyeceğimiz bir cumartesi vurmamıza sebep olacaksa, küçük sayılar tablosu kullanırız. Eğer yaparsa, toplama 2 gün daha eklemeliyiz.


14
2018-03-29 12:23



Mükemmel, çok iyi çalışıyor. Tarihler 7'ye, yani pazar gününe ayarlanmış olan İngiliz SQL veritabanlarıyla çalışanlarınız için, WHEN 6'yı WHEN 7 ve WHEN 7'den WHEN 8'e değiştirmeyi unutmayın. - Matt King
@MattKing Benim datefirst de 7'ye ayarlandı ama önerdiğiniz değişiklik benim için çalışmasını sağlamak için yeterli değildi. Geldiğim çözüm için cevabımı görün. - Nate Cook
Bu çözüm yanlış sonuçlar üretir: I.e. Cuma 11 Mart 1983'e 3 gün eklenmesi, çarşamba 16 mart 1983 üretmelidir. Ancak, bu işlev pazartesi 14 mart 1983 üretir. select @fromDate = '03-11-1983', @DaysToAdd = 3 - Martin Devillers
@MartinDevillers haklı. Bugünün tarihine bir iş günü eklenmesi Cuma, 25 Ocak 2013, Pazartesi, 28 Ocak. Şimdi eklemeyi dene iki veya üç iş günleri. Aynı sonuç. - bernhof
@Bernhof - negatif sayılar hiçbir zaman çalışmamın bir parçası değildi. - Damien_The_Unbeliever


Birisi TSQL çözümü arıyorsa bu daha iyidir. Döngü yok, masa yok, durum ifadesi yok VE negatiflerle çalışıyor. Bunu kimse yenebilir mi?

CREATE FUNCTION[dbo].[AddBusinessDays](@Date date,@n INT)
RETURNS DATE AS 
BEGIN
DECLARE @d INT;SET @d=4-SIGN(@n)*(4-DATEPART(DW,@Date));
RETURN DATEADD(D,@n+((ABS(@n)+@d-2)/5)*2*SIGN(@n)-@d/7,@Date);
END

28
2018-02-13 14:20



Bunu kullandım, diğerleri negatiflerle çalışmıyor gibiydi - agrath
Bu bir çekicilik gibi çalışır! - Edenbauer
Bu açıkça burada en iyi cevap - ebooyens
Ama bu özel bir şey varsayar DATEFIRST Bazılarının ihtiyaç duymadığı ayar (7). - Damien_The_Unbeliever
Geçtiğimiz tarih Pazar günü ise olumsuz değerler çalışmaz. - FistOfFury


Bu soru için kabul edilen yanıtın oluşturulmasında, aşağıdaki kullanıcı tanımlı işlev (UDF) her durumda çalışmalıdır; @@DateFirst.

GÜNCELLEME: Aşağıdaki yorumlar belirtildiği gibi, bu işlev FromDate'in hafta içi olması için tasarlanmıştır. Bir hafta sonu günü FromDate olarak geçirildiğinde davranış tanımlanmamıştır.

ALTER FUNCTION [dbo].[BusinessDaysDateAdd] 
(
   @FromDate datetime,
   @DaysToAdd int
)
RETURNS datetime
AS
BEGIN
   DECLARE @Result datetime

   SET @Result = DATEADD(day, (@DaysToAdd % 5) + CASE ((@@DATEFIRST + DATEPART(weekday, @FromDate) + (@DaysToAdd % 5)) % 7)
                                                 WHEN 0 THEN 2
                                                 WHEN 1 THEN 1
                                                 ELSE 0 END, DATEADD(week, (@DaysToAdd / 5), @FromDate))

   RETURN @Result
END

7
2017-08-30 02:18



Mükemmel, teşekkürler! - Patrick Honorez
Sadece bir not ama beklerdim BusinessDaysDateAdd('2013-09-01', 5) 2013-09-06, ancak 2013-09-09 döndürür. - EBarr
Bir liner stilini seviyorum, ama @EBarr'ın işaret ettiği gibi işe yaramıyor. Ancak, CASE'yi CASE WHEN ((@@DATEFIRST + DATEPART(weekday, @FromDate)) % 7 + (@DaysToAdd % 5)) > 6 THEN 2 ELSE 0 END hile yapmak gibi görünüyor. - Coffee Ninja
@Amine tarafından yapılan önceki yorum tarafından kafam karışmıştı. Forumla DATEADD (gün, (@DaysToAdd% 5) + [buradaki Amine'in kopyaları], DATEADD (hafta, (@DaysToAdd / 5), @FromDate)) olacaktır. - FistOfFury
"2014-04-27" gibi bir Pazar günü gerçekleşen bir tarihe 5 iş günü eklenmesi, aşağıdaki Cuma'yı döndürmelidir, ancak bu işlev yerine aşağıdaki Pazartesi'yi döndürür (5 gün değil, 7 gün ekler). - ctorx


Tüm çalışma günlerini içeren (örneğin fonksiyonunuzu kullanarak) bir çalışma tablosunu önceden doldurmayı düşündünüz, örneğin WorkDays (int DaySequenceId, Date WorkingDate), daha sonra bu tabloyu @fromDate'in DaySequenceId öğesini seçerek kullanabilirsiniz. ve yeni çalışma tarihini almak için @ daysToAdd ekleyin. Açıkçası bu yöntemin, WorkDays tablosunu yönetme ek yükü de vardır, ancak beklediğiniz tarih aralığıyla önceden doldurabilirsiniz. Diğer olumsuz ise, hesaplanabilecek çalışma tarihleri ​​sadece Çalışma Günleri tablosunda bulunanlar olacaktır.


5
2018-03-29 11:19



İş günleriyle uğraşmaya başladığınızda, bir tür takvim tablosu eklemek genellikle daha iyi olur. - Damien_The_Unbeliever


Bu cevaplar dayanmaktadır @ ElmerMiller'ın cevabı.

Pazar öğesindeki @FistOfFury'den alınan negatif değeri düzeltir

Geçtiğimiz tarih Pazar günü ise olumsuz değerler çalışmaz

Ve DATEFIRST ayarının yorumu @Damien_The_Unbeliever

Ancak bu, bazılarının ihtiyaç duymadığı belirli bir DATEFIRST ayarı (7) olduğunu varsayar.

Şimdi düzeltilmiş işlev

CREATE FUNCTION[dbo].[AddBusinessDays](@Date DATE,@n INT)
RETURNS DATE AS 
BEGIN
DECLARE @d INT,@f INT,@DW INT;
SET @f=CAST(abs(1^SIGN(DATEPART(DW, @Date)-(7-@@DATEFIRST))) AS BIT)
SET @DW=DATEPART(DW,@Date)-(7-@@DATEFIRST)*(@f^1)+@@DATEFIRST*(@f&1)
SET @d=4-SIGN(@n)*(4-@DW);
RETURN DATEADD(D,@n+((ABS(@n)+(@d%(8+SIGN(@n)))-2)/5)*2*SIGN(@n)-@d/7,@Date);
END

4
2018-03-17 17:43



Benim için çalışıyor! Ama nasıl çalıştığından emin değilim. Kodu açıklar mısınız lütfen? - lucasvscn


* Bunun eski bir iş parçacığı olduğunu biliyorum ama bir süre önce son derece kullanışlı bir şey buldum, değiştirdim ve bunu aldım.

select ((DATEADD(d,DATEDIFF(d,0,(DATEADD (d,2,@fromDate))),@numbOfDays)))*

Güncelleme: Kodun bir parçasını (tek bir deyimde) bulmak için acele ettiğim için üzgünüm ve bir işlev kullanmaktan kaçınmak için hatalı kod gönderdim.

Eklediğiniz günlerin sayısı 7 veya daha azsa, yukarıda belirtilen bit kullanılabilir.

Daha iyi anlamak için kodu gerekli parametrelerle değiştirdim.

Neyse, ben 'Nate Cook' yukarıda belirtilen ne kullanarak bitti. Ve bunu tek bir kod satırı olarak kullandı. (Çünkü işlevleri kullanmama engelliyim)

Nate'in kodu

select(
DATEADD(day, (@days % 5) + 
CASE ((@@DATEFIRST + DATEPART(weekday, GETDATE()) + (@days % 5)) % 7)
WHEN 0 THEN 2
WHEN 1 THEN 1
ELSE 0 END, DATEADD(week, (@days / 5), GETDATE()))
)

4
2017-08-21 09:58



Çözümünüzün daha iyi bir açıklamaya ihtiyacı var. Lütfen daha iyi bir kullanıcı anlayışı için uygun bir şekilde açıklayınız .. - Shivaay


CREATE FUNCTION DateAddBusinessDays
(
    @Days int,
    @Date datetime  
)
RETURNS datetime
AS
BEGIN
    DECLARE @DayOfWeek int;

    SET @DayOfWeek = CASE 
                        WHEN @Days < 0 THEN (@@DateFirst + DATEPART(weekday, @Date) - 20) % 7
                        ELSE (@@DateFirst + DATEPART(weekday, @Date) - 2) % 7
                     END;

    IF @DayOfWeek = 6 SET @Days = @Days - 1
    ELSE IF @DayOfWeek = -6 SET @Days = @Days + 1;

    RETURN @Date + @Days + (@Days + @DayOfWeek) / 5 * 2;
END;

Bu işlev, @@ DATEFIRST değerinden bağımsız olarak iş günlerini ekleyebilir ve çıkartabilir. İş günlerini çıkarmak için negatif gün sayısı kullanın.


2
2017-07-26 13:54



Geçtiğimiz tarih bir cumartesi ise işe yaramıyor. - FistOfFury