Solved

Working time between 2 datetimes

Posted on 2015-01-30
8
75 Views
Last Modified: 2015-02-06
Im trying to workout the amount of minutes between two dateTimes, but exlclude weekends.

I found a similar issue on StackExchange  stackoverflow.com/questions/18078339/sql-server-2008-sum-business-minutes-between-two-dates-taking-into-account-cus   and started working from that.

I built my query as:-
SELECT  queueChange.entered,
        queueChange.exited,
        workingDays.[Date],
        StartTime = CASE WHEN CAST(queueChange.entered AS DATE) = workingDays.[Date] THEN CAST(queueChange.entered AS TIME) ELSE CAST('08:30' AS TIME) END,
        EndTime = CASE WHEN CAST(queueChange.exited AS DATE) = workingDays.[Date] THEN CAST(queueChange.exited AS TIME) ELSE CAST('17:00' AS TIME) END
FROM    queueChange
        INNER JOIN workingDays
            ON workingDays.Date >= CAST(queueChange.entered AS DATE)
            AND workingDays.Date <= CAST(queueChange.exited AS DATE)

Open in new window


which runs without error, but displays no rows.

I have uploaded my sqlite3 database here tmpWorkingHours.sql, as you can see its quite simple 2 tables, one with the entered and exited, and the other a list of working hours (I know the SQL code above doesn't need it as has defaults in as part of the cast).

Does anyone know anything they can point me in the right direction?

Thank you
0
Comment
Question by:tonelm54
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 

Author Comment

by:tonelm54
ID: 40581413
sqlite
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40582078
I'm not very experienced with SQLite but I believe that date information is actually stored as strings, and although using DATE() is fine you probably need to do this on both tables. An alternative to DATE() is STRFTIME('%Y-%m-%d',...).
With this in mind please try the following:
select 
       *
FROM    queueChange
        LEFT JOIN workingDays
            ON  STRFTIME('%Y-%m-%d',workingDays.date) >= STRFTIME('%Y-%m-%d',queueChange.entered)
            AND STRFTIME('%Y-%m-%d',workingDays.date) <= STRFTIME('%Y-%m-%d',queueChange.exited)
;

Open in new window


----------------------
Notes:
Please do NOT use the sqlite file format to provide the DDL or data (and when providing data only supply a sample)

I could see 3 tables in that file:

CREATE TABLE [workingDays] (
  [date] DATE,
  [startTime] TIME,
  [endTime] TIME)

CREATE TABLE [queueChange] (
  [ticketNo] INT,
  [queue] VARCHAR(35),
  [entered] DATETIME,
  [exited] DATETIME)

CREATE TABLE "temp_table_27E74EF9F4E64890AD24B841846B1C72" (
  [date] DATETIME,
  [startTime] DATETIME,
  [endTime] DATETIME)

but could not extract any data.

Here's what I did use (at SQLFiddle)
**SQLite (WebSQL) Schema Setup**:

    
    
    CREATE TABLE queueChange
    	("ticketNo" INTEGER, "queue" TEXT(9), "entered" DATE, "exited" DATE)
    ;
    
    
    INSERT INTO queueChange
    	("ticketNo", "queue", "entered", "exited")
    VALUES
    	(1, 'blah blah', '2014-01-03 12:12:12', '2014-01-06 12:29:55')
    ;
    
    
    INSERT INTO queueChange
    	("ticketNo", "queue", "entered", "exited")
    VALUES
    	(2, 'blah blah', '2014-01-04 13:09:48', '2014-01-07 13:27:31')
    ;
    
    
    INSERT INTO queueChange
    	("ticketNo", "queue", "entered", "exited")
    VALUES
    	(3, 'blah blah', '2014-01-05 14:07:24', '2014-01-08 14:25:07')
    ;
    
    
    INSERT INTO queueChange
    	("ticketNo", "queue", "entered", "exited")
    VALUES
    	(4, 'blah blah', '2014-01-06 15:05:00', '2014-01-09 15:22:43')
    ;
    
    
    INSERT INTO queueChange
    	("ticketNo", "queue", "entered", "exited")
    VALUES
    	(5, 'blah blah', '2014-01-07 16:02:36', '2014-01-10 16:20:19')
    ;
    
    
    INSERT INTO queueChange
    	("ticketNo", "queue", "entered", "exited")
    VALUES
    	(6, 'blah blah', '2014-01-08 17:00:12', '2014-01-11 17:17:55')
    ;
    
    
    INSERT INTO queueChange
    	("ticketNo", "queue", "entered", "exited")
    VALUES
    	(7, 'blah blah', '2014-01-09 17:57:48', '2014-01-12 18:15:31')
    ;
    
    
    INSERT INTO queueChange
    	("ticketNo", "queue", "entered", "exited")
    VALUES
    	(8, 'blah blah', '2014-01-10 18:55:24', '2014-01-13 19:13:07')
    ;
    
    
    INSERT INTO queueChange
    	("ticketNo", "queue", "entered", "exited")
    VALUES
    	(9, 'blah blah', '2014-01-11 19:53:00', '2014-01-14 20:10:43')
    ;
    
    
    -- ---------------------------------------
    
    
    CREATE TABLE workingDays
    	("date" DATE)
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-03 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-06 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-07 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-08 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-09 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-10 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-13 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-14 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-15 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-16 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-17 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-20 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-21 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-22 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-23 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-24 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-27 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-28 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-29 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-30 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-01-31 00:00:00')
    ;
    
    
    INSERT INTO workingDays
    	("date")
    VALUES
    	('2014-02-03 00:00:00')
    ;
    
    -- -----------------------------------
    
    
    
    
    
    CREATE TABLE MyCalendarTable
    	("DAY" DATE, "IS_WORKING_DAY" INTEGER)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-01 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-02 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-03 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-04 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-05 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-06 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-07 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-08 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-09 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-10 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-11 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-12 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-13 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-14 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-15 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-16 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-17 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-18 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-19 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-20 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-21 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-22 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-23 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-24 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-25 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-26 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-27 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-28 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-29 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-30 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-01-31 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-01 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-02 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-03 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-04 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-05 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-06 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-07 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-08 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-09 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-10 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-11 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-12 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-13 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-14 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-15 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-16 00:00:00', 0)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-17 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-18 00:00:00', 1)
    ;
    
    
    INSERT INTO MyCalendarTable
    	("DAY", "IS_WORKING_DAY")
    VALUES
    	('2014-02-19 00:00:00', 1)
    ;
    
    
    

**Query 1**:

    select 
           *
    FROM    queueChange
            LEFT JOIN workingDays
                ON  STRFTIME('%Y-%m-%d',workingDays.date) >= STRFTIME('%Y-%m-%d',queueChange.entered)
                AND STRFTIME('%Y-%m-%d',workingDays.date) <= STRFTIME('%Y-%m-%d',queueChange.exited)
    

**[Results][2]**:
    
    | ticketNo |     queue |             entered |              exited |                date |
    |----------|-----------|---------------------|---------------------|---------------------|
    |        1 | blah blah | 2014-01-03 12:12:12 | 2014-01-06 12:29:55 | 2014-01-03 00:00:00 |
    |        1 | blah blah | 2014-01-03 12:12:12 | 2014-01-06 12:29:55 | 2014-01-06 00:00:00 |
    |        2 | blah blah | 2014-01-04 13:09:48 | 2014-01-07 13:27:31 | 2014-01-06 00:00:00 |
    |        2 | blah blah | 2014-01-04 13:09:48 | 2014-01-07 13:27:31 | 2014-01-07 00:00:00 |
    |        3 | blah blah | 2014-01-05 14:07:24 | 2014-01-08 14:25:07 | 2014-01-06 00:00:00 |
    |        3 | blah blah | 2014-01-05 14:07:24 | 2014-01-08 14:25:07 | 2014-01-07 00:00:00 |
    |        3 | blah blah | 2014-01-05 14:07:24 | 2014-01-08 14:25:07 | 2014-01-08 00:00:00 |
    |        4 | blah blah | 2014-01-06 15:05:00 | 2014-01-09 15:22:43 | 2014-01-06 00:00:00 |
    |        4 | blah blah | 2014-01-06 15:05:00 | 2014-01-09 15:22:43 | 2014-01-07 00:00:00 |
    |        4 | blah blah | 2014-01-06 15:05:00 | 2014-01-09 15:22:43 | 2014-01-08 00:00:00 |
    |        4 | blah blah | 2014-01-06 15:05:00 | 2014-01-09 15:22:43 | 2014-01-09 00:00:00 |
    |        5 | blah blah | 2014-01-07 16:02:36 | 2014-01-10 16:20:19 | 2014-01-07 00:00:00 |
    |        5 | blah blah | 2014-01-07 16:02:36 | 2014-01-10 16:20:19 | 2014-01-08 00:00:00 |
    |        5 | blah blah | 2014-01-07 16:02:36 | 2014-01-10 16:20:19 | 2014-01-09 00:00:00 |
    |        5 | blah blah | 2014-01-07 16:02:36 | 2014-01-10 16:20:19 | 2014-01-10 00:00:00 |
    |        6 | blah blah | 2014-01-08 17:00:12 | 2014-01-11 17:17:55 | 2014-01-08 00:00:00 |
    |        6 | blah blah | 2014-01-08 17:00:12 | 2014-01-11 17:17:55 | 2014-01-09 00:00:00 |
    |        6 | blah blah | 2014-01-08 17:00:12 | 2014-01-11 17:17:55 | 2014-01-10 00:00:00 |
    |        7 | blah blah | 2014-01-09 17:57:48 | 2014-01-12 18:15:31 | 2014-01-09 00:00:00 |
    |        7 | blah blah | 2014-01-09 17:57:48 | 2014-01-12 18:15:31 | 2014-01-10 00:00:00 |
    |        8 | blah blah | 2014-01-10 18:55:24 | 2014-01-13 19:13:07 | 2014-01-10 00:00:00 |
    |        8 | blah blah | 2014-01-10 18:55:24 | 2014-01-13 19:13:07 | 2014-01-13 00:00:00 |
    |        9 | blah blah | 2014-01-11 19:53:00 | 2014-01-14 20:10:43 | 2014-01-13 00:00:00 |
    |        9 | blah blah | 2014-01-11 19:53:00 | 2014-01-14 20:10:43 | 2014-01-14 00:00:00 |



  [1]: http://sqlfiddle.com/#!7/4b8fd/11

