Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Query Problem- Urgent Help Required

Reply
Thread Tools

Query Problem- Urgent Help Required

 
 
Vp
Guest
Posts: n/a
 
      07-21-2008
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
 
Reply With Quote
 
 
 
 
Cowboy \(Gregory A. Beamer\)
Guest
Posts: n/a
 
      07-21-2008
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! |
********************************************
"Vp" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
urgent help....need urgent help on say string task.. pooja Software 0 03-03-2009 06:16 AM
please very urgent help required my_messenger2006@yahoo.com VHDL 1 01-29-2006 04:45 AM
Urgent Help Required from Gurus - Conditional databinding CGuy ASP .Net 4 10-01-2003 03:50 AM
help required urgent Dinesh Upare ASP .Net 1 08-13-2003 09:51 PM
help required urgent Dinesh Upare ASP .Net 1 08-13-2003 07:14 PM



Advertisments