-1

I'm working on Login/Logout System and I'm facing this problem regarding on SELECT Query. I have 2 tables namely dtr_tbl and shift_tbl. In dtr_tbl are the logs of the user(login/logout time details) and in shift_tbl are the list of shifts available for the users.

>> dtr_tbl

enter image description here

>> shift_tbl (please ignore dates on shifting_timeFr and shiftingtimeTo Columns, just focus on time)

enter image description here

What I wanted to do is compute the TotalHrs per day and per shifting_code. (see below my sample sql)

SELECT 
    user_id,
    CONVERT(varchar,log_date,101) AS [LogDate],
    MIN(d.login_time) AS [Login],
    MAX(d.logout_time) AS [Logout],
    d.shift_code,
    CASE WHEN MIN(d.login_time) <= -->> validate if `Login` time of user is valid within given `gracetime period` 
    CAST(
        CAST(CONVERT(DATE, -->> extract `Date` only from user's `login` time.
                        DATEADD( 
                                hh,
                                IIF(sh.shifting_LVtime=1,-15,0), -->> check if toggle for LVtime(just ignore this)
                                MIN(d.login_time) -->> current login time of user
                                )) AS DATETIME) + 
        CAST(CONVERT(TIME, -->> extract `Time` only from table `shift_tbl` on `shifting_timeFr` column
                        DATEADD( -->> add `15 minutes` for given `gracetime` period
                                MINUTE,
                                sh.shifting_gracetime, -->> 15 mins given `gracetime` period from shift_tbl
                                sh.shifting_timefr --> from `shift_tbl`
                                )) AS DATETIME) 
        AS DATETIME) THEN
    DATEDIFF(MINUTE,
                    CAST(CONVERT(
                                DATE,
                                MIN(d.login_time)
                                ) AS DATETIME) + 
                    CAST(CONVERT(
                                TIME,
                                sh.shifting_timefr
                                ) AS DATETIME)
            ,MAX(d.logout_time))/60.0
ELSE
    DATEDIFF(MINUTE,MIN(d.login_time),
                MAX(d.logout_time))/60.0 
END  AS [TotalHrs]
FROM tblDTR d
INNER JOIN tblShiftings sh
ON d.shift_code = sh.shifting_code
WHERE user_id = 'USER1' 
        AND d.log_date
            BETWEEN CAST('03/13/2015' AS datetime) 
                AND CAST('03/23/2015' AS datetime)
GROUP BY 
    d.user_id,
    d.shift_code,
    sh.shifting_gracetime,
    sh.shifting_timefr,
    sh.shifting_timeto,
    sh.shifting_LVtime,
    CONVERT(varchar,d.log_date,101)
ORDER BY LogDate ASC

My Sample code above is not working, it only work on shift_code with DS01, I also want to compute the other shift_code from dtr_tbl.

>> Result:

enter image description here

thanks.

  • please ignore dates on shifting_timeFr and shiftingtimeTo Columns, just focus on time Why don't you store it as a TIME then? – Mark Sinkinson Mar 26 '15 at 10:52
  • Can you script some sample data & DDL of the tables, then i'll take a look :) – Stijn Wynants Mar 26 '15 at 10:52
0

I have an idea that MAY cause it: The tables you are showing us are not SELECT * FROM ... are they? I think that in dtr_tbl, the logdate has a time part that is not 00:00:00 and this causes that in the WHERE condition,

log_date BETWEEN CAST('03/13/2015' AS datetime) 
             AND CAST('03/23/2015' AS datetime)

leads to ie

2015-03-23 8:00:05 between 2015-03-13 00:00:00 --true
                       and 2015-03-23 00:00:00 --false

thats the only thing i see that could be bad with it as rest of it seems ok to me and i don't see what the problem should be.

PS: It works for DS02 for instance too as can be seen in result set

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.