Coding help - 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)

Coding help

Author

Topic

wmorrison49
Starting Member

2 Posts

Posted - 2007-10-02 : 13:22:43

Hi everyone,
I am new to SQL, just learning and I heard this was a good place to get help. Here is the deal. I am trying to pull sum(earned) and sum(written) from a table called actgpremiumdetail. I am trying to pull FILLR1 and RPT0AGT0NR (those are zeroes) from the table PMSP0200. This tables are in different data warehouses and I don't know how to join them. Here is what I have so far:

quote:
select sum(earned), sum(written),
dbo.PMSP0200.FILLR1 as Agent1,
dbo.PMSP0200.RPT0AGT0NR as Agent2
from actgpremiumdetail
inner join dbo.PMSP0200 on ActgPremiumDetail.policy#=dbo.PMSP0200.SYMBOL+dbo.PMSP0200.POLICY0NUM+dbo.PMSP0200.MODULE

Any help would be greatly appreciated. Thanks a lot.

wmorrison49

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-02 : 13:53:23

A lot of guess work and assumptions on this one, as I don't know how to join your tables either. You'd ahve to post some sample table and tell me which fields link to what, but try this for now

select sum(earned), sum(written),
dbo.PMSP0200.FILLR1 as Agent1,
dbo.PMSP0200.RPT0AGT0NR as Agent2
from actgpremiumdetail act
inner join
dbo.PMSP0200 pms
on
act.[policy#] + dbo.PMS.SYMBOL = dbo.PMS.POLICY0NUM + dbo.PMS.MODULE

group by pms.fillr1,pms.rpt0agtonr

Jim

wmorrison49
Starting Member

2 Posts

Posted - 2007-10-02 : 14:04:27

Well, it didn't work but I hardly gave you any parameters. I don't really know what the hell I am doing. Thanks anyways

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-02 : 14:43:12

Just send some sample data from each table and we may still be able to figure this out. What error did you get when you tried to run my code?

Jim

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-03 : 06:31:18

This is probably what you really want

select sum(earned), sum(written),
pms.FILLR1 as Agent1,
pms.RPT0AGT0NR as Agent2
from actgpremiumdetail act
inner join
dbo.PMSP0200 pms
on
act.[policy#] = PMS.POLICY0NUM
and act.symbol = PMS.MODULE

group by pms.fillr1,pms.rpt0agtonr

Kristen
Test

22859 Posts

Posted - 2007-10-03 : 06:45:17

This maybe?


select sum(APD.earned), 
       sum(APD.written),
       PMS.FILLR1 as Agent1,
       PMS.RPT0AGT0NR as Agent2
from actgpremiumdetail AS APD
       inner join [OtherDataWarehouse].dbo.PMSP0200 AS PMS
           on APD.[policy#] = PMS.SYMBOL+PMS.POLICY0NUM+PMS.MODULE
GROUP BY PMS.FILLR1, PMS.RPT0AGT0NR

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-03 : 06:46:27

"Well, it didn't work"

You'll need to tell us what error you got ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources