Solved

SQL 2005 Table Function Help

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

803 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