Jeg vil gerne have timetallet for medarbejderne, for 2 perioder bagud. Hvis en medarbejder f.eks. har holdt 2 uges ferie (dækkende hele perioden), skal den periode ikke tages med men den foregående periode. Ovenstående skulle gerne resultere i at jeg får timetallet for medarbejder 4000 for datoerne 01-05-05 og 29-05-05, for 5000 for datoerne 01-05-05 og 15-05-05 samt for 6000 datoerne 15-05-05 og 29-05-05
Denne side indeholder artikler med forskellige perspektiver på Identity & Access Management i private og offentlige organisationer. Artiklerne behandler aktuelle IAM-emner og leveres af producenter, rådgivere og implementeringspartnere.
Sure can, Terry. Not with the "lowest" dates, but the newest dates. the dates are always sunday, in a 2 week period. Sunday 2. of January, sunday 16. of January, sunday 30. of January and so on. medarbnr means employee no. (guess you know that) The only way we can see that an employee has been on holiday, is that there are NO records on a particular date. The date represents a payment day. I presented a demo for my boss (it's his assignment, but I offered to help) in which I use a pivottable, but since I was unable to built-in the above logic into the query, I chose to present data for 6 periods, which isn't what he wanted. Just the 2 newest periods pr. employee.
Just need to check something. (Not with the "lowest" dates, but the newest dates.)!!!
Two newsest dates for 5000 are 15-05-05 and 29-05-05, but you say in th eoriginal question (for 5000 for datoerne 01-05-05 og 15-05-05) which are the two oldest (lowest)!!!
SELECT T1.MedarbNr, T1.dato, T1.TimeTal FROM tbltimetal AS T1 WHERE ((((Select Count (*) from tbltimetal Where [dato] > [T1].[dato] AND MedarbNr = t1.MedarbNr)+1) In (1,2))) ORDER BY T1.MedarbNr, T1.dato;
Not sure if you understand the SQL but what the SUB Select does is makes a new column to sort the records, and because I only want the newest 2 then it must be IN(1,2) if I wanted the first 3 the just change it to IN(1,2,3)
Can you elaborate the SQL a bit? I also have a field, Pay, for every record in the table and I would like the SQL to calculate an average kr/hour Dato MedArbNr Timetal Pay 01-05-05 4000 34 5000 15-05-05 4000 36 5200
Total of Pay divided by Total of Timetal (5000+5200) /(34+36)
Well, not quite, I still need it to calculate this:
I also have a field, Pay, for every record in the table and I would like the SQL to calculate an average kr/hour Dato MedArbNr Timetal Pay 01-05-05 4000 34 5000 15-05-05 4000 36 5200
Total of Pay divided by Total of Timetal (5000+5200) /(34+36)
the easiest way is to make a query which takes the data from the first query
SELECT qry.MedarbNr, Sum(Pay) / Sum(TimeTal) AS Tot FROM qry GROUP BY qry.MedarbNr;
Synes godt om
Ny brugerNybegynder
Din løsning...
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.
