Anzahl Arbeitstage zwischen zwei Datumsangaben in T-SQL (Extended)

Erweiterung dieser Frage: Hier

Ich würde gerne die Anzahl Arbeitstage zwischen zwei Datumsangaben in t-sql, gerade an Wochenenden, nicht aber die ganze Arbeit Tag (24 Stunden) nur sagen wir mal von 9 AM bis 5:30 Uhr jeden Tag arbeiten.

Wie kann ich das machen, dass z.B. mit CMS " - Ansatz:

 DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2008/10/01'
SET @EndDate = '2008/10/31'


SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

InformationsquelleAutor Somebody | 2013-03-18

Dies ist, was ich suchte, hier ist der code, falls jemand es brauchen:

Die Parameter sind:

@D1 (StartDate)
@D2 (EndDate)
@T1 (Business Hours - Start)
@T2 (Business Hours - End)

Die Funktion gibt ein float mit der Anzahl der Stunden.

CREATE FUNCTION [dbo].[WorkHoursDiff] (@D1 as datetime,@D2 as datetime,@T1 as int,@T2 as int)  
RETURNS float AS  
BEGIN 
    if @D1>@D2 return 0

    declare @whd as float

    declare @H1 as float
    declare @H2 as float


    if not (datepart(yyyy,@D1   )=datepart(yyyy,@D2) and datepart(mm,@D1)=datepart(mm,@D2) and datepart(dd,@D1)=datepart(dd,@D2))
        begin
            declare @DD as datetime
            SET @whd=0
            if datepart(dw,@D1)<>1 and datepart(dw,@D1)<>7 
                                        if dbo.GetDateHour(@D1)<@T1 SET @whd = @T2-@T1
            else if dbo.GetDateHour(@D1)>@T2 SET @whd = 0
            else if dbo.GetDateHour(@D1)>=@T1 and dbo.GetDateHour(@D1)<=@T2 SET @whd = @T2-dbo.GetDateHour(@D1)

            SET @DD=@D1 + 1
            while not (datepart(yyyy,@DD)=datepart(yyyy,@D2) and datepart(mm,@DD)=datepart(mm,@D2) and datepart(dd,@DD)=datepart(dd,@D2))
                begin
                    if not(datepart(dw,@DD)=1 or datepart(dw,@DD)=7)  
                         SET @whd=@whd + (@T2-@T1)

                    SET @DD=@DD + 1
                end
                if datepart(dw,@D2)<>1 and datepart(dw,@D2)<>7
                if dbo.GetDateHour(@D2)<@T1 SET @whd = @whd 
                else if dbo.GetDateHour(@D2)>@T2 SET @whd = @whd + @T2 - @T1
                else if dbo.GetDateHour(@D2)>=@T1  and dbo.GetDateHour(@D2)<=@T2 SET @whd = @whd +dbo.GetDateHour(@D2)-@T1

        end
    else
        begin
            if dbo.GetDateHour(@D1)<@T1 SET @H1=@T1
            else if dbo.GetDateHour(@D1)>@T2 SET @H1=@T2
            else SET @H1=dbo.GetDateHour(@D1)

            if dbo.GetDateHour(@D2)<@T1 SET @H2=@T1
            else if dbo.GetDateHour(@D2)>@T2 SET @H2=@T2
            else SET @H2=dbo.GetDateHour(@D2)

            if datepart(dw,@D1)=1 or datepart(dw,@D1)=7  SET @whd=0
            else SET @whd=@H2-@H1
        end
    return @whd

END

InformationsquelleAutor Somebody

1

Ich denke, dass Sie ziemlich gut war, nur ändern müssen, die DANN 1 DANN -1

Allerdings bin ich mir nicht ganz sicher, was Sie tun möchten mit den Stunden Teil Ihrer Frage.
```
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2013/03/01'
SET @EndDate = '2013/03/31'


SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN -1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN -1 ELSE 0 END)
```
- Die Stunden Teil wird die Geschäftszeiten der Arbeit Tag. In diesem Fall werde ich ändern müssen, die datediff-in Stunden statt in Tagen. Sagen wir mal so, ich will wissen, zu den Geschäftszeiten zwischen zwei Termine Montag und Dienstag, mit der oben genannten Lösung, ich werde 48 Stunden (2 Tage), aber mit den öffnungszeiten nur (9am to 5pm) von 16 Stunden, das ist es, was ich brauche.
InformationsquelleAutor crosan

