?
Solved

SQL 2005 Table Function Help

Posted on 2010-11-15
5
Medium Priority
?
282 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 41

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

752 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