?
Solved

SQL 2008 Query Help

Posted on 2014-04-02
6
Medium Priority
?
269 Views
Last Modified: 2014-04-02
I have the following query "see below" That query returns duplicates. How do I combine the records that are dupes into a single row? I have included the result set...

ALTER PROCEDURE [dbo].[PXQ6380a] (@AsMM as varchar(2),
				            @AsYYYY as varchar(4),
				            @InsGrpNme as varchar(100),
				            @LicNbr as varchar(100),
/*C04*/						@gbloptState varchar(4))
AS
	SET NOCOUNT ON

	Declare @AsOfPriorMonth as datetime
	Declare @ParmDate as datetime

	SELECT @ParmDate = dbo.LastDayInMonth(Convert(datetime, @AsMM+'/01/'+@AsYYYY))
	SELECT @AsOfPriorMonth = dbo.LastDayInMonth(DATEADD(month, -1, @ParmDate))

	SELECT  Insured,
			GroupFirmName,
			Agent,
			Producer,
			LoanCheckbox,
			License,
			ARKey,
			BilledDate,
			Policy,
			PolEffDate,
			ExpDate,
			TransCovEffDate,
			CoAcctgDate,
			DueDate,
			EntryDate,
			AmtType,
			[Premium Billed],
			'1' as HeaderConstant,
			CONVERT(money, SUM(ISNULL([Premium Received],0))) as [Premium Received],
			CONVERT(money, ([Premium Billed] + SUM(ISNULL([Premium Received],0)))) AS [Premium Outstanding]
	FROM
	(	
		SELECT Insured =
				CASE 
					WHEN Insured.FirstName = ' ' THEN Insured.LastName
					ELSE Insured.LastName + ', ' + Insured.FirstName
				END, 
			Insured.GroupFirmName as GroupFirmName,
			Agent =
				CASE 
					WHEN Agent.FirstName = ' ' THEN Agent.LastName
					ELSE Agent.LastName + ', ' + Agent.FirstName
				END, 	
			Producer =
				CASE 
					WHEN Producer.FirstName = ' ' THEN Producer.LastName
					ELSE Producer.LastName + ', ' + Producer.FirstName
				END, 
			LoanCheckbox = 
				CASE wellandp.ARItem.LoanCheckbox 
					WHEN 'Y' THEN 'Y' 
					ELSE 'N' 
				END,
			SUBSTRING(wellandp.ARItem.ReferKey, 2, LEN(wellandp.ARItem.ReferKey)) AS License, 
			wellandp.ARItem.ARKey,
			BilledDate =
				CASE 
/* C02*/				WHEN wellandp.ARItem.AmtType = 'Prem70' AND ARDocuments.ChangedDate IS NULL AND ARDocuments.SystemDate IS NULL THEN Prem30BilledDateFile_1.BilledDate 
					WHEN ARDocuments.ChangedDate > ARDocuments.SystemDate THEN ARDocuments.ChangedDate
					WHEN ARDocuments.ChangedDate is null THEN ARDocuments.SystemDate
					ELSE ARDocuments.SystemDate
				END,
			wellandp.ARItem.GrossAmt AS [Premium Billed],
			[Premium Received] =
				CASE 
--           					WHEN ISNULL(CashItemFile.DisbRecptDate, CONVERT(datetime, '1/1/2999')) < @AsOfPriorMonth THEN wellandp.ARItem_OffAmounts.OffAmounts
					WHEN SUBSTRING(wellandp.ARItem_OffPeriods.OffPeriods,1,4)+SUBSTRING(wellandp.ARItem_OffPeriods.OffPeriods,6,2) <=  Convert(varchar(4), Year(@AsOfPriorMonth)) + CASE WHEN Convert(varchar(2), Month(@AsOfPriorMonth)) < 10 THEN '0' + Convert(varchar(2), Month(@AsOfPriorMonth)) ELSE Convert(varchar(2), Month(@AsOfPriorMonth)) END THEN wellandp.ARItem_OffAmounts.OffAmounts
					ELSE 0 
				END,
			CashItemFile.DisbRecptDate, 
			wellandp.PolicyGeneral.PolNo AS Policy, 
			wellandp.PolicyGeneral.PolEffDate, 
			wellandp.PolicyGeneral.ExpDate,
			wellandp.PolicyScollect.TransCovEffDate, 
			wellandp.PolicyScollect.CoBookDate, 
/* *************************************************** */
			CoAcctgDate = 
				CASE	WHEN PolicyStat.CoAcctgDate IS NOT NULL THEN PolicyStat.CoAcctgDate
					WHEN wellandp.PolicyScollect.CoBookDate >= wellandp.PolicyScollect.TransCovEffDate THEN wellandp.PolicyScollect.CoBookDate
					ELSE wellandp.PolicyScollect.TransCovEffDate
				END,
--			PolicyStat.CoAcctgDate,
/* *************************************************** */
			wellandp.ARItem.DueDate,
			wellandp.ARItem.Entrydate,
			wellandp.ARItem.AmtType
		FROM	wellandp.ARItem
--           			INNER JOIN wellandp.ARItem_ActivityKey ON wellandp.ARItem.ARKey = wellandp.ARItem_ActivityKey.ARKey 
			INNER JOIN wellandp.PolicyScollect ON wellandp.ARItem.PolScolKey = wellandp.PolicyScollect.PolScolKey 
/*C01*/		INNER JOIN (SELECT DISTINCT ActivityKey, CoAcctgDate from dbo.vw_PolicyStat WHERE TOB IN ('DM', 'DR')) PolicyStat ON PolicyStat.ActivityKey = wellandp.PolicyScollect.ActivityKey 
			INNER JOIN wellandp.PolicyGeneral ON wellandp.PolicyScollect.PolGenKey = wellandp.PolicyGeneral.PolGenKey 
			INNER JOIN wellandp.ReferMaster Insured ON wellandp.ARItem.ReferKey = Insured.ReferKey 
			LEFT OUTER JOIN wellandp.ARItem_OffKeys ON wellandp.ARItem.ARKey = wellandp.ARItem_OffKeys.ARKey 
			LEFT OUTER JOIN wellandp.ARItem_OffAmounts ON wellandp.ARItem_OffKeys.ARItem_OffKeys_ID = wellandp.ARItem_OffAmounts.ARItem_OffAmounts_ID 
  		    LEFT OUTER JOIN wellandp.ARItem_OffPeriods ON wellandp.ARItem_OffPeriods.ARItem_OffPeriods_ID = wellandp.ARItem_OffKeys.ARItem_OffKeys_ID
/*C03*/  --		LEFT OUTER JOIN wellandp.ARDocuments ON wellandp.ARDocuments.SeqNo = wellandp.ARItem.ReferenceNo
/*C03*/		LEFT OUTER JOIN dbo.vw_ARDocuments_INV AS ARDocuments ON	ARDocuments.SeqNo = wellandp.ARItem.ReferenceNo AND 
/*C05*/																	ARDocuments.OptState = @Gbloptstate			
			LEFT OUTER JOIN wellandp.ReferMaster Agent ON 'G' + wellandp.PolicyGeneral.AgentCode = Agent.ReferKey 
			LEFT OUTER JOIN wellandp.ReferMaster Producer ON 'P' + wellandp.PolicyGeneral.ProducerCode = Producer.ReferKey 
			LEFT OUTER JOIN wellandp.CashItem_ARKeys ON wellandp.ARItem_OffKeys.OffKeys = wellandp.CashItem_ARKeys.ARKeys 
			LEFT OUTER JOIN wellandp.CashItem AS CashItemFile ON wellandp.CashItem_ARKeys.CashItemKey = CashItemFile.CashItemKey
/* C02*/ /*C03*/		LEFT OUTER JOIN dbo.vw_Prem30_BilledDate AS Prem30BilledDateFile_1 ON wellandp.PolicyScollect.PolNo = Prem30BilledDateFile_1.Policy AND
/* C02*/									wellandp.PolicyScollect.PolEffDate = Prem30BilledDateFile_1.PolEffDate AND
/* C02*/									SUBSTRING(wellandp.ARItem.ReferKey,2,LEN(wellandp.ARItem.ReferKey)) = Prem30BilledDateFile_1.License AND
/* C02*/									wellandp.PolicyScollect.ActivityKey  = Prem30BilledDateFile_1.ActivityKey
		WHERE	 ISNULL(wellandp.ARItem.ToARKey, ' ') = ' ' 
		AND ((wellandp.ARItem.Status = 'C' 
		AND wellandp.ARItem.OpenAmt = 0) 
		OR (wellandp.ARItem.Status = 'O')) 
		AND wellandp.ARItem.GrossAmt > 0  
		AND
/*C04*/		--	 wellandp.ARItem.ArKey LIKE @gbloptState + '%' AND
/*C04*/		substring(wellandp.ARItem.ARKey, 1, charindex('/', wellandp.ARItem.ARKey)) = @gbloptState  
        AND
        	(@InsGrpNme = ' All Insureds' OR @LicNbr = SUBSTRING(Insured.ReferKey,2,LEN(Insured.ReferKey))  OR @InsGrpNme = Insured.GroupFirmName)
		) AS ARFile
	WHERE	ARFile.CoAcctgDate <=  @AsOfPriorMonth
	GROUP BY 	Insured,
			GroupFirmName,
			Agent,
			Producer,
			LoanCheckbox,
			License,
			ARKey,
			BilledDate,
			[Premium Billed],
			Policy,
			PolEffDate,
			ExpDate,
			TransCovEffDate,
			CoAcctgDate,
			DueDate,
			EntryDate,
			AmtType
	HAVING	([Premium Billed] + SUM(ISNULL([Premium Received],0))) > 0
	ORDER BY	Insured

Open in new window

Results.xlsx
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
  • 3
  • 2
6 Comments
 
LVL 8

Expert Comment

by:5teveo
ID: 39972142
Upon quick review I cannot see which rows you expect to be duplicates? I can guess but
for clarification purposes...  

Which rows of your spreadsheet are Duplicates? Each row appears to have unique data hence SQL group by will show each unique grouping.
0
 

Author Comment

by:mburk1968
ID: 39972157
I have color coded the records that are displaying duplicates on the spreadsheet. ARKey 005/832, 005/833, 005/847 are duplicates
0
 
LVL 8

Accepted Solution

by:
5teveo earned 2000 total points
ID: 39972217
The 'CoAcctgDate' is included in your Group by statement and it has unique values in it.

if you remove this column from Query you will get groupings/tallys  based upon other columns which are all same but you will not include 'CoAcctgDate'.

Maybe try removing column and prove that query groups as expected... if yes.. then question is do you need column?
0
 

Author Closing Comment

by:mburk1968
ID: 39972228
That was it! Thank you so much!
0
 
LVL 8

Expert Comment

by:5teveo
ID: 39972241
great to hear... Good luck
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

762 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