Query help

[mikedaruke]

I need some help taking a table with the following

Client_name, Store_name, product, date
Bob, Joes Cars, Oil, 3/01/08
Bob, Joes Cars, Oil, 4/07/08
Bob, Joes Cars, Oil, 5/08/08
Bob, Joes Cars, Oil, 5/25/08
Bob, Joes Cars, Oil, 6/10/08
Chris,Joes Cars, Oil, 3/10/08
Chris,Joes Cars, Oil, 3/28/08
etc

I want to somehow join the table to itself, and find out the how long before the person came back. But a unique entity is Store_name/Client_name/Product.

So I want my results to look like this
Client_name, Store_name, Product, date, next_return
Bob, Joes Cars, Oil, 3/01/08, 4/07/08
Bob, Joes Cars, Oil, 4/07/08, 5/08/08
Bob, Joes Cars, Oil, 5/08/08, 5/25/08
Bob, Joes Cars, Oil, 5/25/08, 6/10/08
Bob, Joes Cars, Oil, 6/10/08, Null
Chris,Joes Cars, Oil, 3/10/08, 3/28/08
Chris,Joes Cars, Oil, 3/28/08, Null

My problem is the table has like 15,000 clients, and total of like 400,000 rows. so its huge and my querys never complete. Not sure what to do, join it, or run a subquerys. Can anyone help?

 

[LKBrwnDBA]

Show us your query...


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[mikedaruke]

select client_name, store_name, product_date, s_date
, b.s_date "return_date"
from
table1 a, table1 b
where b.s_date = (select min(start_date) return_date
from table1
where start_date > a.start_date)

 

[mikedaruke]

sorry wrong query, here it is, it works if i put a client name in, well some of them, some take to long to come back. I need it to be faster or it will never work

with temp1 as(
select * from table1 where client_name = 'Bob'
)
select * from
temp1 a, temp1 b
where b.s_date = (select max(s_date) from temp1 c
where c.CLIENT_NAME = a.client_name
and c.store_NAME = a.store_name
and c.product = a.product
and c.S_DATE < a.s_date)

 

[LKBrwnDBA]

Would this work?:

Select Client_Name, Store_Name, Product
     , Min(S_Date) S_Date
     , Max(S_Date) Return_Date
  From Table1
 Group By Client_Name, Store_Name, Product

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[mikedaruke]

no that would get me the min and max. I need the next day, based off the last date.

 

[karluk]

Probably the easiest way to code this is with the analytical function "lead". You would have to test it on your table, though, to see if the performance improves.

select client_name, store_name, product, purchase_date, lead(purchase_date,1)
over (partition by client_name order by purchase_date) as next_return
from your_table

 

[LKBrwnDBA]

Ok, I get it...try this:

Select Client_Name, Store_Name, Product
     , S_Date
     , LEAD(S_Date, 1) OVER (
          PARTITION BY Client_Name, Store_Name, Product
          ORDER BY S_Date) Return_Date
  From Table1;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb