Hello All,



Is there anyway to count order for each month being active?



Example:



OrderID : 10

OrderDate : 02/01/2006

OrderStatus: Active





Order is been active till July 2006





OrderID : 10

OrderDate : 07/01/2006

OrderStatus: Closed



Here is the result I'm trying to get:



Month   OrderID

FEB          10

MAR        10

APR         10

MAY        10

JUN          10





Thanks,

     Walter

Walter,

Use the auxilliary numbers table.


create table #orders (
OrderID int not null,
OrderDate datetime not null,
OrderStatus char(1) not null check(OrderStatus in ('A', 'C')),
primary key (OrderID, OrderStatus) -- this assumes that there is no more
than two rows for each OrderId, one with status A (activated) and one with
status C (closed))
go

insert #orders values (10, '20060201', 'A')
insert #orders values (10, '20060701', 'C')
insert #orders values (11, '20060301', 'A')
insert #orders values (11, '20060501', 'C')
insert #orders values (12, '20050901', 'A')
insert #orders values (12, '20060201', 'C')
go

select datename(m, dateadd(m, m.number, 0)), t.orderid
from numbers m
inner join (
        select o.OrderID,
        (select datediff(m, 0, OrderDate) from #orders where OrderID =
o.OrderID and OrderStatus = 'A') as Activated,
        datediff(m, 0, o.OrderDate) as Closed
        from #Orders o
        where o.OrderStatus = 'C'
) t on
        m.number >= t.Activated and
        m.number < t.Closed
order by t.orderid, m.number



HTH,

Dean


Show quoteHide quote

Marking (Not just Finding) Duplicate Values ......
Select part of a string
UPDATE in a SQL script file is not working.
Problem with WHERE clause
SET/SELECT truncate string