Orders placed in 6 months - 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 2005 Forums

Transact-SQL (2005)

Orders placed in 6 months

Author

Topic

mapidea
Posting Yak Master

124 Posts

Posted - 2009-02-03 : 23:26:59

Query to write the orders placed in 6 months after the first order was placed for all the customers.

To find the first order placed

select
o.customerid,
(Min(o.Orderdate) Over (partition By o.CustomerId)) as firstorder
from orders as o

To find the 6 months

select
o.customerid,
--p.productid,
(Min(o.Orderdate) Over (partition By o.CustomerId)) as firstorder
from orders as o
--inner join orderdetails as d on o.orderid = d.orderid
--inner join products as p on d.productid = p.productid
where o.orderdate <= dateadd(mm, 6, firstorder)

the where condition is not taking this.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-03 : 23:41:25

check like this

select
o.customerid,
--p.productid,
o.firstorder
from (select
customerid,
Min(Orderdate) Over (partition By CustomerId), orderdate as firstorder
from orders
) as o
--inner join orderdetails as d on o.orderid = d.orderid
--inner join products as p on d.productid = p.productid
where o.orderdate <= dateadd(mm, 6, o.firstorder)

mapidea
Posting Yak Master

124 Posts

Posted - 2009-02-04 : 00:11:31

Thanks Bklr.

I have it

select
o.customerid,
p.productid,
o.orderdate,
o.firstorder
from
(
Select
orderid,
orderdate,
customerid,
(Min(Orderdate) Over (partition By CustomerId)) as firstorder
from orders
)
as o
inner join orderdetails as d on o.orderid = d.orderid
inner join products as p on d.productid = p.productid
where o.orderdate <= dateadd(mm, 6, firstorder)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-04 : 00:21:01

ur welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources