comparison query same field name multiple times

[MLHab306]

Hi

I am trying to set up a comparison query.

for example date range

1/1/03 to 12/31/03
1/1/04 to 12/31/04
1/1/05 to 12/31/05

thanks for your help.

Mark

 

[SQLSister]

Perhaps a bit more information would be helpful here. What result are you trying to achieve? To find the records between two dates? To find the records for a particular year? Please give us sample data and sample results.

Questions about posting. See faq183-874

 

[ceh4702]

If the people have some other way to identify them you might try that. Name does not make a good unique identifier or Index or Primary Key.

Distinct will eliminate duplicates.

If you do not like my post feel free to point out your opinion or my errors.

 

[MLHab306]

What I am trying to do is do like a year end cpmparison report, comparing the same fields but with different date ranges in the same report.

Date range number of products sold total sales
1/1/02 to 12/31/02 1904 $100,403
1/1/03 to 12/31/03 2310 $98,000
1/1/04 to 12/31/04 2094 $200,000
etc

I would like to be able to compare date ranges whether by month or by ?

 

[repete]

try select year(proddate) as prodyear, sum(prodqty) as qtytot, sum(prodvalue) as valtot from temp01 group by prodyear i have included some code below to show how that would be used

create cursor temp01 (proddate d, prodref c(7), prodqty n(6,0), prodprice n(6,2), prodvalue n(10,2))
index on proddate tag proddate

for i = -100 to -50
	insert into temp01 (proddate, prodref, prodqty, prodprice, prodvalue)	;
		values (date()+i, "PROD"+padl(i,3,"0"), 2, 2.50, 5.00)

	insert into temp01 (proddate, prodref, prodqty, prodprice, prodvalue)	;
		values (date()+i+365, "PROD"+padl(i,3,"0"), 1, 1.50, 1.50)

	insert into temp01 (proddate, prodref, prodqty, prodprice, prodvalue)	;
		values (date()+i+365+365, "PROD"+padl(i,3,"0"), 3, 3.00, 9.00)
endfor

select year(proddate) as prodyear, sum(prodqty) as qtytot, sum(prodvalue) as valtot from temp01 group by prodyear

Pete Bloomfield
Down Under

 

[SQLSister]

YOu could use a UNion query. Create a select query for each data range you want inthe final report and then union them together with Union All.

Questions about posting. See faq183-874