19. juni 2012 - 10:09Der er
3 kommentarer og 1 løsning
SQL beregne tid (virker men regner forkert)
Hej.
Jeg har en tabel med tids registreringer.
jeg kører denne SQL(forespørgsel) på den.
SELECT Format(b.PanelDate,"dd/mm-yyyy") AS dato, Round(24*Sum((e.PanelDate-b.PanelDate)),2) AS Total, IIf([Total]>8,Round(([Total]-8),2),[Total]=Null) AS Overtid, IIf(Weekday([dato],2)=6 Or Weekday([dato],2)=7,[Total],Null) AS Weekend
FROM Registreringer AS b, Registreringer AS e
WHERE (((e.Paneldate)=(select min(Paneldate) from Registreringer where G=True and PanelDate >b.PanelDate)) AND ((b.K)= True))
GROUP BY Format(b.PanelDate,"dd/mm-yyyy");
Problemet er at den regner forkert. "Round(24*Sum((e.PanelDate-b.PanelDate)),2) AS Total"
hvis eks. e.Paneldate="21-01-2012 08:37:43" b.Paneldate="21-01-2012 11:15:58" Bliver Total="2,64"
SELECT Format(b.PanelDate,"dd/mm-yyyy") AS dato, format(sum(e.PanelDate-b.PanelDate),"hh:mm:ss") AS Total, IIf([Total]>"08:00:00", format(DATEADD("h",-8,[Total]),"hh:mm:ss") ,[Total]=Null) AS Overtid, IIf(Weekday([dato],2)=6 Or Weekday([dato],2)=7,[Total],Null) AS Weekend FROM Registreringer AS b, Registreringer AS e WHERE (((e.Paneldate)=(select min(Paneldate) from Registreringer where G=True and PanelDate >b.PanelDate)) AND ((b.K)= True)) GROUP BY Format(b.PanelDate,"dd/mm-yyyy");
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
