Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 83
  • Last Modified:

Working time between 2 datetimes

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
tonelm54
Asked:
tonelm54
1 Solution
 
tonelm54Author Commented:
sqlite
0
 
PortletPaulfreelancerCommented:
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
 
hnasrCommented:
List few representative records with required fields and corresponding required output.
0
 
ZberteocCommented:
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
 
PortletPaulfreelancerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now