Rank: Guest
Groups: Guest
Joined: 9/17/2007
Posts: 11,670
Points: -1,200
|
Date parsed: 22/10/2007 14:20:09
Date: Mon, 22 Oct 2007 14:20:09 -0000
My sproc recieves an account # then it loops thru multiple databases
and inserts matching records into a temp table. When this process is
complete, my temp table has these fields populated. POnum,
activitydate, amount, sstatus.
I now wish to change the sstatus column, grouped by POnum, to the
sstatus with the latest activitydate and sum the amount column.
Here is sample data:
POnum, activitydate, amount, sstatus
123, 1/1/2000, 10, OPEN
123, 2/1/2001, 15, OPEN
123, 5/1/2004, -25, CLOSED
789, 2/2/2005, 40, OPEN
789, 5/2/2006, -30, VOID
I want this result:
123, $0, CLOSED
789, $10, VOID
Here is my attempt: it does not fail, it just gives same sstatus for
every POnum.
UPDATE #ooutput Set sstatus = (select top 1 xx.sstatus from #ooutput
xx where xx.POnum = POnum GROUP BY
xx.POnum,xx.activitydate,xx.sstatus order by xx.activitydate desc )
Help!!!
Thanx, Tom
|
Rank: Guest
Groups: Guest
Joined: 9/17/2007
Posts: 11,670
Points: -1,200
|
Date parsed: 22/10/2007 15:21:57
Date: Mon, 22 Oct 2007 15:21:57 -0000
Uri, thanx for input, but it does not address my problem. I need the
most current status reported. Your suggestion does not include status.
Another way of stating my quest:
1. Get each distinct POnum
2. Get sum of amount for each distinct POnum
3. Get LATEST status for each distinct POnum
TIA, Tom
|
Rank: Guest
Groups: Guest
Joined: 9/17/2007
Posts: 11,670
Points: -1,200
|
Date parsed: 22/10/2007 18:52:26
Date: Mon, 22 Oct 2007 16:52:26 +0200
Tom
select c1 ,sum(c3)
from #t
group by c1
"Tom" <wudwork@hotmail.com> wrote in message
news:1193062809.979578.254610@y27g2000pre.googlegroups.com...
> My sproc recieves an account # then it loops thru multiple databases
> and inserts matching records into a temp table. When this process is
> complete, my temp table has these fields populated. POnum,
> activitydate, amount, sstatus.
>
> I now wish to change the sstatus column, grouped by POnum, to the
> sstatus with the latest activitydate and sum the amount column.
>
> Here is sample data:
>
> POnum, activitydate, amount, sstatus
>
> 123, 1/1/2000, 10, OPEN
> 123, 2/1/2001, 15, OPEN
> 123, 5/1/2004, -25, CLOSED
> 789, 2/2/2005, 40, OPEN
> 789, 5/2/2006, -30, VOID
>
> I want this result:
> 123, $0, CLOSED
> 789, $10, VOID
>
> Here is my attempt: it does not fail, it just gives same sstatus for
> every POnum.
>
> UPDATE #ooutput Set sstatus = (select top 1 xx.sstatus from #ooutput
> xx where xx.POnum = POnum GROUP BY
> xx.POnum,xx.activitydate,xx.sstatus order by xx.activitydate desc )
>
> Help!!!
>
> Thanx, Tom
>
|
