[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL 2005 Table Function Help

Posted on 2010-11-15
5
Medium Priority
?
283 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

656 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