All pastes #199493 Raw Edit

Miscellany

public text v1 · immutable
#199493 ·published 2006-10-12 15:19 UTC
rendered paste body
"SELECT"
        . " InOutTime.personid AS personid"
        . ", Person.name AS name"
        . ", Person.department AS department"
        . ", MONTHNAME(InOutTime.recorddate) AS month"
        . ", MONTH(InOutTime.recorddate) AS monthnumber"

        . ", SUM( "
            ."IF ((InOutTime.indatetime='1001-01-01 00:00:00' OR InOutTime.outdatetime='1001-01-01 00:00:00'), "
                ."0, "
                ."(UNIX_TIMESTAMP(InOutTime.outdatetime) - UNIX_TIMESTAMP(InOutTime.indatetime)- (InOutTime.breakminutes * 60)+(InOutTime.extraminutes * 60)"
                .")"
           .")) AS totalseconds "

        . ", ROUND(SUM( IF ((InOutTime.indatetime='1001-01-01 00:00:00' OR InOutTime.outdatetime='1001-01-01 00:00:00'), (InOutTime.extraminutes*60), (UNIX_TIMESTAMP(InOutTime.outdatetime) - UNIX_TIMESTAMP(InOutTime.indatetime)- (InOutTime.breakminutes * 60)+(InOutTime.extraminutes * 60)))) / 3600, 1) AS totalhours "

        . ", ROUND(((SUM( IF ((InOutTime.indatetime='1001-01-01 00:00:00' OR InOutTime.outdatetime='1001-01-01 00:00:00'), (InOutTime.extraminutes*60), (UNIX_TIMESTAMP(InOutTime.outdatetime) - UNIX_TIMESTAMP(InOutTime.indatetime)- (InOutTime.breakminutes * 60)+(InOutTime.extraminutes * 60)))) / 3600)"
        . "/"
        . " (SUM(IF(WEEKDAY(InOutTime.recorddate)>4, 0, DayTypeValues.multiplier )))), 1) AS dailyaverage "

        . ", ROUND((((SUM( IF ((InOutTime.indatetime='1001-01-01 00:00:00' OR InOutTime.outdatetime='1001-01-01 00:00:00'), (InOutTime.extraminutes*60), (UNIX_TIMESTAMP(InOutTime.outdatetime) - UNIX_TIMESTAMP(InOutTime.indatetime)- (InOutTime.breakminutes * 60)+(InOutTime.extraminutes * 60)))) / 3600)"
        . "/"
        . " (SUM(IF(WEEKDAY(InOutTime.recorddate)>4, 0, DayTypeValues.multiplier ))))*5), 1) AS weeklyaverage "

        . " FROM InOutTime, Person, DayTypeValues";