calculation using temp tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

calculation using temp tables

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-07-09 : 18:32:05

I am trying to calculate columns based on the data comes from temp tables( #temp1, and #temp2).
But, i keep getting error:The SELECT INTO statement cannot have same source and destination tables.

what i am doiing wrong or any better idea for the calculation?

declare @start_date datetime
declare @end_date datetime
@orgid int

set @start_date = '20000101'
set @end_date = '20040701'

Select 'b' as Linecode,'1' as [group],po.org_id as [orgid],' Accounts Generated' as [ Access],
sum(isnull((case p_account_type_cd when '0' then 1 else 0 end),0)) as [ Account Holder],
sum(isnull((case p_account_type_cd when '1' then 1 else 0 end),0)) as [ Holder],
sum(isnull((case p_account_type_cd when '2' then 1 else 0 end),0)) as [ Memeber]
,isnull(count(*),0) as TotaL
into #temp1
from dbo.p_org po
join dbo.p_account pa on pa.p_id = po.p_id
where
--po.org_id IN (@orgid)and
po.p_org_p_role_cd = 'AM'
and @start_date = getdate() and @end_date= getdate()
group by all po.org_id

Union all

select 'c' as Linecode, '1' as [group],po.org_id as [orgid],' Completing Firs-time User Set-up' as [ Access],
sum(isnull((case pa.p_account_type_cd when '0' then 1 else 0 end),0)) as [account Holder],
sum(isnull((case pa.p_account_type_cd when '1' then 1 else 0 end),0)) as [ Holder],
sum(isnull((case pa.p_account_type_cd when '2' then 1 else 0 end),0)) as [ Member]
,isnull(count(*),0) as Total
into #temp2
from dbo.login l

inner join dbo.p_login pl on l.login_id = pl.login_id
inner join dbo.p_account pa on pa.p_id = pl.p_id
inner join dbo.p_org po on po.p_id = l.p_id inner join dbo.ref_p rp on l.p_id = rp.p_id inner join dbo.careplan_ref cr on rp.ref_id = cr.ref_id

where login_create_dt between '20000101' and '20040701'
and l.login_create_dt != l.login_access_dt -- Here is the mistake -- for the members who have completed first time user setup, login_create_dt != login_access_dt
-- and po.org_id IN (@orgid)
and po.p_org_p_role_cd = 'AM'
group by all po.org_id

Union all

----Calculation part using temp tables

select 'd' as Linecode, '1' as [group],a.org_id as [orgid],'Percentage of Members Completing Set-Up' as [Access],
[account Holder]= b.[Account Holder] / a.[Account Holder] ,
[Holder]= b.[Holder] / a.[Holder] ,
[Member] = b.[Member] / a.[Member] ,
[Total] = [Holder] + [Holder] + [Member]
from #temp1 a
join #temp2 b on a.org_id = b.org_id

group by all a.org_id

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-09 : 22:04:02

What are the Union Alls for??

Select blah into #temp1
--
Select blah into #temp2
--
do calculation
Select lots o blah
From #temp1 A
join #temp2 B
On ...

Corey

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-07-09 : 23:32:33

it was a mistake to putting union all.

The problem comes from the calculation part. Does this make sense to you?

select 'd' as Linecode, '1' as [group],a.org_id as [orgid],'Percentage of Members Completing Set-Up' as [Access],
[account Holder]= b.[Account Holder] / a.[Account Holder] ,
[Holder]= b.[Holder] / a.[Holder] ,
[Member] = b.[Member] / a.[Member] ,
[Total] = [Holder] + [Holder] + [Member]
from #temp1 a
join #temp2 b on a.org_id = b.org_id

group by all a.org_id

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-09 : 23:48:39

You're selecting the results into #temp1, so you can have any part of the union all that joins to #temp1 which is what your calculation part does. You need to create a third table called #temp3 and insert into that.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-07-10 : 11:25:09

Now, I am getting this error: Divide by zero error encountered.

For example: if a.[account Holder]= 0
b.[Account Holder] = 0

I can't do ( 0 divided by 0 ) ?

[account Holder]= b.[Account Holder] / a.[Account Holder]

How can I solve this problem?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-10 : 21:02:25

You will only encounter the error if a.[Account Holder] is zero. You can't divide anything by zero. You can use this to fix it:

CASE WHEN a.[Account Holder] = 0 THEN 0 ELSE b.[Account Holder]/a.[Account Holder] END

You really should not have any spaces in your column names btw.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources