Newbie to T-SQL , need help writing statement

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 2005 Forums

Transact-SQL (2005)

Newbie to T-SQL , need help writing statement

Author

Topic

fubuki99
Starting Member

10 Posts

Posted - 2009-05-03 : 00:59:07

Greetings,

I have a query statement that I'm not sure how to begin writing to get the result I want. Basically I have two tables attached:

Table 1:

COS TN_Total Telco
1 28 ACFBS
2 4486 ACFBS
4 508 ACFBS
5 2 ACFBS
7 1 ACFBS

This table consisits of the column COS (Class of Service), TN_Total (Total of Telephone numbers associated with each class of service, Class of service can range from 1-9), this example for this particular "Telco" (telephone company) has only the ones listed for its ID.

Table 2:

House # Street Telco COS TN_Instance
10011 STEEP BANK TRACE ACFBS 2 34
11325 LAKE WOODBRIDGE DR ACFBS 2 50
13707 JESS PIRTLE BLVD ACFBS 4 4
2100 SQUIRE DOBBINS DR ACFBS 2 35

This table consists of the House # (Street #), Street (Steet Name), Telco (Telephone Company ID), COS (Class of Service), and TN_Instance (Total Telephone Numbers listed for this address)

As you can see, table 1 shows that ACFBS has a class of service 2 with total TN count of 4486. That number is broken down into unique addresses that has that class of service. I only listed a few in table 2 as an example. Here is the equation:

Telco TNs subject to service fee = Telco Total TNs per COS - (Sum of Telco TNs per customer per unique location per OS ) + total Telco unique locations per customer per COS * 100

Here's an example of the equation how it should work:

ACFBS has COS 2 total of 4486

equation: 4486-(34+50+35)+3*100

summary: 4486= total COS -(34+50+35)= (Sum of all TNs for each unique address with COS of 2), 3= The number of unique addresses with COS of 2

I need to be able to get the results of this equation for each class of service mentioned, 1-9 and for different Telcos. I'm at a lost for this complex query request and need some serious help. Any thoughts or ideas would be greatly appreciated. Thanks!

Sorry for the table view, not sure why it's not appearing as structured when composing it.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-05-03 : 06:46:50

try something like this

SET NOCOUNT ON

DECLARE @T_Table1 TABLE
(
	[COS] INT,
	TN_Total INT,
	Telco VARCHAR(50)
)
INSERT INTO @T_Table1
SELECT 1, 28, 'ACFBS' UNION ALL
SELECT 2, 4486, 'ACFBS' UNION ALL
SELECT 4, 508, 'ACFBS' UNION ALL
SELECT 5, 2, 'ACFBS' UNION ALL
SELECT 7, 1, 'ACFBS'

DECLARE @T_Table2 TABLE
(
		[House#] INT,
		Street VARCHAR(50),
		Telco VARCHAR(50),
		[COS] INT,
		TN_Instance INT
)
INSERT INTO @T_Table2
SELECT 10011, 'STEEP BANK TRACE', 'ACFBS', 2, 34 UNION ALL
SELECT 11325, 'LAKE WOODBRIDGE DR', 'ACFBS', 2, 50 UNION ALL
SELECT 13707, 'JESS PIRTLE BLVD', 'ACFBS', 4, 4 UNION ALL
SELECT 2100, 'SQUIRE OBBINS DR', 'ACFBS', 2, 35

-- SELECT * FROM @T_Table2

SELECT	T1.[COS], T1.TN_Total, T1.Telco
		, T1.TN_TOTAL - (T2.TN_Instance) + (T2.CntCOS * 100) AS 'ServiceFee'
FROM	@T_Table1 T1
LEFT JOIN (
SELECT Telco, [COS], COUNT(1) AS 'CntCOS', SUM(TN_Instance) AS 'TN_Instance'
		FROM @T_Table2
		GROUP BY Telco, [COS]
		 ) T2 ON T2.[COS] = T1.[COS] AND T2.Telco = T1.Telco

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources