Query Problem- Urgent Help Required

[Vp]

Dear all

I have two tables
Table T1
ID Name
1 A
2 B
3 C
4 D
5 E
Table T2
ID X1 Stat
1 1 F
1 2 F
1 3 F
1 4 F
2 1 F
2 2
2 3 F
2 4 F
3 1 F
3 2 F
3 3

I want output like this
ID Name Count of First three
record of T2 having
F Stat
1 A 3 F
2 B 2 F
3 C 2 F

He Third column display count for continus F coming from top to
bottom, if anything missed inbetween not counted in output

thanx

 

[Cowboy \(Gregory A. Beamer\)]

This is not the easiest query to accomplish, as you end up having to pull
out values and pruning down to 3 or less per unit. If you are working with a
single unit, this is fairly easy. WARNING: Crude code sample ahead:

CREATE TABLE #Temp
(
ID int
, X1 int
, stat char(1)
)

insert into #Temp (ID, X1, Stat)
SELECT top 3 * from table2
where ID = 1
and stat is not null

select t1.ID
, t1.[Name]
, Sum(t2.X1)
, t2.stat
from table1 t1
join #Temp t2
on t1.ID = t2.ID
where t1.ID = 1
group by t1.id, t1.name, t2.stat

Drop table #Temp

This is provided the stat is always F. If not, you end up not aggregating
properly as soon as you add stat to the mix.

A better way to accomplish this is to create the aggregates as new records
are introduced. A bit more weight on insert, but you greatly improve query
times.

If you MUST do it for all IDs at runtime, I would consider a CLR function
personally, as you have full control over the way you loop through and can
start creating a result table on the fly. I believe this would be faster.
ONe caveat is SQL Server 2005 or greater.