March 23, 2010 at 8:42 am
Hello all,
I have following situation: customers can order food for breakfast lunch and dinner. Each meal time has a cut-off time (e.g. 11:30 for lunch) and it is not possible to order fo that meal time when this time is passed.
Table #_ORDERS contain saved customer orders along with details about the corresponding meal time and target date:
create table #_MEALTIME (
MTI_ID int,
MTI_NAME varchar(10),
MTI_CUTOFF datetime
);
create table #_CUSTOMER (
CUST_ID int,
CUST_NAME varchar(20)
);
create table #_ORDER (
ORDER_ID int,
ORDER_TIMESTAMP datetime,
ORDER_TARGET_DATE datetime,
ORDER_MTI_ID int,
ORDER_CUST_ID int
);
insert into #_MEALTIME (MTI_ID, MTI_NAME, MTI_CUTOFF)
select 1,'Breakfast','1900-01-01 07:30:00' union all
select 2,'Lunch','1900-01-01 11:30:00' union all
select 3,'Dinner','1900-01-01 14:30:00'
insert into #_CUSTOMER (CUST_ID, CUST_NAME)
select 1, 'White' union all
select 2, 'Brown' union all
select 3, 'Black' union all
select 4, 'Grey' union all
select 5, 'Yellow' union all
select 6, 'Pink' union all
select 7, 'Blue' union all
select 8, 'Green';
insert into #_ORDER (ORDER_ID, ORDER_TIMESTAMP, ORDER_TARGET_DATE, ORDER_MTI_ID, ORDER_CUST_ID)
select 1,'2010-03-23 11:40:02', '2010-03-23 00:00:00',3, 1 union all
select 2,'2010-03-23 10:40:02', '2010-03-24 00:00:00',1, 2 union all
select 3,'2010-03-23 15:40:02', '2010-03-24 00:00:00',1, 3 union all
select 4,'2010-03-23 09:40:02', '2010-03-24 00:00:00',1, 4 union all
select 5,'2010-03-23 11:40:02', '2010-03-24 00:00:00',3, 4 union all
select 6,'2010-03-23 06:40:02', '2010-03-24 00:00:00',1, 6 union all
select 7,'2010-03-23 10:40:02', '2010-03-24 00:00:00',3, 6 union all
select 8,'2010-03-23 12:40:02', '2010-03-24 00:00:00',2, 6 union all
select 9,'2010-03-23 11:40:02', '2010-03-23 00:00:00',2, 1 union all
select 9,'2010-03-23 12:40:02', '2010-03-24 00:00:00',3, 5;
SELECT * FROM #_ORDER
My goal is to determine the customers with nothing ordered in past 2 meal times.
E.g. from the example above:
assuming now is 2010-03-24 10:20:00 and I need a list of customers not having ordered for past two meal times (breakfast on 24.3. and dinner on 23.3):
a query would return CUST_ID of customers for
Mr. Yellow (CUST_ID 5 - nothing for breakfast on 24.3. and dinner on 23.3.),
Mr. Green (CUST_ID 7 - nothing for breakfast on 24.3. and dinner on 23.3.)
Many thanks for any hints,
Marin
March 23, 2010 at 10:18 am
March 23, 2010 at 10:43 am
Hello Lutz,
I am not sure if my approach is correct.
I can determine the customers not having orderd on past meals of one day only, sth. like
SELECT CUST_ID FROM #_CUSTOMER
WHERE CUST_ID NOT IN
(SELECT o.ORDER_CUST_ID FROM #_ORDER o
WHERE EXISTS (SELECT MTI_ID, MTI_CUTOFF FROM #_MEALTIME
WHERE (CONVERT(varchar(10), MTI_CUTOFF,108)) < (CONVERT(varchar(10), getDate(),104))))
This would return customers with missing orders only for breakfast if executed after breakfast cut-off and before lunch cut-off (7:30-11:30) - INCORRECT , or it would return customers with missing orders for lunch and breakfast if executed after lunch cut-off and before dinner cut-off (11:30 - 14:30) - CORRECT or it would return customers with missing orders for dinner and lunch if executed after 14:30 (after dinner cut-off) _ CORRECT.
My problem is that currently I can't determine the "cross-date" missing meal as it is in bold indicated case (here I would need also dinner from previous date).
Thanks,
Marin
March 23, 2010 at 12:22 pm
Hi Marin,
I'm not sure if it's overengineered but I tried the divide'n'conquer method, causing the numerous CTEs... 😉
DECLARE @date DATETIME
SET @date='2010-03-24 10:20:00'
;WITH
cte0 AS
-- find the latest order per customer before @date
(
SELECT order_cust_id, MAX(order_target_date+MTI_CUTOFF)AS cutoff_date FROM #_ORDER o
INNER JOIN #_MEALTIME m
ON o.ORDER_MTI_ID=m.MTI_ID
WHERE order_timestamp<order_target_date+mti_cutoff
AND order_target_date+mti_cutoff < @date
GROUP BY order_cust_id
),
cte AS
-- build a pseudo calendar table holding meal times for @date and the day before
(
SELECT
MTI_ID,
DATEADD(dd,n,DATEADD(dd,DATEDIFF(dd,0,@date),0))+ mti_cutoff AS DATE
FROM
#_MEALTIME
CROSS APPLY
(SELECT -1 AS n UNION ALL
SELECT 0) t
),
cte2 AS
--number the meal datetime values before @date
(
SELECT
ROW_NUMBER() OVER(ORDER BY DATE DESC) ROW,
DATE,
MTI_ID,
DATEADD(dd,DATEDIFF(dd,0,DATE),0) AS dat
FROM cte
WHERE DATE < @date
)
, cte3 AS
-- find the date for "two meals before"
(
SELECT DAT,MTI_ID,DATE
FROM cte2
WHERE ROW=2
)
SELECT cust_id
FROM #_CUSTOMER
EXCEPT
SELECT cte0.order_cust_id
FROM cte0
CROSS JOIN cte3
WHERE cutoff_date>=DATE
/* result set:
cust_id
5
7
8
*/
March 24, 2010 at 10:58 am
Hello Lutz
this rocks! Many thanks for your hint, I was not aware of CTEs in MSSQL Server 2005.
Works perfectly!
Cheers,
Marin
March 24, 2010 at 11:39 am
Glad I could help 🙂
And I'd like to thank you for taking the time to set up sample data in a ready to use format. Made it easy to work on! Excellent job!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply