Solved

Working time between 2 datetimes

Posted on 2015-01-30
8
69 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
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 26

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 Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now