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:
>

Help on dynamic sql results on temp table

The dynamic sql is used for link server. What I need is that the result of the dynamic sql put on a temp table. Can someone help. Im getting an error

CREATE PROCEDURE GSCLink

( @.LinkCompany nvarchar(50), @.Page int, @.RecsPerPage int )

AS

SET NOCOUNT ON

--Create temp table

CREATE

TABLE #TempTable

( ID int IDENTITY, Name nvarchar(50), AccountID int, Active bit )

INSERT INTO #TempTable (Name, AccountID, Active)

--dynamic sql

DECLARE @.sql nvarchar(4000)

SET @.sql = 'SELECT a.Name, a.AccountID, a.Active

FROM CRMSBALINK.' + @.LinkCompany + '.dbo.AccountTable a

LEFT OUTER JOIN CRM2OA.dbo.GSCCustomer b

ON a.AccountID = b.oaAccountID

WHERE oaAccountID IS NULL

ORDER BY Name ASC'

EXEC sp_executesql @.sql

--Find out the first and last record

DECLARE @.FirstRec int

DECLARE @.LastRec int

SELECT @.FirstRec = (@.Page - 1) * @.RecsPerPage

SELECT @.LastRec = (@.Page * @.RecsPerPage + 1)

--Return the set of paged records, plus an indication of more records or not

SELECT *, (SELECT COUNT(*) FROM #TempTable TI WHERE TI.ID >= @.LastRec) AS MoreRecords

FROM #TempTable

WHERE ID > @.FirstRec AND ID < @.LastRec

Error:

Msg 156, Level 15, State 1, Procedure GSCLink, Line 22

Incorrect syntax near the keyword 'DECLARE'.

change your dynamic sql as follow as ..

SET @.sql = 'Insert Into #TempTable SELECT a.Name, a.AccountID, a.Active

FROM CRMSBALINK.' + @.LinkCompany + '.dbo.AccountTable a

LEFT OUTER JOIN CRM2OA.dbo.GSCCustomer b

ON a.AccountID = b.oaAccountID

WHERE oaAccountID IS NULL

ORDER BY Name ASC'

|||Error gone... But when I ran the sprocs... No records on the temp table. Do you think I need to catch the results on the dynamic sql? How can I do that? Thanks|||

Is your link server is CRMSBALINK. Pls verify the output of SQL query alone ..

|||I got it... Thanks|||yup its CRMSBALINK. But I got it working now... Thanks anyway