Tidsberegninger udfra input i en form.

Er starttid og sluttid en hel dato eller er det bare et tidspunkt. Hvis det er et tidspunkt bliver det mere kompliceret at trække data ud.

Anyway! Første hug kunne være:

SELECT SUM(SLUTTID - STARTTID) AS TID, BRUGERNAVN
FROM  <tabel med vagttider>
WHERE  UGE = '<det ugenr du vil have timer for>'
  AND  BRUGERNAVN = '<den bruger du vil have tidsforbrug for>'

eller dem allesammen

SELECT SUM(SLUTTID - STARTTID) AS TID, BRUGERNAVN
FROM  <tabel med vagttider>
WHERE  UGE = '<det ugenr du vil have timer for>'
GROUP BY BRUGERNAVN

udskift tekster i <> med de relevante navne...

Ja, jeg har siddet og kigget lidt på det nu. Jeg har lavet følgende kode :

<% sum = SLUTTID - STARTTID %>
<%=sum%>

Det virker også fint, men problemet er bare den måde mine data står på.

F.eks. hvis STARTTID er kl. 08 om morgenen, så står der : 08.00
Og er SLUTTID 16, så står der i min DB : 16.00

Når jeg trækker de to tal fra hinanden, så laver systemet følgende regne stykke :
1600 - 0800 = 800 Timer. Det er jo forkert. Kan man ikke formatere på en måde, så man kan angive at de første 2 tal er timer og de sidste 2 er min.

Så ville jeg vel kunne få den til at regne rigtigt ud? Eller????

Kan du ikke bare dividere med 100?

Problemerne kommer først når du har vagter der krydser midnat... :)

Desuden giver yderligere udfordringer hvis du angiver minutter fra 0-59, da dette så heller ikke kommer til at passe når man regner 1630-845 = 785...

OK! Her er lige et sindsygt (utestet!) forsøg på at klare det hele på SQL serveren:

SELECT SUM(SLUTTID - STARTTID) AS TIDSFORBRUG, BRUGERNAVN
FROM
(SELECT ((CAST(STARTTID AS INT)/100)*100)+(CAST(STARTTID AS INT)%100*1.66666) AS STARTTID1,
        (((CAST(SLUTTID AS INT)/100)*100)+(CAST(SLUTTID AS INT)%100*1.66666))+2400 AS SLUTTID1,
        BRUGERNAVN
FROM <tabel>
WHERE STARTTID > SLUTTID
UNION
SELECT ((CAST(STARTTID AS INT)/100)*100)+(CAST(STARTTID AS INT)%100*1.66666) AS STARTTID1,
        (((CAST(SLUTTID AS INT)/100)*100)+(CAST(SLUTTID AS INT)%100*1.66666)) AS SLUTTID1,
        BRUGERNAVN
FROM <tabel>
WHERE STARTTID <= SLUTTID)
WHERE UGE = <det ugenr du vil have timer for>
GROUP BY BRUGERNAVN


Så skal du bare trække tidsforbrug og brugernavn ud af resultatet...

Prøv det evt. af i QueryAnalyzer først.

Typisk! Så får man lige sneget en fejl ind! Kolonnenavnene har jo ændret sig i subquery, de hedder naturligvis STARTTID1 og SLUTTID1:

SELECT SUM(SLUTTID1 - STARTTID1) AS TIDSFORBRUG, BRUGERNAVN
FROM
(SELECT ((CAST(STARTTID AS INT)/100)*100)+(CAST(STARTTID AS INT)%100*1.66666) AS STARTTID1,
        (((CAST(SLUTTID AS INT)/100)*100)+(CAST(SLUTTID AS INT)%100*1.66666))+2400 AS SLUTTID1,
        BRUGERNAVN
FROM <tabel>
WHERE STARTTID > SLUTTID
UNION
SELECT ((CAST(STARTTID AS INT)/100)*100)+(CAST(STARTTID AS INT)%100*1.66666) AS STARTTID1,
        (((CAST(SLUTTID AS INT)/100)*100)+(CAST(SLUTTID AS INT)%100*1.66666)) AS SLUTTID1,
        BRUGERNAVN
FROM <tabel>
WHERE STARTTID <= SLUTTID)
WHERE UGE = <det ugenr du vil have timer for>
GROUP BY BRUGERNAVN

Okay, det var godt nok en vild SQL sætning. Jeg forstår ikke halvdelen af den, men har lige prøvet.

Jeg har rettet lidt i kollone navnene, så de passer til hvad jeg faktisk havde kaldt dem. (Mig der skrev forkert i første indlæg)...

Det virker ikke helt.

SQL jeg har fyret af i min SQL Manager er :

SELECT SUM(SLUTTID1 - STARTTID1) AS TIDSFORBRUG, BRUGERNAVN
FROM
(SELECT ((CAST(STARTTIME AS INT)/100)*100)+(CAST(STARTTIME  AS INT)%100*1.66666) AS STARTTID1,
        (((CAST(ENDTIME AS INT)/100)*100)+(CAST(ENDTIME AS INT)%100*1.66666))+2400 AS SLUTTID1,
        BRUGERNAVN
FROM koldingvagtplan
WHERE STARTTIME  > ENDTIME
UNION
SELECT ((CAST(STARTTIME  AS INT)/100)*100)+(CAST(STARTTIME  AS INT)%100*1.66666) AS STARTTID1,
        (((CAST(ENDTIME AS INT)/100)*100)+(CAST(ENDTIME AS INT)%100*1.66666)) AS SLUTTID1,
        NAME
FROM koldingvagtplan
WHERE STARTTIME <= ENDTIME)
WHERE WEEK = '40'
GROUP BY BRUGERNAVN





Den giver følgende fejl :

Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'WHERE'.


Line 14 er ved : WHERE UGE = '40'

jeg er igang med at kigge på en forbedret version... håber den er der om 10 min :)

Rolig nu! Kl er over 2 om natten!.... Jeg smutter i seng nu.... vi snakkes ved senere.

go'nat

go'nat :)

hmmm... jeg tror nok denne fungerer (godt nok stadig med de gamle kolonnenavne, men dem kan du selv rette igen):

SELECT
  SUM(CAST(REPLACE(SLUTTID1,'.','') AS INT) - CAST(REPLACE(STARTTID1,'.','') AS INT)) AS TIDSFORBRUG,
  BRUGERNAVN,
FROM 
  ((SELECT (CAST(REPLACE(STARTTID,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(STARTTID,'.','') AS INT)%100*1.66666),0) AS INT) AS STARTTID1,
      ((CAST(REPLACE(SLUTTID,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(SLUTTID,'.','') AS INT)%100*1.66666),0) AS INT)+2400) AS SLUTTID1,
        BRUGERNAVN,
            UGE
    FROM vagtplan
    WHERE STARTTID > SLUTTID
      AND UGE = '5')
    UNION
  (SELECT (CAST(REPLACE(STARTTID,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(STARTTID,'.','') AS INT)%100*1.66666),0) AS INT) AS STARTTID1,
          (CAST(REPLACE(SLUTTID,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(SLUTTID,'.','') AS INT)%100*1.66666),0) AS INT) AS SLUTTID1,
        BRUGERNAVN,
            UGE
    FROM vagtplan
    WHERE STARTTID <= SLUTTID
      AND UGE = '5')) v1
GROUP BY
  BRUGERNAVN

I bund og grund handler en stor del af den SQL om at konvertere dine tidspunkt og håndtere overskridelse af midnat (den kan dog ikke håndtere arbejdsdage længere end 24 timer!!!).

Mht. konvertering af tidspunkter, så sker der det at punktum fjernes og tallet konverteres til et heltal. Derefter dividerer jeg med 100 og ganger med 100 for at fjerne minutter. Til de hele timer lægger jeg så resten af divisionen (modolus) med 100 (dvs. minutterne), hvilken jeg ganger med 1.666 for at få minutterne regnet om til 1/100 i stedet for 1/60.

Jeg laver to resultater som jeg samler til et langt. Det første lægger 24 timer til sluttiden hvis den er mindre end starttiden (dem hvor vagten har krydset midnat). Det andet er dem hvor starttid er mindre eller lig med sluttid.

Ud af det summerer jeg så differencen mellem de to tider (som nu altid vil give et positivt tal!) og grupperer efter brugernavn, så den forbrugte tid er for den pågældende bruger. Dvs. alle brugere burde komme ud med deres forbrugte tid i den pågældende uge (i mit eksempel altså uge 5).

HUSK! at rette uge i begge subqueries, ellers får du nok nogle sære resultater :)

Ok. Jeg er ikke helt så hård til SQL som dig.

Jeg skal vist til at læse lidt op på de funktioner en SQL Server tilbyder.

Jeg vil helst ikke begynde at rode alt for meget rundt på diverse tegn, da jeg slet ikke er sikker på hvorfor de står som de gør.

Jeg har nu forsøgt mig igen. Dog stadig med fejl. Gør jeg noget forkert??? :
Her er koden som jeg bruger den :

--------------


SELECT
  SUM(CAST(REPLACE(SLUTTID1,'.','') AS INT) - CAST(REPLACE(STARTTID1,'.','') AS INT)) AS TIDSFORBRUG,
  NAME,
FROM KOLDINGVAGTPLAN
  ((SELECT (CAST(REPLACE(STARTTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(STARTTIME,'.','') AS INT)%100*1.66666),0) AS INT) AS STARTTID1,
      ((CAST(REPLACE(ENDTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(ENDTIME,'.','') AS INT)%100*1.66666),0) AS INT)+2400) AS SLUTTID1,
        NAME,
            WEEK
    FROM KOLDINGVAGTPLAN
    WHERE STARTTIME > ENDTIME
      AND UGE = '5')
    UNION
  (SELECT (CAST(REPLACE(STARTTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(STARTTIME,'.','') AS INT)%100*1.66666),0) AS INT) AS STARTTID1,
          (CAST(REPLACE(ENDTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(ENDTIME,'.','') AS INT)%100*1.66666),0) AS INT) AS SLUTTID1,
        NAME,
            WEEK
    FROM KOLDINGVAGTPLAN
    WHERE STARTTIME <= ENDTIME
      AND WEEK = '40')) v1
GROUP BY
  NAME


-----------

Fejl jeg får er følgende :

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'FROM'.
Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near 'v1'.

Du har et komma for meget i din feltliste i SELECT:

SELECT
  SUM(CAST(REPLACE(SLUTTID1,'.','') AS INT) - CAST(REPLACE(STARTTID1,'.','') AS INT)) AS TIDSFORBRUG,
  NAME,
FROM KOLDINGVAGTPLAN

skal være:

SELECT
  SUM(CAST(REPLACE(SLUTTID1,'.','') AS INT) - CAST(REPLACE(STARTTID1,'.','') AS INT)) AS TIDSFORBRUG,
  NAME
FROM KOLDINGVAGTPLAN

desuden refererer du til et felt som er fra min version af selecten, nemlig uge:

FROM KOLDINGVAGTPLAN
    WHERE STARTTIME > ENDTIME
      AND UGE = '5')

det skal naturligvis være WEEK.

Du skal i øvrigt også fjern tabelnavnet KOLDINGVAGTSKEMA efter den første FROM.

KOLDINGVAGTPLAN, sorry

Jeg når frem til at den skal se således ud:

SELECT
  SUM(CAST(REPLACE(SLUTTID1,'.','') AS INT) - CAST(REPLACE(STARTTID1,'.','') AS INT)) AS TIDSFORBRUG,
  NAME
FROM
  ((SELECT (CAST(REPLACE(STARTTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(STARTTIME,'.','') AS INT)%100*1.66666),0) AS INT) AS STARTTID1,
      ((CAST(REPLACE(ENDTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(ENDTIME,'.','') AS INT)%100*1.66666),0) AS INT)+2400) AS SLUTTID1,
        NAME,
            WEEK
    FROM KOLDINGVAGTPLAN
    WHERE STARTTIME > ENDTIME
      AND WEEK = '40')
    UNION
  (SELECT (CAST(REPLACE(STARTTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(STARTTIME,'.','') AS INT)%100*1.66666),0) AS INT) AS STARTTID1,
          (CAST(REPLACE(ENDTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(ENDTIME,'.','') AS INT)%100*1.66666),0) AS INT) AS SLUTTID1,
        NAME,
            WEEK
    FROM KOLDINGVAGTPLAN
    WHERE STARTTIME <= ENDTIME
      AND WEEK = '40')) v1
GROUP BY
  NAME

Ja, nu er det ved at ligne et eller andet. Men mit resultat bliver følgende output :

TIDSFORBRUG      NAME
250              Anders

Mine værdier er følgende :
STARTTIME = 15.30
ENDTIME = 18.00

Den skulle jo gerne skrive 2,3 eller hvad? Det er jo 2 Timer og 30 minutter.

Umiddebart ville jeg jo synes at et resultat der skriver 2,5 timer ville være mere normalt, nå man snakker timeforbrug. i det tilfælde behøver vi bare at dividere tidsforbrug med 100. Hvis du kan leve med det, så skal din SQL ændres en lille smule:

SELECT
  CAST(SUM(CAST(REPLACE(SLUTTID1,'.','') AS INT) - CAST(REPLACE(STARTTID1,'.','') AS INT)) AS TIDSFORBRUG,
  NAME
FROM
  ((SELECT (CAST(REPLACE(STARTTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(STARTTIME,'.','') AS INT)%100*1.66666),0) AS INT) AS STARTTID1,
      ((CAST(REPLACE(ENDTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(ENDTIME,'.','') AS INT)%100*1.66666),0) AS INT)+2400) AS SLUTTID1,
        NAME,
            WEEK
    FROM KOLDINGVAGTPLAN
    WHERE STARTTIME > ENDTIME
      AND WEEK = '40')
    UNION
  (SELECT (CAST(REPLACE(STARTTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(STARTTIME,'.','') AS INT)%100*1.66666),0) AS INT) AS STARTTID1,
          (CAST(REPLACE(ENDTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(ENDTIME,'.','') AS INT)%100*1.66666),0) AS INT) AS SLUTTID1,
        NAME,
            WEEK
    FROM KOLDINGVAGTPLAN
    WHERE STARTTIME <= ENDTIME
      AND WEEK = '40')) v1
GROUP BY
  NAME

ØV! For tidlig post :(

Sådan!

SELECT
  CAST(SUM(CAST(REPLACE(SLUTTID1,'.','') AS INT) - CAST(REPLACE(STARTTID1,'.','') AS INT)) AS FLOAT)/100 AS TIDSFORBRUG,
  NAME
FROM
  ((SELECT (CAST(REPLACE(STARTTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(STARTTIME,'.','') AS INT)%100*1.66666),0) AS INT) AS STARTTID1,
      ((CAST(REPLACE(ENDTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(ENDTIME,'.','') AS INT)%100*1.66666),0) AS INT)+2400) AS SLUTTID1,
        NAME,
            WEEK
    FROM KOLDINGVAGTPLAN
    WHERE STARTTIME > ENDTIME
      AND WEEK = '40')
    UNION
  (SELECT (CAST(REPLACE(STARTTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(STARTTIME,'.','') AS INT)%100*1.66666),0) AS INT) AS STARTTID1,
          (CAST(REPLACE(ENDTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(ENDTIME,'.','') AS INT)%100*1.66666),0) AS INT) AS SLUTTID1,
        NAME,
            WEEK
    FROM KOLDINGVAGTPLAN
    WHERE STARTTIME <= ENDTIME
      AND WEEK = '40')) v1
GROUP BY
  NAME

Hva' så? Kan du få det til at fungere?

jepper. det funger bare. også med vagter som overskrider midnat.

tak for hjælpen.
sender du et svar så du kan få nogle points!

Godt det fungerer, så har jeg også lært noget om SQL :)

Men lige en lille ting til. Ved du hvad man skal skrive i ASP for at kunne holde formatteringen af dit SQL script? Altså Linie skift osv. Ellers skal det hele jo stå på en linie og det vil jeg helst ikke!

Skal man ikke skrive noget i slutningen af hver linie???

Tænker du på formatteringen af selve SQL-sætningen? Hvis ja, så kan du bruge underscore tegnet. Således:

SQL = "SELECT " & _
  "CAST(SUM(CAST(REPLACE(SLUTTID1,'.','') AS INT) - CAST(REPLACE(STARTTID1,'.','') AS INT)) AS FLOAT)/100 AS TIDSFORBRUG, " & _
  "NAME " & _
  "FROM " & _
  "((SELECT (CAST(REPLACE(STARTTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(STARTTIME,'.','') AS INT)%100*1.66666),0) AS INT) AS STARTTID1, " & _
  "((CAST(REPLACE(ENDTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(ENDTIME,'.','') AS INT)%100*1.66666),0) AS INT)+2400) AS SLUTTID1, " & _
  "NAME, " & _
  "WEEK " & _
  "FROM KOLDINGVAGTPLAN " & _
  "WHERE STARTTIME > ENDTIME " & _
  "AND WEEK = '40') " & _
  "UNION " & _
  "(SELECT (CAST(REPLACE(STARTTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(STARTTIME,'.','') AS INT)%100*1.66666),0) AS INT) AS STARTTID1, " & _
  "(CAST(REPLACE(ENDTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(ENDTIME,'.','') AS INT)%100*1.66666),0) AS INT) AS SLUTTID1, " & _
  "NAME, " & _
  "WEEK " & _
  "FROM KOLDINGVAGTPLAN " & _
  "WHERE STARTTIME <= ENDTIME " & _
  "AND WEEK = '40')) v1 " & _
"GROUP BY " & _
  "NAME "

Vær dog opmærksom på at der nok er en begrænsning i hvormange linier du skal strække et statement over.

Grundlæggende vil jeg faktisk anbefale at du laver dette statement som en stored procedure, hvilket også vil have andre fordele (f.eks. hastighed, pga. SQL Servers mulighed for at planlægge forspørgslen, samt at den er oversat når den ligger som en SP).

Tak for point i øvrigt :)

Jeps. Var så lidt.

Har lige et spørgsmål til.

Nu har jeg selvfølgelig ikke fået formatteret SQL sætningen, så den står på en linie endnu.

Det virker rigtig godt, men der er en ting som irriterer mig.

F.eks :

En medarbejder er på arbejde både Mandag, Tirsdag og Onsdag.
Medarbejderen møder :

Mandag  : kl. 14.00 og går hjem kl. 16.00.
Tirsdag : kl. 13.00 og går hjem kl. 17.00.

Her tæller dit SQL Script fint sammen. RS("TIDSFORBRUG") bliver her udskrevet korrekt, altså : 6 Timer

Men hvis 2 dage er ens, lægger den ikke den sidste dag til.

F.eks. :
Medarbejderen møder :

Mandag  : kl. 14.00 og går hjem kl. 16.00.
Tirsdag : kl. 14.00 og går hjem kl. 16.00.

I dette tilfælde bliver rs("TIDSFORBRUG") sat til : 2
Jeg har også en kollone der hedder WEEKDAY. Jeg forstår ikke SQL sætningen godt nok til at jeg selv vil begynde at pille ved den, men kan man ikke bygge den kollone ind i også? Så vil det jo aldrig blive 2 ens entries.

forstår du hvad jeg mener?

ups... Medarbejderen er på arbejde Mandag og Tirsdag skulle der have stået

Weekday kollonen har data der hedder enten : Mandag eller Tirsdag eller Onsdag osv...

Det er iøvrigt kun hvis 2 vagter har samme starttidspunkt. Det gør ikke hvis sluttidspunktet er det samme.

Først og fremmest forstår jeg ikke helt hvorfor det skulle være et problem at man to dage møder og går hjem på samme tidspunkt - du ændrer vel ikke noget i den måde du registrerer data på i det tilfælde...(?)
Databasen betragter det bare som to rækker der skal summeres efter de angivne regler (og jeg kan ikke lige selv gennemskue hvor det skulle gå galt med start og sluttider på tværs af to dage).

Grunden til at weekday ikke kommer med er at forspørgslen kun finder tidsforbrug pr. ugenr. hvilket ikke lægger op til at de enkelte ugedage skal bruges til noget.

Kan du evt. vise et udsnit af relevante data som resulterer i fejlagtigt udtræk?

Jo, men for at beholde min stolthed, vil jeg lige rydde lidt op i min kode. Den er MEGET rodet, og det er vist kun mig selv der kan finde hoved og hale i den! Har du evt. en mail adresse hvor jeg kan sende dig den på?

jan (a) softspot.dk

OK! Så lærte jeg lige lidt mere om UNION. UNION tager åbenbart og samler enslydende rækker til en, hvilket gjorde sig gældende for to af rækkerne lørdag og søndag. Det man skal gøre er at skrive UNION ALL som resulterer i at alle rækker returneres selvom der måtte være sammenfald i data. Altså:

SELECT
  CAST(SUM(SLUTTID1 - STARTTID1) AS FLOAT)/100 AS TIDSFORBRUG,
  NAME
FROM
  ((SELECT (CAST(REPLACE(STARTTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(STARTTIME,'.','') AS INT)%100*1.66666),0) AS INT) AS STARTTID1,
      ((CAST(REPLACE(ENDTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(ENDTIME,'.','') AS INT)%100*1.66666),0) AS INT)+2400) AS SLUTTID1,
        NAME,
            WEEK
    FROM KOLDINGVAGTPLAN
    WHERE STARTTIME > ENDTIME
      AND WEEK = '40')
    UNION ALL
  (SELECT (CAST(REPLACE(STARTTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(STARTTIME,'.','') AS INT)%100*1.66666),0) AS INT) AS STARTTID1,
          (CAST(REPLACE(ENDTIME,'.','') AS INT)/100*100)+CAST(ROUND((CAST(REPLACE(ENDTIME,'.','') AS INT)%100*1.66666),0) AS INT) AS SLUTTID1,
        NAME,
            WEEK
    FROM KOLDINGVAGTPLAN
    WHERE STARTTIME <= ENDTIME
      AND WEEK = '40')) v1
GROUP BY
  NAME