Hoe kan ik deze tijden optellen

Status
Niet open voor verdere reacties.
M

movedown

Gebruiker
Lid geworden
4 aug 2009
Berichten
5
Ik heb een database met tijden die opgeteld zouden moeten worden op dagbasis.
per persoon moeten dus de tijden berekend worden.
iedere in en uit boeking heeft een eigen regel
er zijn 2 verschillende mensen in deze database namelijk medewerkers en bezoekers.
deze filter ik met het eerste stuk in de code.
dus in de #tempdb staan alleen de medewerkers

voorbeeld uit #tempdb
voorbeeld database.jpg

hier is de code die ik nu heb.
drop table #tempdb
select imt, dbo.view_eventlog_online.lastname, dept_firm, timestamp, entrancename, eventtype, dbo.view_person.type
into #tempdb
from dbo.view_eventlog_online
inner join dbo.view_person
on dbo.view_eventlog_online.lastname=dbo.view_person.lastname
where entrancename like 'cp1_g003_salto%'
and eventtype <>'40'
and dbo.view_eventlog_online.lastname is not null
and type='M'


SELECT distinct L1.timestamp AS date_in, L1.lastname AS naam, L1.imt AS voornaam, L1.entrancename, L1.dept_firm AS afdeling, L1.type, L2.timestamp AS date_out, L3.entrancename
FROM #tempdb AS L1, #tempdb AS L2, #tempdb as L3
WHERE L1.timestamp<L2.timestamp AND L1.lastname=L2.lastname AND L1.imt=L2.imt AND L1.entrancename like 'CP1_G003_Salto_TNK_In'
AND L1.dept_firm=L2.dept_firm AND L2.entrancename like 'CP1_G003_salto_TNK_Uit'
AND L3.entrancename like'CP1_G003_salto%_Uit'
AND L1.lastname=L3.lastname AND L1.imt=L3.imt AND L1.dept_firm=L3.dept_firm
AND L3.timestamp<L2.timestamp AND L3.timestamp>=L1.timestamp
 

Bijlagen

  • voorbeeld database v2.jpg
    voorbeeld database v2.jpg
    21,3 KB · Weergaven: 48
Laatst bewerkt:
Ik weet niet precies wat je wilt maar ik heb een test uitgevoerd op je "voorbeeld_database v2.jpg"

stap 1: testtabel aanmaken
[SQL]
IF OBJECT_ID(N'tempdb..#test',N'U') IS NOT NULL
DROP TABLE #test

CREATE TABLE #test
(
DatumIn DATETIME
,Onbekend VARCHAR(30)
-- CONSTRAINT DF_TEMPDB_onbekend DEFAULT ''
,Naam VARCHAR(20)
,Omschrijving1 VARCHAR(40)
,Afdeling VARCHAR(30)
,PersoonType VARCHAR(10)
,DatumUit DATETIME
,Omschrijving2 VARCHAR(40)
)
[/SQL]

stap2: vullen met test data
[SQL]
INSERT INTO #test
VALUES('2010-11-18 09:54:19.887','','Theo','CP1_G003_Salto_TNK','Maintenance','M','2010-11-19 15:46:41.253','CP1_G003_Salto_TNK_UIT')
INSERT INTO #test
VALUES('2010-11-18 09:55:00.000','','Kristel','CP1_G003_Salto_TNK','Finance', 'M','2010-11-19 14:42:52.160','CP1_G003_Salto_TNK_UIT')
INSERT INTO #test
VALUES('2010-11-18 09:55:00.000','','Kristel','CP1_G003_Salto_TNK','Finance', 'M','2010-11-22 15:00:23.763','CP1_G003_Salto_TNK_UIT')
[/SQL]

stap3: query uren optellen dmv het verschil uitrekenen tussen datum IN en datum UIT
(in het voorbeeld per maand)
[SQL]
SELECT Naam,
DATENAME(MONTH,DatumIn) AS Maand,
SUM(DATEDIFF(HOUR,0,DatumUit-DatumIn)) AS Uren,
SUM(DATEDIFF(MINUTE,0,DatumUit-DatumIn) % 60) AS Minuten,
SUM(DATEDIFF(SECOND,0,DatumUit-DatumIn) % 60) AS Seconden
FROM #test
GROUP BY Naam,
DATENAME(MONTH,DatumIn)
[/SQL]

bedoel je zoiets? of kun je iets duidelijker zijn wat je precies uitrekent.
 
Hey Markxl,

ik haal met het eerste stuk sql uit een grote database met alle evenementen de klokkingen van alle gebruikers die of medewerker zijn of bezoeker.(dit gebeurd door het type M of type B in het voorbeeld dus de medewerker)

dan heb ik van de hele week alle boekingen in en uit.
nu wil ik de juiste bij elkaar zoeken en dan per dag het totaal gewerkte uren per persoon berekenen.(een soort urenlijst per dag per persoon)

dit is dan weer voor de gewerkte uren uit te rekenen tot bijvoorbeeld het volgende ongeval. (hoeveel uur zijn er gewerkt tussen 2 ongevallen)

Ik hoop dat dit wat duidelijk maakt

groet,

movedown.
 
Laatst bewerkt:
Movedown,

Een paar vragen:

Komt het voor dat op één dag een medewerker meerdere keren in je bron database voorkomt? moet ik daar ook rekening mee houden?

Wat houdt eventtype in?

Bekijk het resultaat van deze query eens. is dit wat je ongeveer wilt zien?
[SQL]
SELECT imt,
VW_P.lastname,
dept_firm,
MIN(timestamp) AS aankomst,
MAX(timestamp) AS vertrek,
CAST(CAST(MAX(timestamp) - MIN(timestamp) AS TIME) AS VARCHAR(8)) AS [hh:mm:ss],
eventtype,
VW_P.type AS Persontype
FROM dbo.view_eventlog_online VW_EO
INNER JOIN dbo.view_person VW_P on VW_EO.lastname=VW_P.lastname
WHERE entrancename like 'cp1_g003_salto%'
AND eventtype <>'40'
AND VW_EO.lastname IS NOT NULL
AND VW_P.type='M'
GROUP BY DAY(timestamp),
imt,
VW_P.lastname,
dept_firm,
eventtype,
VW_P.type[/SQL]

voeg eventueeel een order by clausule in
[SQL]ORDER BY dept_firm,
VW_P.lastname,
imt,[/SQL]
 
Laatst bewerkt:
Hey mark Xl,

allereerst bedankt voor je snelle reacties en hulp.
dit komt zeker in de buurt.

op je eerste vraag, dat is inderdaad mogelijk men kan in en uit gaan diverse keren per dag.
bijvoorbeeld de rokers zij gaan buiten de deur en deze tijd wordt niet meegerekend.

het eventtype is in de database omdat er verschillende registraties zijn.

alles boven 40 zijn voor andere deuren of locaties en of bezoekers.

zoals je ziet werkt je voorbeeld alleen zit er een groot verschil in de in en uit boekings datum. Deze zouden normaal gezien op dezelfde dag moeten zijn behalve bij de nacht dienst.
ik probeer het plaatje van het resultaat er weer bij te plakken.

groet,

movedown.
voorbeeld database v3.jpg
 
Wat ik nu heb gedaan is alle tijden per medewerker per dag bij elkaar opgeteld.
Dat doe ik door alle inklok tijden negatief te nemen, en daarbij alle uitkloktijden op te tellen.
bijvoorbeeld:
Jan klokt in 8 uur, klokt uit, 10 uur(roken), klokt in 10:30, klokt uit 16 uur
berekening : -8 + 10 -10,5 + 16 = 7,5 uur

