July 5, 2009 at 5:34 pm
Hi
Self learnign SQL.
I have an orders and customer table.
I want to retrieve the customer ID's that have orders within a certain date period but not prior, with the aim of finding recently new customers.
If the customer table has a field 'cus_id' as does the orders table. The orders table also has the field 'date'
I have tried with all kinds of JOINS and EXISTS /NOT EXISTS statement, but I either get nothing or everything.
Any help greatly appreciated.
Matt
July 5, 2009 at 5:47 pm
Hi Matt,
as you probably figured it's hard to verbally describe a database scenario with just a few words...
It's also hard for some of us to "draw the picture".
Maybe you could post some sample data together with your expected result and what you've tried so far to make it easier for us to work with your data? The best way to post sample data is described in the link in my signature.
July 5, 2009 at 6:27 pm
select distinct
c.cus_id
from
@customers c
inner join
@orders o
on c.cus_id = o.cus_id
where
o.order_date between @start and @end
andc.cus_id not in (select o2.cus_id from @orders o2 where o2.order_date < @start);
--- Full Test ---
declare @customers table (cus_id int);
declare @orders table (cus_id int, order_date datetime);
insert into @customers values (1);
insert into @customers values (2);
insert into @customers values (3);
insert into @customers values (4);
insert into @orders values (1, '1/1/2009')
insert into @orders values (1, '2/1/2009')
insert into @orders values (2, '2/1/2009')
insert into @orders values (2, '3/1/2009')
insert into @orders values (3, '3/1/2009')
insert into @orders values (3, '4/1/2009')
insert into @orders values (4, '4/1/2009')
insert into @orders values (4, '5/1/2009')
declare @start datetime;
declare @end datetime;
set @start = '3/1/2009';
set @end = '4/1/2009';
select distinct
c.cus_id
from
@customers c
inner join
@orders o
on c.cus_id = o.cus_id
where
o.order_date between @start and @end
andc.cus_id not in (select o2.cus_id from @orders o2 where o2.order_date < @start);
July 6, 2009 at 11:58 am
Hi VampireGhost,
Thank you for providing the sample data in a ready to use format! Good job! 🙂
Regarding the SELECT statement:
Instead of using the IN clause I'd rather use something like:
SELECT o.cus_id FROM @customers c INNER JOIN @orders o ON o.cus_id=c.cus_id
GROUP BY o.cus_id
HAVING MIN(order_date)>=@start
AND MIN(order_date)<=@end
I'd prefer this solution for two reasons: I wouldn't need any IN clause that will cause an additional select and therefore a decrease in performance and I also wouldn't need any DISTINCT clause which usually indicates a suboptimal query.
I'd also add any index to the orders table (assuming, it's not a table variable...) covering cus_id and order_date and I'd expect to see an index on customers.cus_id as well.
July 6, 2009 at 8:42 pm
Here is an alternative solution. I added an additional column to show you what was being returned.
declare @customers table (cus_id int);
declare @orders table (cus_id int, order_date datetime);
insert into @customers values (1);
insert into @customers values (2);
insert into @customers values (3);
insert into @customers values (4);
insert into @orders values (1, '1/1/2009')
insert into @orders values (1, '2/1/2009')
insert into @orders values (2, '2/1/2009')
insert into @orders values (2, '3/1/2009')
insert into @orders values (3, '3/1/2009')
insert into @orders values (3, '4/1/2009')
insert into @orders values (4, '4/1/2009')
insert into @orders values (4, '5/1/2009')
declare @start datetime;
declare @end datetime;
set @start = '3/1/2009';
set @end = '4/1/2009';
select
c.cus_id,
o.order_date
from
@customers c
inner join @orders o
on c.cus_id = o.cus_id
select --distinct
c.cus_id,
o.order_date
from
@customers c
inner join @orders o
on c.cus_id = o.cus_id
left outer join @orders o1
on (c.cus_id = o1.cus_id
and o1.order_date = @start and
o.order_date < @end and -- change < to <= if you want to include the end date in the range
o1.cus_id is null
July 8, 2009 at 3:56 pm
Hi Guys,
Thanks all for your help.
I came up with a solution, substitute customers for nurses, orders for jobs and date for weekno
select count(distinct nurses.nurs_id) from nurses join jobs on jobs.nurs_id=nurses.nurs_id
where exists (select * from jobs where nurses.nurs_id=jobs.nurs_id and jobs.weekno > 200912
and not exists (select * from jobs where nurses.nurs_id=jobs.nurs_id and jobs.weekno < 200913
I don't know if this is less suitable than other suggestions, I will need to examine the others and learn the techniques !
Matt
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply