Query Problem- Urgent Help Required

Discussion in 'ASP .Net' started by Vp, Jul 21, 2008.

  1. Vp

    Vp Guest

    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
    Vp, Jul 21, 2008
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:...
    > 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\), Jul 21, 2008
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Dinesh Upare

    help required urgent

    Dinesh Upare, Aug 13, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    1,006
    Carl Prothman [MVP]
    Aug 13, 2003
  2. Replies:
    1
    Views:
    503
    kameshwar_kaushik
    Jan 29, 2006
  3. Dinesh Upare

    help required urgent

    Dinesh Upare, Aug 13, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    338
    Tommy
    Aug 13, 2003
  4. CGuy
    Replies:
    4
    Views:
    565
    Saravana
    Oct 1, 2003
  5. Rob
    Replies:
    3
    Views:
    435
Loading...

Share This Page