De type conversie van datestamp naar float, naar datetime, naar time, naar varchar ziet er ingewikkeld uit, maar wat er opgeteld wordt is de som van alle in/uitkloktijden per medewerker.

ziet dit er al beter uit?
probeer sowieso altijd een paar handmatig te controleren

Mijn group by statement was overigens fout, die heb ik nu ook aangepast

[SQL]
SELECT imt,
VW_P.lastname,
dept_firm,
MIN(timestamp) AS aankomst,
MAX(timestamp) AS vertrek,
CAST(
CAST(
CAST(
SUM(
CASE WHEN RIGHT(entrancename,3) = 'UIT' THEN
cast(timestamp AS FLOAT)
WHEN RIGHT(entrancename,3) = '_IN' THEN
0 - cast(timestamp as float)
ELSE
0
END
)
AS DATETIME)
AS TIME)
AS VARCHAR(8)) AS [hh:mm:ss]
eventtype,
VW_P.type AS Persontype
FROM dbo.view_eventlog_online VW_EO
INNER JOIN dbo.view_person VW_P on VW_EO.lastname=VW_P.lastname
WHERE entrancename like 'cp1_g003_salto%'
AND eventtype <>'40'
AND VW_EO.lastname IS NOT NULL
AND VW_P.type='M'
GROUP BY DATEDIFF(D,0,timestamp)
imt,
VW_P.lastname,
dept_firm,
eventtype,
VW_P.type
ORDER BY dept_firm,
VW_P.lastname,
imt
[/SQL]
 
Hey Mark XL,

dit komt er super dichtbij en ik probeer de code ook goed te lezen en te begrijpen.
steeds leer je weer een stukje bij.

ik krijg een stuk en dat zie er goed uit :thumb: :thumb:
de klokkingen uit het voorbeeld heb ik gecontroleerd en deze zijn zo als het in de database staat.
voorbeeld database v4.jpg

maar dan krijg ik de onderstaande melding
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

zouden we dat kunnen voorkomen als we specifieke datums in geven ??
groet,

movedown
 
Laatst bewerkt:
verander de code in vanaf FROM

de reden dat het fout gaat heeft te maken met ontbrekende kloktijden.
als je deze query draait krijg je alleen alle overzichten waar in en uitgeklokt is op dezelfde dag. rijen waar maar 1x per dag geregistreerd wordt worden niet meegenomen (de tijd kan ook niet worden uitgerekend voor die dagen)

vervang vanaf from in de bovenstaande query de tekst met het volgende:

[SQL]
FROM dbo.view_eventlog_online VW_EO
INNER JOIN dbo.view_person VW_P on VW_EO.lastname=VW_P.lastname
WHERE entrancename like 'cp1_g003_salto%'
AND eventtype <>'40'
AND VW_EO.lastname IS NOT NULL
AND VW_P.type='M'
GROUP BY DATEDIFF(D,0,timestamp)
imt,
VW_P.lastname,
dept_firm,
eventtype,
VW_P.type
HAVING SUM(CASE WHEN RIGHT(entrancename,3) = 'UIT' THEN
cast(timestamp AS FLOAT)
WHEN RIGHT(entrancename,3) = '_IN' THEN
0 - cast(timestamp as float)
ELSE
0
END) BETWEEN 0 AND 1
ORDER BY dept_firm,
VW_P.lastname,
imt
[/SQL]
 
Hey Mark XL

Dit is echt super, ik ben al dagen aan het puzzelen.

Ik ga hier nog verder mee om ook te proberen de nachtdiensten te pakken.
deze starten op een ene dag en eindigen op de andere.

dat wordt nog een uitdaging maar je hebt me weer nieuwe inzichten gegeven.
toch nog een extra cursus of veel meer met SQL aan de gang zijn.

super bedankt voor al je hulp.

groet,

movedown
:thumb::thumb::thumb::thumb::thumb:
 
Graag gedaan movedown Ik ben blij dat je er wat aan hebt!

wellicht heb je oook wat aan de test tabellen die ik heb gebruikt
(tip: bij complexe opdrachten is het aan te raden altijd even een of meerdere kleine testtabellen aan te maken.)

ik mag geen sql bestanden uploaden dus dan maar even in een codeblock
[SQL]
USE tempdb
/**********************************************
Testscript voor optellen data (datums)
(C) Mark Heuckeroth 2010
**********************************************/
SET NOCOUNT ON

-- als de tijdelijke tabellen bestaan, deze verwijderen
IF OBJECT_ID(N'tempdb..#Test',N'U') IS NOT NULL DROP TABLE #Test
IF OBJECT_ID(N'tempdb..#MinMaxWaarden',N'U') IS NOT NULL DROP TABLE #MinMaxWaarden

-- test tabel "personen"
CREATE TABLE #Test
(
TestID INT IDENTITY(1,1),
Omschrijving VARCHAR(10)
)
-- gegevens invoegen
INSERT INTO #Test VALUES('Persoon 1')
INSERT INTO #Test VALUES('Persoon 2')

-- test tabel "kloktijden"
CREATE TABLE #MinMaxWaarden
(
TestID INT,
Waarde DATETIME,
logstatus VARCHAR(3)
)
-- test data invoeren
INSERT INTO #MinMaxWaarden(TestID,Waarde,logstatus) VALUES(1,'2010-11-25 08:00:00.000','IN')
INSERT INTO #MinMaxWaarden(TestID,Waarde,logstatus) VALUES(1,'2010-11-25 09:30:00.000','UIT')
INSERT INTO #MinMaxWaarden(TestID,Waarde,logstatus) VALUES(1,'2010-11-25 10:00:00.000','IN')
INSERT INTO #MinMaxWaarden(TestID,Waarde,logstatus) VALUES(1,'2010-11-25 12:00:00.000','UIT')
INSERT INTO #MinMaxWaarden(TestID,Waarde,logstatus) VALUES(2,'2010-11-25 08:30:00.000','IN')
INSERT INTO #MinMaxWaarden(TestID,Waarde,logstatus) VALUES(2,'2010-11-25 12:15:00.000','UIT')
INSERT INTO #MinMaxWaarden(TestID,Waarde,logstatus) VALUES(2,'2010-11-25 13:12:00.000','IN')
INSERT INTO #MinMaxWaarden(TestID,Waarde,logstatus) VALUES(2,'2010-11-25 17:55:00.000','UIT')

-- de query
SELECT T.Omschrijving,
-- aantal dagen verschil tussen 0 (='31-12-1899 00:00:00.000') en #minmaxwaarden.datumwaarde
DATEDIFF(D,0,w.waarde) AS DatumID,
CAST(
CAST(
CAST(
SUM(
CASE WHEN logstatus = 'UIT' THEN
cast(w.waarde AS FLOAT)
WHEN logstatus = 'IN' THEN
- cast(w.waarde AS FLOAT) --Inkloktijden positief
ELSE
0
END
)
AS DATETIME)
AS TIME)
AS VARCHAR(8)) AS [hh:mm:ss]
FROM #Test T
INNER JOIN #MinMaxWaarden W ON W.TestID=T.TestID
GROUP BY DATEDIFF(D,0,w.waarde),
T.Omschrijving
HAVING SUM(CASE WHEN logstatus = 'UIT' THEN
CAST(w.waarde AS FLOAT)
WHEN logstatus = 'IN' THEN
- CAST(w.waarde AS FLOAT)
ELSE
0
END) BETWEEN 0 AND 1 -- 0 = geen verschil. 1 = 24 uur (meer dan een dag, dat is fout.)
ORDER BY T.Omschrijving

-- tijdelijke tabellen verwijderen
DROP TABLE #Test
DROP TABLE #MinMaxWaarden
[/SQL]
 
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan