August 8, 2003 at 7:29 am
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?
August 8, 2003 at 8:08 am
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
August 8, 2003 at 8:33 am
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?
August 8, 2003 at 8:55 am
May want to group the Time_TargetDelivery.
August 8, 2003 at 11:22 pm
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.
August 10, 2003 at 6:37 pm
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....
August 10, 2003 at 11:27 pm
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.
August 10, 2003 at 11:39 pm
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.
August 11, 2003 at 12:20 am
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.
August 12, 2003 at 11:09 pm
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.
August 13, 2003 at 1:18 am
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