query problem in crystal report

[VBmim]

Hello

I'm making a crystal report with rdc in visual basic. I have a sql server database with one table 'sales'.

This is a sketch of the database
c.no Customer year value Art Group
1 John 2002 1203.20$ Accessories
2 Smith 2001 5401.25$ Clothing
2 John 2001 500.50$ Accessories
.....

I have to make a report for all the 'Accessories' sales per customer in a year. My problem is that I have to include the sales of the year before in my report. So I have to have the following report

c.no Customer Current y. Last y.
1 John 1203.20$ 500.50%
...

I can write an sql that gives me single reports for each year (with CASE), but I want both values in one record... is this possible in crystal report?

thnx in advance for the help

greetz

Mim

 

[MikeTom]

If you use a SQL stored procedure, try creating a temporary table isnsert all current Years into the table and then update last years figure
i.e

Create table #Results(c.no int,
Customer varchar(50),
Current y. int,
Last y. int Null)

Insert into #Results
Select C.No,
Customer,
Sum(Value)
from sales
Where year = 2002
Group By c.no,Customer

Update #Resuts set last y. = Sum(value) from sales
where year = 2002
Inner join #Results on sales.c.no = #Results.c.no

select * from #results

 

[rt63]

Try this


select customer
, sum(case when year = 2002 then value else 0 end) as current_yr
, sum(case when year = 2001 then value else 0 end) as last_yr
from sales
where art_group = 'accessories'
and year in (2001,2002)
group by customer

RT