asked on

SQL 2005 Table Function Help

What I am trying to do is when the [Account Nbr] = '01-2125-000000-010000-00'
the LOBtotal should be the sum 'CM','OC','GL','Tails','Mini-Tails' in the Case Statement. So I would have an LOBTotal for '01-2125-000000-010000-00' And one for '01-2125-00PTL0-010000-00' I tried using a Group by but it didn't work.

-- detail for Ceded Balances Payable
			SELECT BatchID = 'IS4WPACP' + @ToDate
					,DocNbr = 'CEDEDPREMIUM' + @ToDate 
					,[Account Nbr] = CASE 
										 WHEN LOB_LOB IN ('CM','OC','GL','Tails','Mini-Tails') THEN '01-2125-000000-010000-00'
										 WHEN LOB_LOB = 'Claims Made Plus Prepaid Tail' THEN '01-2125-00PTL0-010000-00'
										 
									  END
					, LOBtotal
					,[Description] = 'CEDEDPREMIUM'
													
			FROM dbo.LOB
			WHERE lobtob = 'Ceded Written' AND LOB_LOB IN ('CM','OC','GL','Tails','Mini-Tails','Claims Made Plus Prepaid Tail')

Open in new window

Sharath S

Do you have another column as LOBAmount and want to sum that column based on LOB_LOB?
Can you explain what you are lookng for with an example?

mburk1968

ASKER

Currently I am getting this as my Result Set.

BatchID DocNbr Account Nbr LOBtotal Description
IS4WPACP10312010,CEDEDPREMIUM10312010,01-2125-000000-010000-00,166287.96,CEDEDPREMIUM
IS4WPACP10312010,CEDEDPREMIUM10312010,01-2125-000000-010000-00,3206.88,CEDEDPREMIUM
IS4WPACP10312010,CEDEDPREMIUM10312010,01-2125-000000-010000-00,3597.65,CEDEDPREMIUM
IS4WPACP10312010,CEDEDPREMIUM10312010,01-2125-00PTL0-010000-00,2063.04,CEDEDPREMIUM

This is what I want...
Account Number is the Sum of the three
IS4WPACP10312010,CEDEDPREMIUM10312010,01-2125-000000-010000-00,-173092.49,CEDEDPREMIUM
IS4WPACP10312010,CEDEDPREMIUM10312010,01-2125-00PTL0-010000-00,2063.04,CEDEDPREMIUM

mburk1968

ASKER

Did that help? Basically I need the Lobtotal for the first account number and the second. Instead I am getting the individual totals.

ASKER CERTIFIED SOLUTION

mburk1968

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

mburk1968

ASKER

Posted code that solved my question. I used an outer query.