Query Problem- Urgent Help Required

  • Thread starter Thread starter Vp
  • Start date Start date
V

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
 
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.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

Subscribe to my blog
http://gregorybeamer.spaces.live.com/lists/feed.rss

or just read it:
http://gregorybeamer.spaces.live.com/

********************************************
| Think outside the box! |
********************************************
 
Back
Top