Solved

SQL 2005 Table Function Help

Posted on 2010-11-15
5
275 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 59
How to query LOCK_ESCALATION 4 40
Problem with SqlConnection 4 159
Find data in a column which is not in  a date format 29 0
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 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

18 Experts available now in Live!

Get 1:1 Help Now