Solved

SQL 2005 Table Function Help

Posted on 2010-11-15
5
274 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:mburk1968
  • 4
5 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 34138031
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?
0
 

Author Comment

by:mburk1968
ID: 34138085
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


0
 

Author Comment

by:mburk1968
ID: 34138198
Did that help? Basically I need the Lobtotal for the first account number and the second. Instead I am getting the individual totals.
0
 

Accepted Solution

by:
mburk1968 earned 0 total points
ID: 34139289
Solved my Issue with the following Code.

SELECT
                               BatchID
                            ,DocNbr
                            ,[Account Nbr]
                            ,(0 - SUM(LOBtotal)) AS LOBtotal
                            ,[Description]                  
                  FROM
                              (
                                    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')
                              ) dtl
                  GROUP BY BatchID, DocNbr, [Account Nbr], [Description]
0
 

Author Closing Comment

by:mburk1968
ID: 34179051
Posted code that solved my question. I used an outer query.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu‚Ķ
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now