Complicated date selection

[jdc73]

I have a table that has part numbers with affectivity dates like so
Part 1 01/01/2003
Part 2 07/15/2004
Part 1 09/30/2005
Part 2 11/26/2006
Part 1 12/15/2007

I need to find all the Parts sales for each part between the first effectively date and the next effectively date and then the sales between the second effectively date and the third date and then between the third date and that date plus 36 months. The last date in the table always has an end date of 36 months from that date while previous dates have and end date of the next date in the table for that part.

Any suggestions?

[ck9663]

Say what?...Just kidding, I got confused...

Can you post how do you want your output to look like based on the sample data you posted?


~~~ CK

[Newface]

just i was try to understand your issue and as per my perception

select partno,sum(sales) sale from test where date >'2003-01-01' and date<'2006-01-01' group by partno

might help you in some manner

[jdc73]

Sorry, looks like I did not give enough info.
I have two tables, one with all the sales information including the date the sale was made. The other table contains the part revision information. This table lists the part number which was revised and the date of the revision. Each part can be revised any number of times during its life cycle. I need to produce a report of all the sales for each part that has been revised for periods starting with the first revision date to the next revision date and then from that revision date to the next and so on. Each revision has a life cycle of 36 months unless it is revised within that time period. The output should look something like this:
Part Number Beginning Date Ending Date Sales Amount
Xxxxx1 01/01/2007 05/15/2008 $000,000
Xxxxx2 01/25/2009 02/20/2010 $000,000
Xxxxx1 05/15/2008 09/30/2011 $000,000

If you need more info, let me know.
Thanks

[Newface]

if i understood,this should be :

select t1.PartNumber,t1.BeginningDate,t1.EndingDate,sum(t.sales) Sales
	   from test1 t1 inner join test t 
	   on t1.PartNumber=t.PartNo
	   group by t1.PartNumber,t1.BeginningDate,t1.EndingDate

[GeneralIvanov]

SirMulke

Subject promises to be interesting , perhaps the stock chips :)