Just nu i M3-nätverket
Gå till innehåll
KTZ

Minuter mellan två datumstämplar (ta höjd för raster, och helger...)

Rekommendera Poster

Hej ärade forummedlemmar! ;-)

Jag har försökt att få till en lösning i MS T-SQL som kan berätta för mig hur många minuter som har gått mellan två tidsstämplar. Svaret ska ta höjd för när arbetsdagen börjar och slutar (samma hela tiden), olika raster under dagens arbetsperiod samt helg (lördag och söndag).

 

Min kunskap är något begränsad när det gäller T-SQL men jag är fast besluten att lära mig och undrar om ni hade kunnat ge förslag på hur detta skulle kunna lösas på ett effektivt och smidigt sätt.

 

Skapa tabell 1, innehåller tid för start och stopp för dagen.

CREATE TABLE #tbl_Arbetsdag(Start time,Stopp time,Minuter int);

Skapa tabell 2, innehåller tider för olika raster under dagen.

CREATE TABLE #tbl_Raster(Start time,Stopp time,Minuter int,Rast varchar(20));

Skapa tabell 3, innehåller objekt som ska få antal "Netto Minuter"

CREATE TABLE #tbl_Objekt(Object varchar(20),Start datetime,Stopp datetime,Brutto_Minuter int,Netto_Minuter int);

Lägger in aktuella värden i temp-tabellerna.

INSERT #tbl_Arbetsdag VALUES ('07:00:00', '15:42:00', Datediff(minute,'07:00:00','15:42:00'));
INSERT #tbl_Raster VALUES ('08:48:00', '09:00:00', Datediff(minute,'08:48:00','09:00:00'), 'Frukost');
INSERT #tbl_Raster VALUES ('10:30:00', '11:00:00', Datediff(minute,'10:30:00','11:00:00'), 'Förmiddagsrast');
INSERT #tbl_Raster VALUES ('12:30:00', '13:00:00', Datediff(minute,'12:30:00','13:00:00'), 'Lunch');
INSERT #tbl_Raster VALUES ('14:30:00', '15:00:00', Datediff(minute,'14:30:00','15:00:00'), 'Eftermiddagsrast');
INSERT #tbl_Objekt VALUES ('Objekt 1', '2013-11-22 14:21', '2013-11-25 09:16', Datediff(minute, '2013-11-22 14:21', '2013-11-25 09:16'), NULL);
INSERT #tbl_Objekt VALUES ('Objekt 2', '2013-11-20 07:30', '2013-11-20 11:40', Datediff(minute, '2013-11-20 07:30', '2013-11-20 11:40'), NULL);
INSERT #tbl_Objekt VALUES ('Objekt 3', '2013-11-18 08:10', '2013-11-18 13:45', Datediff(minute, '2013-11-18 08:10', '2013-11-18 13:45'), NULL);
-- ## SQL KOD FÖR ATT TA FRAM ANTAL NETTO-MINUTER FÖR DE OLIKA OBJEKTEN  ## --
SELECT * FROM #tbl_Arbetsdag;
SELECT * FROM #tbl_Raster;
SELECT * FROM #tbl_Objekt;
DROP TABLE  #tbl_Arbetsdag;
DROP TABLE  #tbl_Raster;
DROP TABLE  #tbl_Objekt;

 

Redigerad av KTZ

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser
Jag har inte något lösning ännu, men jag är inne på ett annat spår kring tabellerna. Slår ihop två tabeller till en "kalender" med koden nedan, för att få till en bättre lösning, verkar som jag måste ta höjd för både datum och tid för att komma fram till lösningen. Någon annan som har erfarenhet av detta så är jag tacksam för varje råd i rätt riktning.
CREATE TABLE #tbl_Kalender(Kal_ID int, Kal_Datum date, Kal_Dag_Start datetime, Kal_Dag_Stopp datetime, Kal_Dag_Minuter int, Kal_Dag_Helg int, Kal_Rast_Start datetime, Kal_Rast_Stopp datetime, Kal_Rast_Minuter int, Kal_Rast_Namn varchar(30))

SET DATEFIRST 1;
DECLARE @Loop_Dagar int
DECLARE @Datum datetime
DECLARE @Dag_Start time
DECLARE @Dag_Stopp time
DECLARE @Rast1_Start time
DECLARE @Rast1_Stopp time
DECLARE @Rast2_Start time
DECLARE @Rast2_Stopp time
DECLARE @Rast3_Start time
DECLARE @Rast3_Stopp time
DECLARE @Rast4_Start time
DECLARE @Rast4_Stopp time

SET @Datum = CAST(GETDATE()-2 AS date)
SET @Dag_Start = '07:00:00'
SET @Dag_Stopp = '15:42:00'
SET @Rast1_Start = '08:48:00' -- FRUKOST
SET @Rast1_Stopp = '09:00:00' -- FRUKOST
SET @Rast2_Start = '10:30:00' -- FÖRMIDDAGSRAST
SET @Rast2_Stopp = '11:00:00' -- FÖRMIDDAGSRAST
SET @Rast3_Start = '12:30:00' -- LUNCH
SET @Rast3_Stopp = '13:00:00' -- LUNCH
SET @Rast4_Start = '14:30:00' -- EFTERMIDDAGSRAST
SET @Rast4_Stopp = '15:00:00' -- EFTERMIDDAGSRAST


WHILE (@Datum <= GETDATE()+10)
BEGIN
SET @Loop_Dagar = @Loop_Dagar + 1
SET @Datum = @Datum + 1

-- FRUKOST
INSERT #tbl_Kalender VALUES (
(SELECT ISNULL(MAX(Kal_ID),0)+1 FROM #tbl_Kalender) ,@Datum,CAST(CONVERT(CHAR(10),@Datum,120) + ' ' + CONVERT(CHAR(8),CAST(@Dag_Start AS time)) AS datetime),CAST(CONVERT(CHAR(10),@Datum,120) + ' ' + CONVERT(CHAR(8),CAST(@Dag_Stopp AS time)) AS datetime),
Datediff(minute,@Dag_Start,@Dag_Stopp),CASE WHEN (SELECT DATEPART(WEEKDAY, @Datum)) > 5 THEN 1 ELSE 0 END,CAST(CONVERT(CHAR(10),@Datum,120) + ' ' + CONVERT(CHAR(8),CAST(@Rast1_Start AS time)) AS datetime),
CAST(CONVERT(CHAR(10),@Datum,120) + ' ' + CONVERT(CHAR(8),CAST(@Rast1_Stopp AS time)) AS datetime),Datediff(minute,@Rast1_Start,@Rast1_Stopp),'Frukost');

-- FÖRMIDDAGSRAST
INSERT #tbl_Kalender VALUES (
(SELECT ISNULL(MAX(Kal_ID),0)+1 FROM #tbl_Kalender) ,@Datum,CAST(CONVERT(CHAR(10),@Datum,120) + ' ' + CONVERT(CHAR(8),CAST(@Dag_Start AS time)) AS datetime),CAST(CONVERT(CHAR(10),@Datum,120) + ' ' + CONVERT(CHAR(8),CAST(@Dag_Stopp AS time)) AS datetime),
Datediff(minute,@Dag_Start,@Dag_Stopp),CASE WHEN (SELECT DATEPART(WEEKDAY, @Datum)) > 5 THEN 1 ELSE 0 END,CAST(CONVERT(CHAR(10),@Datum,120) + ' ' + CONVERT(CHAR(8),CAST(@Rast2_Start AS time)) AS datetime),
CAST(CONVERT(CHAR(10),@Datum,120) + ' ' + CONVERT(CHAR(8),CAST(@Rast2_Stopp AS time)) AS datetime),Datediff(minute,@Rast2_Start,@Rast2_Stopp),'Förmiddagsrast');

-- Lunch
INSERT #tbl_Kalender VALUES (
(SELECT ISNULL(MAX(Kal_ID),0)+1 FROM #tbl_Kalender) ,@Datum,CAST(CONVERT(CHAR(10),@Datum,120) + ' ' + CONVERT(CHAR(8),CAST(@Dag_Start AS time)) AS datetime),CAST(CONVERT(CHAR(10),@Datum,120) + ' ' + CONVERT(CHAR(8),CAST(@Dag_Stopp AS time)) AS datetime),
Datediff(minute,@Dag_Start,@Dag_Stopp),CASE WHEN (SELECT DATEPART(WEEKDAY, @Datum)) > 5 THEN 1 ELSE 0 END,CAST(CONVERT(CHAR(10),@Datum,120) + ' ' + CONVERT(CHAR(8),CAST(@Rast3_Start AS time)) AS datetime),
CAST(CONVERT(CHAR(10),@Datum,120) + ' ' + CONVERT(CHAR(8),CAST(@Rast3_Stopp AS time)) AS datetime),Datediff(minute,@Rast3_Start,@Rast3_Stopp),'Lunch');

-- Eftermiddagsrast
INSERT #tbl_Kalender VALUES (
(SELECT ISNULL(MAX(Kal_ID),0)+1 FROM #tbl_Kalender) ,@Datum,CAST(CONVERT(CHAR(10),@Datum,120) + ' ' + CONVERT(CHAR(8),CAST(@Dag_Start AS time)) AS datetime),CAST(CONVERT(CHAR(10),@Datum,120) + ' ' + CONVERT(CHAR(8),CAST(@Dag_Stopp AS time)) AS datetime),
Datediff(minute,@Dag_Start,@Dag_Stopp),CASE WHEN (SELECT DATEPART(WEEKDAY, @Datum)) > 5 THEN 1 ELSE 0 END,CAST(CONVERT(CHAR(10),@Datum,120) + ' ' + CONVERT(CHAR(8),CAST(@Rast4_Start AS time)) AS datetime),
CAST(CONVERT(CHAR(10),@Datum,120) + ' ' + CONVERT(CHAR(8),CAST(@Rast4_Stopp AS time)) AS datetime),Datediff(minute,@Rast4_Start,@Rast4_Stopp),'Eftermiddagsrast');

