Problem when getting this output ?

Hi everybody,

I am facing a problem when getting this out put ?

i have a table call Trans looks like this

Tran_Id Opt_Id TranDate

1 141 01/02/2007 dd/mm/yyyy

2 542 01/06/2007 dd/mm/yyyy

and i got a table call Appointment like this

Opt_Id Arrived_Date

141 01/01/2007 dd/mm/yyyy

542 01/02/2007 dd/mm/yyyy

No i need to display the output like this

Weeks Wait No Of Transaction

0 to 3 0

3 to 10 1

morethan 10 1

In that above Example,we have to calculate the Weeks Wait = datediff(dd,Appointment.Arrived_Date,Trans.TranDate)

Any Idea ? to get that output ?

regards

suis

here it is,

Code Snippet

Create Table #trans (

[Tran_Id] int ,

[Opt_Id] int ,

[TranDate] datetime

);

Set DateFormat DMY

Insert Into #trans Values('1','141','01/02/2007');

Insert Into #trans Values('2','542','01/06/2007');

Create Table #appointment(

[Opt_Id] int ,

[Arrived_Date] datetime

);

Insert Into #appointmentValues('141','01/01/2007');

Insert Into #appointmentValues('542','01/02/2007');

Select

DescValue [Weeks Wait]

,Count(Tran_Id) [No Of Transaction]

From

(

Select

Tran_ID

,Case When DateDiff(DD,[Arrived_Date], [TranDate])/7 <4 Then '0-3'

When DateDiff(DD,[Arrived_Date], [TranDate])/7 <11 Then '4-10'

Else '> 10' End [Weeks Wait]

from

#trans T

Join #appointment A

On T.[Opt_Id]=A.[Opt_Id]

) as Data

Right Outer Join (Select '0-3' DescValue Union All Select '4-10' Union All Select '> 10') as Descdata

on data.[Weeks Wait] = Descdata.DescValue

Group By

DescValue