• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

SQL 2008 Query Help

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
mburk1968
Asked:
mburk1968
  • 3
  • 2
1 Solution
 
5teveoCommented:
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
 
mburk1968Author Commented:
I have color coded the records that are displaying duplicates on the spreadsheet. ARKey 005/832, 005/833, 005/847 are duplicates
0
 
5teveoCommented:
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
 
mburk1968Author Commented:
That was it! Thank you so much!
0
 
5teveoCommented:
great to hear... Good luck
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now