Open in new window

0
 
LVL 30

Expert Comment

by:hnasr
ID: 40582381
List few representative records with required fields and corresponding required output.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 40588629
I dont understand the ON clause condition:

            ON workingDays.Date >= CAST(queueChange.entered AS DATE)
            AND workingDays.Date <= CAST(queueChange.exited AS DATE)

I assume the working dates are only the date part without the time, like '2015-02-04' and entered and exited dates have also time part like, '2015-02-04 08:30' and '2015-02-04 17:00' and that is the reason why you cast them to date. But if you do, both those dates will become '2015-02-04' so it <= doesn't make much sense because what matters is if the dates are equal or not.

I would try:

            ON (workingDays.Date = CAST(queueChange.entered AS DATE)
            or workingDays.Date = CAST(queueChange.exited AS DATE))
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40589620
If you want each day (just the YYYY-MM-DD) from a starting point to an ending point, then you do need >= and <=

e.g.
| ticketNo |      queue |             entered |              exited |    workingDays.date |
|----------|------------|---------------------|---------------------|---------------------|
|        4 |   ticket 4 | 2014-01-06 15:05:00 | 2014-01-09 15:22:43 | 2014-01-06 00:00:00 |
|        4 |   ticket 4 | 2014-01-06 15:05:00 | 2014-01-09 15:22:43 | 2014-01-07 00:00:00 |
|        4 |   ticket 4 | 2014-01-06 15:05:00 | 2014-01-09 15:22:43 | 2014-01-08 00:00:00 |
|        4 |   ticket 4 | 2014-01-06 15:05:00 | 2014-01-09 15:22:43 | 2014-01-09 00:00:00 |

Open in new window

0

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Read about achieving the basic levels of HRIS security in the workplace.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question