Counting shifts

  • I have a table that contains some order data. This table contains the following relevant fields:

    OrderID

    Delivery_Date

    Time_TargetDelivery

    DriverID

    We have 3 shifts that run as follows:

    7 a.m. to 11 a.m. is breakfast

    11 a.m. to 3 p.m. is lunch

    4:30 p.m. to 9 p.m. is dinner

    Based upon the Time_TargetDelivery field I want to count how many shifts a driver has during a given time period. If the driver has the following deliveries:

    Monday: 3 breakfast, 2 lunch

    Tuesday: 2 lunch, 5 dinner

    Wednesday: 6 dinner

    Thursday: 3 lunch

    Friday: 8 dinner

    my query should return a count of 7 shifts. I've tried using a case statement to determine the time period of an order and add it as a shift of that type, but it only catches one of each type of shift, so the maximum number of shifts it returns in 3. How can I count the shifts properly?

  • select Totalcount = (breakfast+dinner+lunch)

    from

    (

    Select

    (Select Count(*) From Time_TargetDelivery Where Time_TargetDelivery < breakfast time range) As breakfast,

    (Select Count(*) From Time_TargetDelivery Where Time_TargetDelivery < Lunch time range) As Lunch,

    (Select Count(*) From Time_TargetDelivery Where Time_TargetDelivery < Dinnertime range) As Dinner

    from table

    ) as T

  • That would not work. It would return the following information:

    Monday breakfast: 3

    Monday lunch: 2

    Tuesday lunch: 2

    Tuesday dinner: 5

    Wednesday dinner: 6

    Thursday lunch: 3

    Friday dinner: 8

    TotalCount = 24

    I should also have mentioned the following:

    I already have a query in place that is totalling up the number of errors during a given period, the total number of orders delivered during a given period, the total dollar amount of orders delivered during a period, etc. and just need to add this shift issue to that query. Any ideas?

  • May want to group the Time_TargetDelivery.

  • Another good idea, but that won't work because the time_targetdelivery column has different values in it and the grouping won't help me determine the shift splits.

  • How about.

    create function Shift(@Time datetime) as

    return case when Hour([@Time]) beween 3 and 11 then 'B'

    else when hour([@teim]) between 11 and 15 then 'L'

    else 'D'

    end

    select Convert(DateTime,Convert(Varchar(50),Delivery_Date,101)) DelivDate,

    , shift(Time_TargetDelivery) Shift

    into #temp

    from table

    group by Convert(DateTime,Convert(Varchar(50),Delivery_Date,101)),

    , shift(Time_TargetDelivery)

    select count(*) from #temp

    where DelivDate between @start and @end

    I didn't look up the syntax on the function, so that might need adjusting....

  • It would be nice if u can paste some sample data from your table and the result expected from that data.

    Relationships are like Banks.You Invest the Principal and then get the Interest.


    He who knows others is learned but the wise one is one who knows himself.

  • Okay, How about this?

    select count(DriverID) from

    (select driverID, Delivery_date,

    DeliveryTime =

    CASE

    WHEN datepart(hour,Time_TargetDelivery) between 7 and 10

    Then 'B'

    WHEN datepart(hour,Time_TargetDelivery) between 11 and 15

    Then 'L'

    WHEN datepart(hour,Time_TargetDelivery) between 16 and 23

    Then 'D'

    END

    from btctblDriverActivity

    group by driverID, delivery_Date,

    CASE

    WHEN datepart(hour,Time_TargetDelivery) between 7 and 10

    Then 'B'

    WHEN datepart(hour,Time_TargetDelivery) between 11 and 15

    Then 'L'

    WHEN datepart(hour,Time_TargetDelivery) between 16 and 23

    Then 'D'

    END)as t

    group by DriverID

    This breaks each record down into a shift and counts the number of shifts per day.

  • I have this sample data

    12003-08-11 00:00:00.0007

    22003-08-11 00:00:00.0008

    32003-08-11 00:00:00.0009

    42003-08-11 00:00:00.00015

    52003-08-12 00:00:00.00016

    And I m making this query

    select sum(case when time_target between 7 and 11 then 1 else 0 end) as 'BREAKFAST',

    sum(case when time_target between 12 and 15 then 1 else 0 end) as 'LUNCH',

    sum(case when time_target between 16 and 21 then 1 else 0 end) as 'DINNER'

    from orders1 where deliverydate>='2003-08-11'

    GROUP BY deliverydate

    Correct if my approach is wrong..

    Relationships are like Banks.You Invest the Principal and then get the Interest.


    He who knows others is learned but the wise one is one who knows himself.

  • Actually, your approach is similar to the one I'm using that isn't returning the proper information. Consider the following (real) sample data:

    1 2003-08-10 00:00:00 1899-12-30 12:15:00

    2 2003-08-10 00:00:00 1899-12-30 12:30:00

    3 2003-08-11 00:00:00 1899-12-30 10:15:00

    4 2003-08-11 00:00:00 1899-12-30 11:15:00

    5 2003-08-11 00:00:00 1899-12-30 20:15:00

    This data should return 2 lunch shifts and 1 dinner shift if I select the date range of >= 8/10/2003 and <= 8/11/2003, but what happens is it returns 1 lunch shift and one dinner shift.

  • To count the number of shifts, you have to group by the date.

    
    
    SELECT bf_table.COUNT(*) as breakfast,
    lu_table.COUNT(*) as lunch,
    di_table.COUNT(*) as dinner
    FROM (SELECT delivery_date FROM <your table>
    WHERE DATEPART(hour,Time_TargetDelivery) < 12
    GROUP BY delivery_date) bf_table,
    (SELECT delivery_date FROM <your table>
    WHERE DATEPART(hour,Time_TargetDelivery) >= 12
    AND DATEPART(hour, Time_TargetDelivery) < 4
    GROUP BY delivery_date) lu_table,
    (SELECT delivery_date FROM <your table>
    WHERE DATEPART(hour,Time_TargetDelivery) > 4
    OR (DATEPART(hour, Time_TargetDelivery) = 4
    AND DATEPART(minute,Time_TargetDelivery) >= 30)
    GROUP BY delivery_date) di_table

    Edited by - NPeeters on 08/13/2003 01:23:20 AM

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply