help on re-write w/o temp table

This works, but i'd like to know how to re-write it so that i don't use a
temp table.
select h.stkhstDate,
Sum(Case When h.stkhstRSBS = 'B' Then 1 End) as Buy,
Sum(Case When h.stkhstRSBS = 'S' Then 1 End) as Sell,
SUM(Case When h.stkhstRSXO = 'X' Then 1 End) as RSinX,
SUM(Case When h.stkhstRSXO = 'O' Then 1 End) as RSinO,
u.unvmemunvID
into #t1
from stkhst h join unvmem u
on u.unvmemcsiid = h.stkhstcsisym
where h.stkhstDate = 20050502
group by h.stkhstdate, u.unvmemunvID
order by stkhstdate
update i
set i.idxhstRSBuy = t.Buy,
i.idxhstRSSell = t.Sell,
i.idxhstRSX = t.RSinX,
i.idxhstRSO = t.RSinO
from #t1 t join idxhst i on i.idxhstidxid = t.unvmemunvid and i.idxhstDate =
t.stkhstDate
drop table #t1
thanks
kesPlease post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in message
news:0050C1C7-0D0A-4F4B-9E90-E66938DFD2FE@.microsoft.com...
> This works, but i'd like to know how to re-write it so that i don't use a
> temp table.
> select h.stkhstDate,
> Sum(Case When h.stkhstRSBS = 'B' Then 1 End) as Buy,
> Sum(Case When h.stkhstRSBS = 'S' Then 1 End) as Sell,
> SUM(Case When h.stkhstRSXO = 'X' Then 1 End) as RSinX,
> SUM(Case When h.stkhstRSXO = 'O' Then 1 End) as RSinO,
> u.unvmemunvID
> into #t1
> from stkhst h join unvmem u
> on u.unvmemcsiid = h.stkhstcsisym
> where h.stkhstDate = 20050502
> group by h.stkhstdate, u.unvmemunvID
> order by stkhstdate
> update i
> set i.idxhstRSBuy = t.Buy,
> i.idxhstRSSell = t.Sell,
> i.idxhstRSX = t.RSinX,
> i.idxhstRSO = t.RSinO
> from #t1 t join idxhst i on i.idxhstidxid = t.unvmemunvid and i.idxhstDate
> =
> t.stkhstDate
> drop table #t1
> thanks
> kes|||Thank you for responding.
i'm looking for advice only on this issue. I put the update script up for
reference and should have been more specific or phreased my question
differently.
i have a query using a group by and i don't know how to turn it into a updat
e
directly. I guess can it be done and do you know of any update query
examples that have multi agragates?
thanks
kes
"AB - MVP" wrote:

> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
>
> "Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in messag
e
> news:0050C1C7-0D0A-4F4B-9E90-E66938DFD2FE@.microsoft.com...
>
>|||Try This:
update idxhst set
i.idxhstRSBuy = (Select Count(*)
From stkhst h join unvmem u
On u.unvmemcsiid = h.stkhstcsisym
Where u.unvmemunvid = idxhst.idxhstidxid
And stkhstDate = '20050502'
And stkhstRSBS = 'B'),
i.idxhstRSSell = (Select Count(*)
From stkhst h join unvmem u
On u.unvmemcsiid = h.stkhstcsisym
Where u.unvmemunvid = idxhst.idxhstidxid
And stkhstDate = '20050502'
And stkhstRSBS = 'S'),
i.idxhstRSX = (Select Count(*)
From stkhst h join unvmem u
On u.unvmemcsiid = h.stkhstcsisym
Where u.unvmemunvid = idxhst.idxhstidxid
And stkhstDate = '20050502'
And stkhstRSXO = 'X'),
i.idxhstRSO = (Select Count(*)
From stkhst h join unvmem u
On u.unvmemcsiid = h.stkhstcsisym
Where u.unvmemunvid = idxhst.idxhstidxid
And stkhstDate = '20050502'
And stkhstRSXO = 'O')
Where idxhstDate = '20050502'
"Kurt Schroeder" wrote:

> This works, but i'd like to know how to re-write it so that i don't use a
> temp table.
> select h.stkhstDate,
> Sum(Case When h.stkhstRSBS = 'B' Then 1 End) as Buy,
> Sum(Case When h.stkhstRSBS = 'S' Then 1 End) as Sell,
> SUM(Case When h.stkhstRSXO = 'X' Then 1 End) as RSinX,
> SUM(Case When h.stkhstRSXO = 'O' Then 1 End) as RSinO,
> u.unvmemunvID
> into #t1
> from stkhst h join unvmem u
> on u.unvmemcsiid = h.stkhstcsisym
> where h.stkhstDate = 20050502
> group by h.stkhstdate, u.unvmemunvID
> order by stkhstdate
> update i
> set i.idxhstRSBuy = t.Buy,
> i.idxhstRSSell = t.Sell,
> i.idxhstRSX = t.RSinX,
> i.idxhstRSO = t.RSinO
> from #t1 t join idxhst i on i.idxhstidxid = t.unvmemunvid and i.idxhstDate
=
> t.stkhstDate
> drop table #t1
> thanks
> kes|||> i'm looking for advice only on this issue.
Did you read the link I posted?|||thanks,
i was afraid of that. there are times when just going low rent is the better
option.
my script takes less that 1 sec, but i just wanted to checkout the
alternative.
thanks
kes
"CBretana" wrote:
> Try This:
> update idxhst set
> i.idxhstRSBuy = (Select Count(*)
> From stkhst h join unvmem u
> On u.unvmemcsiid = h.stkhstcsisym
> Where u.unvmemunvid = idxhst.idxhstidxid
> And stkhstDate = '20050502'
> And stkhstRSBS = 'B'),
> i.idxhstRSSell = (Select Count(*)
> From stkhst h join unvmem u
> On u.unvmemcsiid = h.stkhstcsisym
> Where u.unvmemunvid = idxhst.idxhstidxid
> And stkhstDate = '20050502'
> And stkhstRSBS = 'S'),
> i.idxhstRSX = (Select Count(*)
> From stkhst h join unvmem u
> On u.unvmemcsiid = h.stkhstcsisym
> Where u.unvmemunvid = idxhst.idxhstidxid
> And stkhstDate = '20050502'
> And stkhstRSXO = 'X'),
> i.idxhstRSO = (Select Count(*)
> From stkhst h join unvmem u
> On u.unvmemcsiid = h.stkhstcsisym
> Where u.unvmemunvid = idxhst.idxhstidxid
> And stkhstDate = '20050502'
> And stkhstRSXO = 'O')
> Where idxhstDate = '20050502'
>
> "Kurt Schroeder" wrote:
>|||How long does the "Right" way take?
"Kurt Schroeder" wrote:
> thanks,
> i was afraid of that. there are times when just going low rent is the bett
er
> option.
> my script takes less that 1 sec, but i just wanted to checkout the
> alternative.
> thanks
> kes
> "CBretana" wrote:
>|||yes
"AB - MVP" wrote:

> Did you read the link I posted?
>
>|||Usingthe update you provided i modified it so that it would run (sub idxhst
for i.)
update idxhst set
idxhst.idxhstRSBuy = (Select Count(*)
From stkhst h join unvmem u
On u.unvmemcsiid = h.stkhstcsisym
Where u.unvmemunvid = idxhst.idxhstidxid
And stkhstDate = '20050502'
And stkhstRSBS = 'B'),
idxhst.idxhstRSSell = (Select Count(*)
From stkhst h join unvmem u
On u.unvmemcsiid = h.stkhstcsisym
Where u.unvmemunvid = idxhst.idxhstidxid
And stkhstDate = '20050502'
And stkhstRSBS = 'S'),
idxhst.idxhstRSX = (Select Count(*)
From stkhst h join unvmem u
On u.unvmemcsiid = h.stkhstcsisym
Where u.unvmemunvid = idxhst.idxhstidxid
And stkhstDate = '20050502'
And stkhstRSXO = 'X'),
idxhst.idxhstRSO = (Select Count(*)
From stkhst h join unvmem u
On u.unvmemcsiid = h.stkhstcsisym
Where u.unvmemunvid = idxhst.idxhstidxid
And stkhstDate = '20050502'
And stkhstRSXO = 'O')
Where idxhst.idxhstDate = '20050502'
it takes on average 3 times as long to run. In my case the "Right" answer is
my origional query which takes less than a second on all runs.
thank you for responding.
kes
"CBretana" wrote:
> How long does the "Right" way take?
> "Kurt Schroeder" wrote:
>|||We had the same problem as well sometime back.
[ select h.stkhstDate,
Sum(Case When h.stkhstRSBS = 'B' Then 1 End) as Buy,
Sum(Case When h.stkhstRSBS = 'S' Then 1 End) as Sell,
SUM(Case When h.stkhstRSXO = 'X' Then 1 End) as RSinX,
SUM(Case When h.stkhstRSXO = 'O' Then 1 End) as RSinO,
u.unvmemunvID ]
If the above select just gets one record, then you might be better of in
storing the values in a Varibale as
opposed to storing it in a temp table and then updating them.
Gopi
"Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in message
news:6A640FE1-99F0-4FFD-A96C-13B17D2D5BC6@.microsoft.com...
> Usingthe update you provided i modified it so that it would run (sub
> idxhst
> for i.)
> update idxhst set
> idxhst.idxhstRSBuy = (Select Count(*)
> From stkhst h join unvmem u
> On u.unvmemcsiid = h.stkhstcsisym
> Where u.unvmemunvid = idxhst.idxhstidxid
> And stkhstDate = '20050502'
> And stkhstRSBS = 'B'),
> idxhst.idxhstRSSell = (Select Count(*)
> From stkhst h join unvmem u
> On u.unvmemcsiid = h.stkhstcsisym
> Where u.unvmemunvid = idxhst.idxhstidxid
> And stkhstDate = '20050502'
> And stkhstRSBS = 'S'),
> idxhst.idxhstRSX = (Select Count(*)
> From stkhst h join unvmem u
> On u.unvmemcsiid = h.stkhstcsisym
> Where u.unvmemunvid = idxhst.idxhstidxid
> And stkhstDate = '20050502'
> And stkhstRSXO = 'X'),
> idxhst.idxhstRSO = (Select Count(*)
> From stkhst h join unvmem u
> On u.unvmemcsiid = h.stkhstcsisym
> Where u.unvmemunvid = idxhst.idxhstidxid
> And stkhstDate = '20050502'
> And stkhstRSXO = 'O')
> Where idxhst.idxhstDate = '20050502'
> it takes on average 3 times as long to run. In my case the "Right" answer
> is
> my origional query which takes less than a second on all runs.
> thank you for responding.
> kes
> "CBretana" wrote:
>