Temporary table help

[SACRob]

I am trying to add some extra information to my temporary table. My insert statemet includes a sum and group by so i am not able to select individual data to put into the table. (or i dont know how.) basically i want to store the the LNITMSEQ number from when the FROMSTAT=10E and when the TOSTAT<>10E in the first change colum. However i cannot do an update on my temporary and i dont really want to store this data in it's own table. Any help would be great.

 declare @svcslrtemp table
(CALLNBR char(11) not null primary key,
 holddur int not null,
 workdur int not null,
 firstchange int null,
 complete int null)
INSERT  @svcslrtemp
(CALLNBR,holddur,workdur)

Select 
A.CALLNBR,
        Sum(Case When A.FRMSTAT In('00C','30B','40M','45I','50O','70C','72P','74I','74O','74S','75R','78M')
             Then DateDiff(Minute, A.CreatDDT + A.CreateTime, B.CreatDDT + B.CreateTime)
             Else 0 End) As HoldDuration,
        Sum(Case When A.FRMSTAT IN('04S','10E','20N','50D','60A','65A')
             Then DateDiff(Minute, A.CreatDDT + A.CreateTime, B.CreatDDT + B.CreateTime)
             Else 0 End) As WorkDuration
From    SVC00210 A
        Inner Join SVC00210 B
            On A.CallNbr = B.CallNbr
            And A.LNITMSEQ = B.LNITMSEQ - 1
Where   A.TOSTAT In ('04S','10E','20N','50D','60A','65A','00C','30B','40M','45I','50O','70C','72P','74I','74O','74S','75R','78M','79U','80R','90I')
Group By A.CALLNBR
Update @svcslrtemp

 

[SACRob]

I created a permenant table to resolve this issue.

 

[AlexCuse]

it could be that you are losing scope. I forget exactly where, but I've had this happen when looping. have you tried using an actual temp table (of the #TEMP variaty)?

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[SACRob]

That is how i had it before but i was not able to do a inner join on the #table. so i created the @table. now i am not able to update the rows. I am very new to SQL so i have certain fears of mucking with the permanent table structure. I bit the bullet and just made a new table lol.