Joining Data from Summary and Detail Table

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)

Joining Data from Summary and Detail Table

Author

Topic

vani_r14
Starting Member

24 Posts

Posted - 2009-02-05 : 19:51:55

Hi all

I am kind of stuck with a join. I have two tables called Mobile and Mobileactivity. Mobile table is a detail table that includes calls of all types made from a cellphone by a user and Mobileactivity tables shows all types of Recurring Charges in a summary. I have a report at the moment which shows all details from the mobile table for each user and then shows the total costs per user.... I would like to include another two rows which would show the recurring charges on a monthly basis for each user...

Description of tables

table mobile
(
acno varchar(20),
phno varchar(20),
calldate date,
plcalled varchar(30),
charge int);

table mobileactivity
(acno varchar(20),
serviceid varchar(20),
billdesc varchar(30),
chargetype varchar(30),
charge int);

Data

ACNO, PHNO, CALLDATE, PLCALLED,CHARGE

485968, 27-2128582,25-NOV-08,1234567,10

485968, 27-2128582,25-NOV-08,1234567,10

485968, 27-2128582,26-NOV-08,1234567,15

From MobileActivity Table
ACNO, SERVICEID, BILLDESC, CHARGETYPE, CHARGE

485968, 27-2128582, phone insure, Recurring, 10

485968, 27-2128582, GPRS Charge, Recurring, 50

The report currently shows all calls and costs incurred per each user and the total costs. Recurring Charges are on a monthly basis.

I would like to include those recurring charges just once in the report after the detail data of calls have been shown... can some one please help..

At this stage I have tried using subqueries and temp tables but the data from mobileactivity table repeats itself for everyrow of data from mobile table. So it is now coming up as

Each row of data from mobile table is repeated two times (which is the no of rows in mobile activity table) so the total is incorrect. I have to make it so that the data from mobile table is not repeated so many times and data from mobile activity table only comes up twice at the end.

Thanks in advance

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-05 : 19:59:42

Can you show how output should look like?