fand diese..

 CREATE FUNCTION dbo.GetWorkingDays
  ( @StartDate datetime,
    @EndDate datetime )
RETURNS INT
AS
BEGIN
  DECLARE @WorkDays int, @FirstPart int
  DECLARE @FirstNum int, @TotalDays int
  DECLARE @LastNum int, @LastPart int
  IF (DATEDIFF(day, @StartDate, @EndDate) < 2)
    BEGIN
      RETURN ( 0 )
    END
  SELECT
   @TotalDays = DATEDIFF(day, @StartDate, @EndDate) - 1,
   @FirstPart = CASE DATENAME(weekday, @StartDate)
                 WHEN 'Sunday' THEN 6
                 WHEN 'Monday' THEN 5
                 WHEN 'Tuesday' THEN 4
                 WHEN 'Wednesday' THEN 3
                 WHEN 'Thursday' THEN 2
                 WHEN 'Friday' THEN 1
                 WHEN 'Saturday' THEN 0
               END,
   @FirstNum = CASE DATENAME(weekday, @StartDate)
                 WHEN 'Sunday' THEN 5
                 WHEN 'Monday' THEN 4
                 WHEN 'Tuesday' THEN 3
                 WHEN 'Wednesday' THEN 2
                 WHEN 'Thursday' THEN 1
                 WHEN 'Friday' THEN 0
                 WHEN 'Saturday' THEN 0
               END
  IF (@TotalDays < @FirstPart)
     BEGIN
       SELECT @WorkDays = @TotalDays
     END
  ELSE
     BEGIN
       SELECT @WorkDays = (@TotalDays - @FirstPart) / 7
       SELECT @LastPart = (@TotalDays - @FirstPart) % 7
       SELECT @LastNum = CASE
         WHEN (@LastPart < 7) AND (@LastPart > 0) THEN @LastPart - 1
         ELSE 0
       END
       SELECT @WorkDays = @WorkDays * 5 + @FirstNum + @LastNum
     END
  RETURN ( @WorkDays )
END
GO

InformationsquelleAutor Hagbart Celine

Versuchen, diese

Declare @startdate datetime,
@enddate datetime,
@countSunday int,
@countTuesday int,
@countWednesday int,@countThursday int,@countFriday int,@countSaturday int

set @countSunday=0
set @countMonday=0
set @countTuesday =0
set @countWednesday =0
set @countThursday =0
set @countFriday =0
set @countSaturday=0
set @startdate='2018-10-01'
set @enddate='2018-10-20'

while @startdate<=@enddate
Begin
    IF DatePart(WEEKDAY,@startdate) = 1
    SET @countSunday=@countSunday+1
    else if  DatePart(WEEKDAY,@startdate) = 2
      SET @countMonday=@countMonday+1  
      else if  DatePart(WEEKDAY,@startdate) = 3
      SET @countTuesday=@countTuesday+1  
      else if  DatePart(WEEKDAY,@startdate) = 4
      SET @countWednesday=@countWednesday+1  
      else if  DatePart(WEEKDAY,@startdate) = 5
      SET @countThursday=@countThursday+1  
      else if  DatePart(WEEKDAY,@startdate) = 6
      SET @countFriday=@countFriday+1  
      else if  DatePart(WEEKDAY,@startdate) = 7
      SET @countSaturday=@countSaturday+1  
    SET @startdate=DateAdd(d,1,@startdate)
END


select @countSunday as Sunday ,@countMonday as Monday,@countTuesday as Tuesday,@countWednesday as Wednesday,@countThursday as Thursday,@countFriday as Friday,@countSaturday as Saturday

InformationsquelleAutor Abdulsalam KMP

Versuchen, diese

DECLARE @DateFrom DateTime ='20181001' , @DateTo DateTime = '20181031'  ;
WITH CTE(dt)
AS
(
SELECT @DateFrom
UNION ALL
SELECT DATEADD(d, 1, dt) FROM CTE
  WHERE dt < @DateTo
)
SELECT datename("dw", dt) as Days,count(datename("dw", dt)) as numberofDays FROM CTE 
group by datename ("dw", dt)

InformationsquelleAutor Abdulsalam KMP

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.