Solved

SQL 2008 Query Help

Posted on 2014-04-02
6
255 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
  • 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 500 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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

760 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

19 Experts available now in Live!

Get 1:1 Help Now