END;
GO

En ny tabell för objekten.

DECLARE @Object1_Start datetime
DECLARE @Object1_Stopp datetime
DECLARE @Object2_Start datetime
DECLARE @Object2_Stopp datetime
DECLARE @Object3_Start datetime
DECLARE @Object3_Stopp datetime
DECLARE @Object4_Start datetime
DECLARE @Object4_Stopp datetime

SET @Object1_Start = '2013-11-22 14:21'
SET @Object1_Stopp = '2013-11-25 09:16'
SET @Object2_Start = '2013-11-20 07:30'
SET @Object2_Stopp = '2013-11-20 11:40'
SET @Object3_Start = '2013-11-18 08:10'
SET @Object3_Stopp = '2013-11-18 13:45'
SET @Object4_Start = '2013-11-21 09:34'
SET @Object4_Stopp = '2013-11-22 15:48'

--SKAPA OBJEKT TABELL
CREATE TABLE #tbl_Objekt(Obj_Namn varchar(20),Obj_Start datetime, Obj_Stopp datetime,Obj_Dagar int,Obj_Brutto_Minuter int, Obj_Netto_Minuter int, Obj_Over_Natt int, Obj_Over_Helg int);

-- LÄGGER IN DE 4 OBJEKT SOM SKA FÅ TILLDELAT "NETTO MINUTER"
INSERT #tbl_Objekt VALUES ('Objekt 1', @Object1_Start, @Object1_Stopp, Datediff(day, @Object1_Start, @Object1_Stopp), Datediff(minute, @Object1_Start, @Object1_Stopp), NULL, CASE WHEN DATEDIFF(DAY,@Object1_Start,@Object1_Stopp) > 0 THEN 1 ELSE 0 END, CASE WHEN DATEPART(WEEK,@Object1_Start) <> DATEPART(WEEK,@Object1_Stopp) THEN 1 ELSE 0 END );
INSERT #tbl_Objekt VALUES ('Objekt 2', @Object2_Start, @Object2_Stopp, Datediff(day, @Object2_Start, @Object2_Stopp), Datediff(minute, @Object2_Start, @Object2_Stopp), NULL, CASE WHEN DATEDIFF(DAY,@Object2_Start,@Object2_Stopp) > 0 THEN 1 ELSE 0 END, CASE WHEN DATEPART(WEEK,@Object2_Start) <> DATEPART(WEEK,@Object2_Stopp) THEN 1 ELSE 0 END );
INSERT #tbl_Objekt VALUES ('Objekt 3', @Object3_Start, @Object3_Stopp, Datediff(day, @Object3_Start, @Object3_Stopp), Datediff(minute, @Object3_Start, @Object3_Stopp), NULL, CASE WHEN DATEDIFF(DAY,@Object3_Start,@Object3_Stopp) > 0 THEN 1 ELSE 0 END, CASE WHEN DATEPART(WEEK,@Object3_Start) <> DATEPART(WEEK,@Object3_Stopp) THEN 1 ELSE 0 END );
INSERT #tbl_Objekt VALUES ('Objekt 4', @Object4_Start, @Object4_Stopp, Datediff(day, @Object4_Start, @Object4_Stopp), Datediff(minute, @Object4_Start, @Object4_Stopp), NULL, CASE WHEN DATEDIFF(DAY,@Object4_Start,@Object4_Stopp) > 0 THEN 1 ELSE 0 END, CASE WHEN DATEPART(WEEK,@Object4_Start) <> DATEPART(WEEK,@Object4_Stopp) THEN 1 ELSE 0 END );
 
 
 
 
 

 

Redigerad av KTZ

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Tack för visat intresse!

Jag löste det genom att skapa en lååång tabell med minut-perioder. flaggade dessa med 1/0 för arbetstid (1) eller inte (0) Därefter skapade jag en select som summerade antal 1:or i kolumnen arbetstid mellan två två tidpunkterna.

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Skapa ett konto eller logga in för att kommentera

Du måste vara medlem för att kunna kommentera

Skapa ett konto

Skapa ett nytt konto på vårt forum. Det är lätt!

Registrera ett nytt konto

Logga in

Redan medlem? Logga in här.

Logga in nu



×
×
  • Skapa nytt...