New B needs assistance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

New B needs assistance

Author

Topic

sharkb8
Starting Member

12 Posts

Posted - 2002-12-18 : 17:12:47

This is my first post, and I'm not too heavy into SQL so forgive me if I sound like a idiot.

I have a table that contains user ID's and their corresponding orders.

The users can submit orders at any time.

I would like to find all users in the system where they have multiple orders for the same product.

Further, I would like to
DATEADD (d, 7, [DateofOrder]) As window1
DATEADD (d, 7, [DateofOrder]) As window2
for each item and then scan the system to find which users have order dates that are greater than their DateOfOrder and Less than Window1, which would tell me how many orders were re-filled within 7 days.

The same goes for the 14 day (window2)

I've run through it a few times, and have the logic for adding the Dates to the orders, but am having a problem doing the scan and finding only those users who have duplicate orders for the same item.

Any help would be greatly appreciated.

Thanks...

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-18 : 17:15:21

SELECT UserID, OrderItem
FROM
Orders
GROUP BY UserID, OrderItem
HAVING COUNT(*) > 1

SELECT UserID,OrderItem
FROM Orders
WHERE
Orderdate > DateOfOrder AND Orderdate < DATEADD(d,7,DateOfOrder)

New B Hint: If you post your ddl (ex: create table) some sample data and expected results you will obtain better solutions and you'll get faster responses.

Edited by - ValterBorges on 12/18/2002 17:22:02

sharkb8
Starting Member

12 Posts

Posted - 2002-12-18 : 17:24:17

Thanks Valter...
I tried that some time ago, and while that does give me the Users and Orders for a particular product, it only lists the 1st one, not all the orders in the system by that product...and I still have the 7 and 14 day problem...

Thanks!!!

Oops..I responded before your edit...so here's a good example

I have a Orders Table with UserID(varchar 11), Order_dt(smalldatetime), ProductName(varchar 50)....amongst other things

The issue that I'm having is that a user can have multiple orders
i.e.

UserID Order_dt ProductName
------ -------- -----------
1111 12/1/02 Tennis Balls
2222 12/31/02 Ping Pong Balls
1111 11/30/02 Tennis Balls

I want to find all the occurrences of user 1111 ordering tennis balls and weed out the users who have only ordered 1 OR non-matching items.

I then want to pad the original order date, with 2 possible values, 7 and 14 days....then scan the system for subsequent orders that are within 7 days, and 14 days. That way we can identify patterns (possibly offer discounts), and stock accordingly for that client.

The problem is, that for each occurrence of the user ordering a product they've ordered before, I need to repeat the DateAdd routine and the order date scanning forward depends on the row you are looking at currently....

Thanks!!!

Edited by - sharkb8 on 12/18/2002 17:36:46

Edited by - sharkb8 on 12/18/2002 17:40:03

Edited by - sharkb8 on 12/18/2002 17:42:14

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-18 : 22:23:30

hi sharky

good question, I've had to do this thing a lot before and it's tricky your first time. You could do all this in one clever SQL statement, but for clarity (and for my poor brain) I'll break it down into steps (and views!) -

First - let's turn Valter's query into a view
create view vMultipleOrders
as
select UserID, ProductName
from Orders
group by UserID, ProductName
having count(*) > 1
go

Next, create a view which calculates the windows
create view vOrderWindows
as
select a.UserID, a.ProductNAme, a.Order_dt, dateadd(d,7,a.Order_dt) as window1, dateadd(d, 14, a.Order_dt) as window2
from Orders a inner join vMultipleOrders b
on a.UserID = b.UserID
and a.ProductName = b.ProductName
go
notice this builds on the first view, and I join the original table, so that only Multiple orders are included...

and now for our query for users who've reordered within 7 days

select a.UserID, a.ProductName, 'Reordered within 7 days', b.Order_dt as OriginalOrder, a.Order_dt as ReOrder
from vOrderWindows a, vOrderWindows b
where a.Order_dt between b.Order_dt and b.window1
and a.Order_dt > b.Order_dt
and a.ProductName = b.ProductName
and a.UserID = b.UserID

This step joins vOrderWindows on itself - so the where clause is all important - (columns in red are for testing only)

a.Order_dt between b.Order_dt and b.window1 - obvious
a.Order_dt > b.Order_dt - makes sure firstly that we aren't matching to the same record, and secondly, so that record "a" is the later, or "re-ordered" record and "b" is the original order.
a.ProductName = b.ProductName
and a.UserID = b.UserID - obvious

similarly then for the 14 day reorders
select a.UserID, a.ProductName, 'Reordered within 14 days', a.Order_dt, b.window2
from vOrderWindows a, vOrderWindows b
where a.Order_dt between b.window1 and b.window2
and a.Order_dt > b.Order_dt
and a.ProductName = b.ProductName
and a.UserID = b.UserID

Not sure what you want to do with items reordered more than 14 days after, or for items re-ordered more than a second time.

Of course if you want all this in the one recordset, just union the two queries - eg
select a.UserID, ...
union
select a.UserID, ...

Hope that all makes sense. When you get clever - do it all in one go - if you have further requirements, let us know.

PS (I assume Valter's in bed now zzzzzzzzzzz....)

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 12/18/2002 22:30:39

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-18 : 22:26:03

PS - for anyone else - here's the script for my example data

create table Orders(UserID integer, Order_dt datetime, ProductName nvarchar(100))
insert into Orders (UserID, Order_dt, ProductName) select 1111, convert(Datetime, '12/1/02'), 'Tennis Balls'
insert into Orders (UserID, Order_dt, ProductName) select 2222, convert(datetime, '12/31/02'), 'Ping Pong Balls'
insert into Orders (UserID, Order_dt, ProductName) select 1111, convert(datetime, '11/30/02'), 'Tennis Balls'
insert into Orders (UserID, Order_dt, ProductName) select 2222, convert(datetime, '01/25/03'), 'Ping Pong Balls'
insert into Orders (UserID, Order_dt, ProductName) select 2222, convert(datetime, '01/01/03'), 'BBQ Tongs'
insert into Orders (UserID, Order_dt, ProductName) select 2222, convert(datetime, '01/15/03'), 'BBQ Tongs'

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-19 : 12:59:20

quote:

PS (I assume Valter's in bed now zzzzzzzzzzz....)

Nope,
Opening of Lord Of The Rings Two Towers.

It was awwwwwwesoooooooommmmmmmmmmme

Edited by - ValterBorges on 12/19/2002 13:00:18

sharkb8
Starting Member

12 Posts

Posted - 2002-12-19 : 14:18:42

rrb,
Thanks for the all the help!! In response to your question, I need to do the same thing for all the orders in the system, not just for the 1st two.....

I'm going to try the code out....Thanks for the help!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources