Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   ASP .Net (http://www.velocityreviews.com/forums/f29-asp-net.html)
-   -   Query Problem- Urgent Help Required (http://www.velocityreviews.com/forums/t626823-query-problem-urgent-help-required.html)

 Vp 07-21-2008 12:20 PM

Query Problem- Urgent Help Required

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\) 07-21-2008 02:41 PM

Re: Query Problem- Urgent Help Required

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/

********************************************
| Think outside the box! |
********************************************
"Vp" <vishnu.baroda@gmail.com> wrote in message
> 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

 All times are GMT. The time now is 01:16 PM.