Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Query help

Posted on 2010-01-08
20
Medium Priority
?
320 Views
Last Modified: 2012-05-08
I'm trying to modify a query where I have a policy information stored. Each time a transaction is made a new record is made.  I want to return the row with the greatest transaction date.

Let's assume table name is "policy" and fields are policy-number, transaction-date, client-name, policy-effective-date, broker-name.

What does the SQL look like to return one row per policy with the latest transaction date?
0
Comment
Question by:tobin46
  • 9
  • 5
  • 4
  • +1
20 Comments
 
LVL 3

Expert Comment

by:ScottParker
ID: 26213779
coulndt you do something like...

select policy-number, max(transaction-date) from policy group by policy-number
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26213822
I guess you want all columns from policy table so try like this:
select * from policy a
where [transaction-date] = (select max([transaction-date] from policy where [policy-number] = a.[policy-number])

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 26213847
Or like this:
select * from policy a
inner join (select [policy-number], max([transaction-date]) mtdate from policy group by [policy-number]) b on a.[policy-number] = b.[policy-number] and a.[transaction-date] = b.mtdate

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 41

Expert Comment

by:ralmada
ID: 26213861
Sorry above should be like this:
select a.* from policy a 
inner join (select [policy-number], max([transaction-date]) mtdate from policy group by [policy-number]) b on a.[policy-number] = b.[policy-number] and a.[transaction-date] = b.mtdate

Open in new window

0
 
LVL 1

Author Comment

by:tobin46
ID: 26213865
I tried that but it then tells me I must include all fields in aggregate function.  Then I did that but still returns multiple records for a policy number.

There are joins in the procedure q would this matter?
0
 
LVL 3

Expert Comment

by:ScottParker
ID: 26213919
please post the exact statement you used.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26214022
I guess you have not tried mine: (see comments above)
select * from policy a  where [transaction-date] = (select max([transaction-date] from policy where [policy-number] = a.[policy-number])
or like this:
select a.* from policy a   inner join (select [policy-number], max([transaction-date]) mtdate from policy group by [policy-number]) b on a.[policy-number] = b.[policy-number] and a.[transaction-date] = b.mtdate
 
 
0
 
LVL 1

Author Comment

by:tobin46
ID: 26222614
I'm going to post the entire procedure when I get back in front of a pc.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26225701
Another possibility if you're in SQL 2005/2008
select * from (
	select *, row_number() over (partition by [policy-number] order by [transaction-date] desc) rn from policy 
)a  
where rn = 1

Open in new window

0
 
LVL 1

Author Comment

by:tobin46
ID: 26246427
Here is the entire procedure...I inherited this from someone who left the company.  I'm a novice at best with SQL.  What this procedure does is return basic policy information but returns many rows per policy.  As I mentioned earlier I'd like this to return one row per policy with the most recent transaction date which is POLICY.POLICY_DATE_TIME.  I included in via TXT file.
Policy.txt
0
 
LVL 1

Author Comment

by:tobin46
ID: 26273893
Ralmada - I see the logic behind what you're doing - where would I insert that into the procedure?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26274298
Here's your procedure using row_number()
ALTER PROCEDURE [dbo].[GetBasicPolicyInfo] 
	@Carrier						INT,		
	@StartDate						DATETIME,	-- Start pulling transactions starting at this date.
	@EndDate						DATETIME	-- Stop pulling transaction ending on this date.
AS
BEGIN

	SET NOCOUNT ON;
	;WITH CTE as (
	SELECT  POLICY.POLICY_NUMBER AS PolNum 
			,(POLICY.POLICY_DATE_TIME) AS TransDate 
			,POLICY.POL_EFF_DATE AS PolEffDate 
			,POLICY.POL_EXP_DATE AS PolExpDate 
			,POLICY.CHANGE_EFF_DATE AS TransEffDate 
			,REPLACE(CASE PB_DETAIL.CHAR_01_02 WHEN  1 THEN 'MWC' + POLICY.POL_ALT_ID + '00' ELSE POLICY.POL_ALT_ID END, '-', '') AS AltPolNum
			,REPLACE(CASE PB_DETAIL.CHAR_01_02 WHEN  1 THEN 'MWC' + POLICY.PRIOR_POLICY + '00' ELSE POLICY.PRIOR_POLICY END, '-', '') AS PriorPolNum
			,CASE  
				WHEN POLICY.POL_TRANS_ID = '-' 
					THEN 
						CASE POLICY.NEW_OR_RENEWAL 
							WHEN 1 
								THEN 'NB' 
							ELSE 
								CASE 
									WHEN POLICY.PRIOR_POLICY IS NULL 
										THEN 'NB' 
									ELSE 'RE' 
								END 
						END
				WHEN POLICY.UNDERWRITER_CANCEL <> 0
					THEN 'CA' 
				ELSE 'EN' 
			 END AS TransType
			,POLICY.AGENCY_ID AS ProdID
			,POLICY.CLIENT_NUMBER AS SDID
			,AGENCY.AGENCY_NAME AS ProdName
			,AGENCY.AGENCY_ADDR_1 AS ProdAddr1
			,AGENCY.AGENCY_ADDR_2 AS ProdAddr2
			,AGENCY.AGENCY_CITY AS ProdCity
			,AGENCY.AGENCY_STATE AS ProdState
			,AGENCY.AGENCY_ZIP_CODE AS ProdZip
			,CLIENT.LNAME1 AS SDName  -- Insured Name
			,CASE WHEN ADDRESS.SEQUENCE_NUMBER = 1 THEN 'Y' ELSE 'N' END AS SDMailingAddr
			,ADDRESS.ADDRESS1 AS SDAddr1 
			,ADDRESS.ADDRESS2 AS SDAddr2
			,ADDRESS.CITY AS SDCity
			,ADDRESS.STATE AS SDState 
			,ADDRESS.ZIPCODE AS SDZip
			,CLIENT.TAX_ID1 AS SDFEIN 
			,PB_DETAIL.CHAR_DESC_01 AS BureauID
			,CASE WHEN PB_DETAIL.CHAR_01_02 = 1 THEN '11111' ELSE '12345' END AS InsCarrier 
			,POLICY.POL_TRANS_ID AS EndNum
			,WC_RATING.EXP_MOD AS ExpRated
			,WC_RATING.EXP_MOD_FCTR AS ExpModFactor
			,WC_RATING.MINIMUM_PREM AS MinPrem
			,WC_RATING.MOD_PREM_INC_CR AS PolEstStandardPrem
			,POLICY.BILLED_PREMIUM AS PolDepPrem
			,WC_RATING.PREM_DISC_PREM AS PremDisc
			,CASE WC_RATING.LIAB_LMT WHEN 1 THEN 100000 WHEN 2 THEN 500000 WHEN 3 THEN 1000000 ELSE 0 END AS AccLiabLimit
			,CASE WC_RATING.LIAB_LMT WHEN 1 THEN 500000 WHEN 2 THEN 500000 WHEN 3 THEN 1000000 ELSE 0 END AS PolLiabLimit
			,CASE WC_RATING.LIAB_LMT WHEN 1 THEN 100000 WHEN 2 THEN 500000 WHEN 3 THEN 1000000 END AS EmpLiabLimit
			,TypeOfBusText.DESCRIPTION AS LegalNatureOfIns
			,row_number() over (partition by POLICY.POLICY_NUMBER order by POLICY_DATE_TIME desc) rn
	FROM WC_RATING 
			INNER JOIN
				  (SELECT CODE, DESCRIPTION
					FROM EDIT_SHORT_CODE
					WHERE (NAME = 'TYPE_OF_BUS') AND (TBNAME = 'WC_RATING')) AS TypeOfBusText 
				ON WC_RATING.TYPE_OF_BUS = TypeOfBusText.CODE 
			RIGHT OUTER JOIN
					POLICY 
			LEFT OUTER JOIN
					PB_DETAIL 
				ON POLICY.POLICY_DATE_TIME = PB_DETAIL.POLICY_DATE_TIME 
					AND POLICY.POLICY_NUMBER = PB_DETAIL.POLICY_NUMBER
				ON WC_RATING.POLICY_NUMBER = POLICY.POLICY_NUMBER 
					AND WC_RATING.POLICY_DATE_TIME = POLICY.POLICY_DATE_TIME 
			LEFT OUTER JOIN
					AGENCY 
				ON POLICY.AGENCY_ID = AGENCY.AGENCY_ID 
			LEFT OUTER JOIN
					ADDRESS 
			INNER JOIN
					CLIENT 
				ON ADDRESS.CLIENT_NUMBER = CLIENT.CLIENT_NUMBER 
					AND ADDRESS.SEQUENCE_NUMBER = CLIENT.ADDRESS_NUMBERS
				ON POLICY.CLIENT_NUMBER = CLIENT.CLIENT_NUMBER
	WHERE (POLICY.QUOTE_IND IS NULL)
		AND (POLICY.UNDERWRITER_CANCEL = 0) 
                AND POLICY.POLICY_NUMBER Like '10-%-08' 
		AND PB_DETAIL.CHAR_01_02 = @Carrier 
		AND POLICY.POLICY_DATE_TIME > @StartDate 
		AND POLICY.POLICY_DATE_TIME <= @EndDate 
	) 
	select * from CTE
	where rn = 1		

END

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 26274316
Try this using the first alternative I've suggested
ALTER PROCEDURE [dbo].[GetBasicPolicyInfo] 
	@Carrier						INT,		
	@StartDate						DATETIME,	-- Start pulling transactions starting at this date.
	@EndDate						DATETIME	-- Stop pulling transaction ending on this date.
AS
BEGIN

	SET NOCOUNT ON;
	SELECT  POLICY.POLICY_NUMBER AS PolNum 
			,(POLICY.POLICY_DATE_TIME) AS TransDate 
			,POLICY.POL_EFF_DATE AS PolEffDate 
			,POLICY.POL_EXP_DATE AS PolExpDate 
			,POLICY.CHANGE_EFF_DATE AS TransEffDate 
			,REPLACE(CASE PB_DETAIL.CHAR_01_02 WHEN  1 THEN 'MWC' + POLICY.POL_ALT_ID + '00' ELSE POLICY.POL_ALT_ID END, '-', '') AS AltPolNum
			,REPLACE(CASE PB_DETAIL.CHAR_01_02 WHEN  1 THEN 'MWC' + POLICY.PRIOR_POLICY + '00' ELSE POLICY.PRIOR_POLICY END, '-', '') AS PriorPolNum
			,CASE  
				WHEN POLICY.POL_TRANS_ID = '-' 
					THEN 
						CASE POLICY.NEW_OR_RENEWAL 
							WHEN 1 
								THEN 'NB' 
							ELSE 
								CASE 
									WHEN POLICY.PRIOR_POLICY IS NULL 
										THEN 'NB' 
									ELSE 'RE' 
								END 
						END
				WHEN POLICY.UNDERWRITER_CANCEL <> 0
					THEN 'CA' 
				ELSE 'EN' 
			 END AS TransType
			,POLICY.AGENCY_ID AS ProdID
			,POLICY.CLIENT_NUMBER AS SDID
			,AGENCY.AGENCY_NAME AS ProdName
			,AGENCY.AGENCY_ADDR_1 AS ProdAddr1
			,AGENCY.AGENCY_ADDR_2 AS ProdAddr2
			,AGENCY.AGENCY_CITY AS ProdCity
			,AGENCY.AGENCY_STATE AS ProdState
			,AGENCY.AGENCY_ZIP_CODE AS ProdZip
			,CLIENT.LNAME1 AS SDName  -- Insured Name
			,CASE WHEN ADDRESS.SEQUENCE_NUMBER = 1 THEN 'Y' ELSE 'N' END AS SDMailingAddr
			,ADDRESS.ADDRESS1 AS SDAddr1 
			,ADDRESS.ADDRESS2 AS SDAddr2
			,ADDRESS.CITY AS SDCity
			,ADDRESS.STATE AS SDState 
			,ADDRESS.ZIPCODE AS SDZip
			,CLIENT.TAX_ID1 AS SDFEIN 
			,PB_DETAIL.CHAR_DESC_01 AS BureauID
			,CASE WHEN PB_DETAIL.CHAR_01_02 = 1 THEN '11111' ELSE '12345' END AS InsCarrier 
			,POLICY.POL_TRANS_ID AS EndNum
			,WC_RATING.EXP_MOD AS ExpRated
			,WC_RATING.EXP_MOD_FCTR AS ExpModFactor
			,WC_RATING.MINIMUM_PREM AS MinPrem
			,WC_RATING.MOD_PREM_INC_CR AS PolEstStandardPrem
			,POLICY.BILLED_PREMIUM AS PolDepPrem
			,WC_RATING.PREM_DISC_PREM AS PremDisc
			,CASE WC_RATING.LIAB_LMT WHEN 1 THEN 100000 WHEN 2 THEN 500000 WHEN 3 THEN 1000000 ELSE 0 END AS AccLiabLimit
			,CASE WC_RATING.LIAB_LMT WHEN 1 THEN 500000 WHEN 2 THEN 500000 WHEN 3 THEN 1000000 ELSE 0 END AS PolLiabLimit
			,CASE WC_RATING.LIAB_LMT WHEN 1 THEN 100000 WHEN 2 THEN 500000 WHEN 3 THEN 1000000 END AS EmpLiabLimit
			,TypeOfBusText.DESCRIPTION AS LegalNatureOfIns
	FROM WC_RATING 
			INNER JOIN
				  (SELECT CODE, DESCRIPTION
					FROM EDIT_SHORT_CODE
					WHERE (NAME = 'TYPE_OF_BUS') AND (TBNAME = 'WC_RATING')) AS TypeOfBusText 
				ON WC_RATING.TYPE_OF_BUS = TypeOfBusText.CODE 
			RIGHT OUTER JOIN
					POLICY 
			LEFT OUTER JOIN
					PB_DETAIL 
				ON POLICY.POLICY_DATE_TIME = PB_DETAIL.POLICY_DATE_TIME 
					AND POLICY.POLICY_NUMBER = PB_DETAIL.POLICY_NUMBER
				ON WC_RATING.POLICY_NUMBER = POLICY.POLICY_NUMBER 
					AND WC_RATING.POLICY_DATE_TIME = POLICY.POLICY_DATE_TIME 
			LEFT OUTER JOIN
					AGENCY 
				ON POLICY.AGENCY_ID = AGENCY.AGENCY_ID 
			LEFT OUTER JOIN
					ADDRESS 
			INNER JOIN
					CLIENT 
				ON ADDRESS.CLIENT_NUMBER = CLIENT.CLIENT_NUMBER 
					AND ADDRESS.SEQUENCE_NUMBER = CLIENT.ADDRESS_NUMBERS
				ON POLICY.CLIENT_NUMBER = CLIENT.CLIENT_NUMBER
	WHERE (POLICY.QUOTE_IND IS NULL)
		AND (POLICY.UNDERWRITER_CANCEL = 0) 
                AND POLICY.POLICY_NUMBER Like '10-%-08' 
		AND PB_DETAIL.CHAR_01_02 = @Carrier 
		AND POLICY.POLICY_DATE_TIME > @StartDate 
		AND POLICY.POLICY_DATE_TIME <= @EndDate 
		AND POLICY.POLICY_DATE_TIME = (select max(a.POLICY_DATE_TIME) from POLICY a where a.POLICY_NUMBER = POLICY.POLICY_NUMBER)

END

Open in new window

0
 
LVL 1

Author Comment

by:tobin46
ID: 26276002
Ralmada!  You are great!!!  The first solution worked, just had an ambiguous column name in POLICY_DATE_TIME.  Had to change to POLICY.POLICY_DATE_TIME and it worked.  Also can you explain how the row number over partition code works?  Does the policy record with the greatest (most recent) POLIC_DATE_TIME have rn=1?

The second solution doesn't work using the start and end dates...any thoughts on that?
0
 
LVL 41

Expert Comment

by:Sharath
ID: 26276300
try this.
ALTER PROCEDURE [dbo].[GetBasicPolicyInfo] 
	@Carrier						INT,		
	@StartDate						DATETIME,	-- Start pulling transactions starting at this date.
	@EndDate						DATETIME	-- Stop pulling transaction ending on this date.
AS
BEGIN

	SET NOCOUNT ON;
    WITH CTE AS (
	SELECT  POLICY.POLICY_NUMBER AS PolNum 
			,(POLICY.POLICY_DATE_TIME) AS TransDate 
			,POLICY.POL_EFF_DATE AS PolEffDate 
			,POLICY.POL_EXP_DATE AS PolExpDate 
			,POLICY.CHANGE_EFF_DATE AS TransEffDate 
			,REPLACE(CASE PB_DETAIL.CHAR_01_02 WHEN  1 THEN 'MWC' + POLICY.POL_ALT_ID + '00' ELSE POLICY.POL_ALT_ID END, '-', '') AS AltPolNum
			,REPLACE(CASE PB_DETAIL.CHAR_01_02 WHEN  1 THEN 'MWC' + POLICY.PRIOR_POLICY + '00' ELSE POLICY.PRIOR_POLICY END, '-', '') AS PriorPolNum
			,CASE  
				WHEN POLICY.POL_TRANS_ID = '-' 
					THEN 
						CASE POLICY.NEW_OR_RENEWAL 
							WHEN 1 
								THEN 'NB' 
							ELSE 
								CASE 
									WHEN POLICY.PRIOR_POLICY IS NULL 
										THEN 'NB' 
									ELSE 'RE' 
								END 
						END
				WHEN POLICY.UNDERWRITER_CANCEL <> 0
					THEN 'CA' 
				ELSE 'EN' 
			 END AS TransType
			,POLICY.AGENCY_ID AS ProdID
			,POLICY.CLIENT_NUMBER AS SDID
			,AGENCY.AGENCY_NAME AS ProdName
			,AGENCY.AGENCY_ADDR_1 AS ProdAddr1
			,AGENCY.AGENCY_ADDR_2 AS ProdAddr2
			,AGENCY.AGENCY_CITY AS ProdCity
			,AGENCY.AGENCY_STATE AS ProdState
			,AGENCY.AGENCY_ZIP_CODE AS ProdZip
			,CLIENT.LNAME1 AS SDName  -- Insured Name
			,CASE WHEN ADDRESS.SEQUENCE_NUMBER = 1 THEN 'Y' ELSE 'N' END AS SDMailingAddr
			,ADDRESS.ADDRESS1 AS SDAddr1 
			,ADDRESS.ADDRESS2 AS SDAddr2
			,ADDRESS.CITY AS SDCity
			,ADDRESS.STATE AS SDState 
			,ADDRESS.ZIPCODE AS SDZip
			,CLIENT.TAX_ID1 AS SDFEIN 
			,PB_DETAIL.CHAR_DESC_01 AS BureauID
			,CASE WHEN PB_DETAIL.CHAR_01_02 = 1 THEN '11111' ELSE '12345' END AS InsCarrier 
			,POLICY.POL_TRANS_ID AS EndNum
			,WC_RATING.EXP_MOD AS ExpRated
			,WC_RATING.EXP_MOD_FCTR AS ExpModFactor
			,WC_RATING.MINIMUM_PREM AS MinPrem
			,WC_RATING.MOD_PREM_INC_CR AS PolEstStandardPrem
			,POLICY.BILLED_PREMIUM AS PolDepPrem
			,WC_RATING.PREM_DISC_PREM AS PremDisc
			,CASE WC_RATING.LIAB_LMT WHEN 1 THEN 100000 WHEN 2 THEN 500000 WHEN 3 THEN 1000000 ELSE 0 END AS AccLiabLimit
			,CASE WC_RATING.LIAB_LMT WHEN 1 THEN 500000 WHEN 2 THEN 500000 WHEN 3 THEN 1000000 ELSE 0 END AS PolLiabLimit
			,CASE WC_RATING.LIAB_LMT WHEN 1 THEN 100000 WHEN 2 THEN 500000 WHEN 3 THEN 1000000 END AS EmpLiabLimit
			,TypeOfBusText.DESCRIPTION AS LegalNatureOfIns
	FROM WC_RATING 
			INNER JOIN
				  (SELECT CODE, DESCRIPTION
					FROM EDIT_SHORT_CODE
					WHERE (NAME = 'TYPE_OF_BUS') AND (TBNAME = 'WC_RATING')) AS TypeOfBusText 
				ON WC_RATING.TYPE_OF_BUS = TypeOfBusText.CODE 
			RIGHT OUTER JOIN
					POLICY 
			LEFT OUTER JOIN
					PB_DETAIL 
				ON POLICY.POLICY_DATE_TIME = PB_DETAIL.POLICY_DATE_TIME 
					AND POLICY.POLICY_NUMBER = PB_DETAIL.POLICY_NUMBER
				ON WC_RATING.POLICY_NUMBER = POLICY.POLICY_NUMBER 
					AND WC_RATING.POLICY_DATE_TIME = POLICY.POLICY_DATE_TIME 
			LEFT OUTER JOIN
					AGENCY 
				ON POLICY.AGENCY_ID = AGENCY.AGENCY_ID 
			LEFT OUTER JOIN
					ADDRESS 
			INNER JOIN
					CLIENT 
				ON ADDRESS.CLIENT_NUMBER = CLIENT.CLIENT_NUMBER 
					AND ADDRESS.SEQUENCE_NUMBER = CLIENT.ADDRESS_NUMBERS
				ON POLICY.CLIENT_NUMBER = CLIENT.CLIENT_NUMBER
	WHERE (POLICY.QUOTE_IND IS NULL)
		AND (POLICY.UNDERWRITER_CANCEL = 0) 
                AND POLICY.POLICY_NUMBER Like '10-%-08' 
		AND PB_DETAIL.CHAR_01_02 = @Carrier 
		AND POLICY.POLICY_DATE_TIME > @StartDate 
		AND POLICY.POLICY_DATE_TIME <= @EndDate )
SELECT *
  FROM CTE C1 WHERE TransDate = (SELECT MAX(POLICY_DATE_TIME) FROM POLICY C2 WHERE C1.PolNum = C2.PolNum)
		

END

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 26276309
used wrong column name. try this.
ALTER PROCEDURE [dbo].[GetBasicPolicyInfo] 
	@Carrier						INT,		
	@StartDate						DATETIME,	-- Start pulling transactions starting at this date.
	@EndDate						DATETIME	-- Stop pulling transaction ending on this date.
AS
BEGIN

	SET NOCOUNT ON;
    WITH CTE AS (
	SELECT  POLICY.POLICY_NUMBER AS PolNum 
			,(POLICY.POLICY_DATE_TIME) AS TransDate 
			,POLICY.POL_EFF_DATE AS PolEffDate 
			,POLICY.POL_EXP_DATE AS PolExpDate 
			,POLICY.CHANGE_EFF_DATE AS TransEffDate 
			,REPLACE(CASE PB_DETAIL.CHAR_01_02 WHEN  1 THEN 'MWC' + POLICY.POL_ALT_ID + '00' ELSE POLICY.POL_ALT_ID END, '-', '') AS AltPolNum
			,REPLACE(CASE PB_DETAIL.CHAR_01_02 WHEN  1 THEN 'MWC' + POLICY.PRIOR_POLICY + '00' ELSE POLICY.PRIOR_POLICY END, '-', '') AS PriorPolNum
			,CASE  
				WHEN POLICY.POL_TRANS_ID = '-' 
					THEN 
						CASE POLICY.NEW_OR_RENEWAL 
							WHEN 1 
								THEN 'NB' 
							ELSE 
								CASE 
									WHEN POLICY.PRIOR_POLICY IS NULL 
										THEN 'NB' 
									ELSE 'RE' 
								END 
						END
				WHEN POLICY.UNDERWRITER_CANCEL <> 0
					THEN 'CA' 
				ELSE 'EN' 
			 END AS TransType
			,POLICY.AGENCY_ID AS ProdID
			,POLICY.CLIENT_NUMBER AS SDID
			,AGENCY.AGENCY_NAME AS ProdName
			,AGENCY.AGENCY_ADDR_1 AS ProdAddr1
			,AGENCY.AGENCY_ADDR_2 AS ProdAddr2
			,AGENCY.AGENCY_CITY AS ProdCity
			,AGENCY.AGENCY_STATE AS ProdState
			,AGENCY.AGENCY_ZIP_CODE AS ProdZip
			,CLIENT.LNAME1 AS SDName  -- Insured Name
			,CASE WHEN ADDRESS.SEQUENCE_NUMBER = 1 THEN 'Y' ELSE 'N' END AS SDMailingAddr
			,ADDRESS.ADDRESS1 AS SDAddr1 
			,ADDRESS.ADDRESS2 AS SDAddr2
			,ADDRESS.CITY AS SDCity
			,ADDRESS.STATE AS SDState 
			,ADDRESS.ZIPCODE AS SDZip
			,CLIENT.TAX_ID1 AS SDFEIN 
			,PB_DETAIL.CHAR_DESC_01 AS BureauID
			,CASE WHEN PB_DETAIL.CHAR_01_02 = 1 THEN '11111' ELSE '12345' END AS InsCarrier 
			,POLICY.POL_TRANS_ID AS EndNum
			,WC_RATING.EXP_MOD AS ExpRated
			,WC_RATING.EXP_MOD_FCTR AS ExpModFactor
			,WC_RATING.MINIMUM_PREM AS MinPrem
			,WC_RATING.MOD_PREM_INC_CR AS PolEstStandardPrem
			,POLICY.BILLED_PREMIUM AS PolDepPrem
			,WC_RATING.PREM_DISC_PREM AS PremDisc
			,CASE WC_RATING.LIAB_LMT WHEN 1 THEN 100000 WHEN 2 THEN 500000 WHEN 3 THEN 1000000 ELSE 0 END AS AccLiabLimit
			,CASE WC_RATING.LIAB_LMT WHEN 1 THEN 500000 WHEN 2 THEN 500000 WHEN 3 THEN 1000000 ELSE 0 END AS PolLiabLimit
			,CASE WC_RATING.LIAB_LMT WHEN 1 THEN 100000 WHEN 2 THEN 500000 WHEN 3 THEN 1000000 END AS EmpLiabLimit
			,TypeOfBusText.DESCRIPTION AS LegalNatureOfIns
	FROM WC_RATING 
			INNER JOIN
				  (SELECT CODE, DESCRIPTION
					FROM EDIT_SHORT_CODE
					WHERE (NAME = 'TYPE_OF_BUS') AND (TBNAME = 'WC_RATING')) AS TypeOfBusText 
				ON WC_RATING.TYPE_OF_BUS = TypeOfBusText.CODE 
			RIGHT OUTER JOIN
					POLICY 
			LEFT OUTER JOIN
					PB_DETAIL 
				ON POLICY.POLICY_DATE_TIME = PB_DETAIL.POLICY_DATE_TIME 
					AND POLICY.POLICY_NUMBER = PB_DETAIL.POLICY_NUMBER
				ON WC_RATING.POLICY_NUMBER = POLICY.POLICY_NUMBER 
					AND WC_RATING.POLICY_DATE_TIME = POLICY.POLICY_DATE_TIME 
			LEFT OUTER JOIN
					AGENCY 
				ON POLICY.AGENCY_ID = AGENCY.AGENCY_ID 
			LEFT OUTER JOIN
					ADDRESS 
			INNER JOIN
					CLIENT 
				ON ADDRESS.CLIENT_NUMBER = CLIENT.CLIENT_NUMBER 
					AND ADDRESS.SEQUENCE_NUMBER = CLIENT.ADDRESS_NUMBERS
				ON POLICY.CLIENT_NUMBER = CLIENT.CLIENT_NUMBER
	WHERE (POLICY.QUOTE_IND IS NULL)
		AND (POLICY.UNDERWRITER_CANCEL = 0) 
                AND POLICY.POLICY_NUMBER Like '10-%-08' 
		AND PB_DETAIL.CHAR_01_02 = @Carrier 
		AND POLICY.POLICY_DATE_TIME > @StartDate 
		AND POLICY.POLICY_DATE_TIME <= @EndDate )
SELECT *
  FROM CTE C1 WHERE TransDate = (SELECT MAX(POLICY_DATE_TIME) FROM POLICY C2 WHERE C1.PolNum = C2.POLICY_NUMBER)
		

END

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 26276312
This is another way.
ALTER PROCEDURE [dbo].[GetBasicPolicyInfo] 
	@Carrier						INT,		
	@StartDate						DATETIME,	-- Start pulling transactions starting at this date.
	@EndDate						DATETIME	-- Stop pulling transaction ending on this date.
AS
BEGIN

	SET NOCOUNT ON;
    WITH CTE AS (
	SELECT  POLICY.POLICY_NUMBER AS PolNum 
			,(POLICY.POLICY_DATE_TIME) AS TransDate 
			,POLICY.POL_EFF_DATE AS PolEffDate 
			,POLICY.POL_EXP_DATE AS PolExpDate 
			,POLICY.CHANGE_EFF_DATE AS TransEffDate 
			,REPLACE(CASE PB_DETAIL.CHAR_01_02 WHEN  1 THEN 'MWC' + POLICY.POL_ALT_ID + '00' ELSE POLICY.POL_ALT_ID END, '-', '') AS AltPolNum
			,REPLACE(CASE PB_DETAIL.CHAR_01_02 WHEN  1 THEN 'MWC' + POLICY.PRIOR_POLICY + '00' ELSE POLICY.PRIOR_POLICY END, '-', '') AS PriorPolNum
			,CASE  
				WHEN POLICY.POL_TRANS_ID = '-' 
					THEN 
						CASE POLICY.NEW_OR_RENEWAL 
							WHEN 1 
								THEN 'NB' 
							ELSE 
								CASE 
									WHEN POLICY.PRIOR_POLICY IS NULL 
										THEN 'NB' 
									ELSE 'RE' 
								END 
						END
				WHEN POLICY.UNDERWRITER_CANCEL <> 0
					THEN 'CA' 
				ELSE 'EN' 
			 END AS TransType
			,POLICY.AGENCY_ID AS ProdID
			,POLICY.CLIENT_NUMBER AS SDID
			,AGENCY.AGENCY_NAME AS ProdName
			,AGENCY.AGENCY_ADDR_1 AS ProdAddr1
			,AGENCY.AGENCY_ADDR_2 AS ProdAddr2
			,AGENCY.AGENCY_CITY AS ProdCity
			,AGENCY.AGENCY_STATE AS ProdState
			,AGENCY.AGENCY_ZIP_CODE AS ProdZip
			,CLIENT.LNAME1 AS SDName  -- Insured Name
			,CASE WHEN ADDRESS.SEQUENCE_NUMBER = 1 THEN 'Y' ELSE 'N' END AS SDMailingAddr
			,ADDRESS.ADDRESS1 AS SDAddr1 
			,ADDRESS.ADDRESS2 AS SDAddr2
			,ADDRESS.CITY AS SDCity
			,ADDRESS.STATE AS SDState 
			,ADDRESS.ZIPCODE AS SDZip
			,CLIENT.TAX_ID1 AS SDFEIN 
			,PB_DETAIL.CHAR_DESC_01 AS BureauID
			,CASE WHEN PB_DETAIL.CHAR_01_02 = 1 THEN '11111' ELSE '12345' END AS InsCarrier 
			,POLICY.POL_TRANS_ID AS EndNum
			,WC_RATING.EXP_MOD AS ExpRated
			,WC_RATING.EXP_MOD_FCTR AS ExpModFactor
			,WC_RATING.MINIMUM_PREM AS MinPrem
			,WC_RATING.MOD_PREM_INC_CR AS PolEstStandardPrem
			,POLICY.BILLED_PREMIUM AS PolDepPrem
			,WC_RATING.PREM_DISC_PREM AS PremDisc
			,CASE WC_RATING.LIAB_LMT WHEN 1 THEN 100000 WHEN 2 THEN 500000 WHEN 3 THEN 1000000 ELSE 0 END AS AccLiabLimit
			,CASE WC_RATING.LIAB_LMT WHEN 1 THEN 500000 WHEN 2 THEN 500000 WHEN 3 THEN 1000000 ELSE 0 END AS PolLiabLimit
			,CASE WC_RATING.LIAB_LMT WHEN 1 THEN 100000 WHEN 2 THEN 500000 WHEN 3 THEN 1000000 END AS EmpLiabLimit
			,TypeOfBusText.DESCRIPTION AS LegalNatureOfIns
	FROM WC_RATING 
			INNER JOIN
				  (SELECT CODE, DESCRIPTION
					FROM EDIT_SHORT_CODE
					WHERE (NAME = 'TYPE_OF_BUS') AND (TBNAME = 'WC_RATING')) AS TypeOfBusText 
				ON WC_RATING.TYPE_OF_BUS = TypeOfBusText.CODE 
			RIGHT OUTER JOIN
					POLICY 
			LEFT OUTER JOIN
					PB_DETAIL 
				ON POLICY.POLICY_DATE_TIME = PB_DETAIL.POLICY_DATE_TIME 
					AND POLICY.POLICY_NUMBER = PB_DETAIL.POLICY_NUMBER
				ON WC_RATING.POLICY_NUMBER = POLICY.POLICY_NUMBER 
					AND WC_RATING.POLICY_DATE_TIME = POLICY.POLICY_DATE_TIME 
			LEFT OUTER JOIN
					AGENCY 
				ON POLICY.AGENCY_ID = AGENCY.AGENCY_ID 
			LEFT OUTER JOIN
					ADDRESS 
			INNER JOIN
					CLIENT 
				ON ADDRESS.CLIENT_NUMBER = CLIENT.CLIENT_NUMBER 
					AND ADDRESS.SEQUENCE_NUMBER = CLIENT.ADDRESS_NUMBERS
				ON POLICY.CLIENT_NUMBER = CLIENT.CLIENT_NUMBER
	WHERE (POLICY.QUOTE_IND IS NULL)
		AND (POLICY.UNDERWRITER_CANCEL = 0) 
                AND POLICY.POLICY_NUMBER Like '10-%-08' 
		AND PB_DETAIL.CHAR_01_02 = @Carrier 
		AND POLICY.POLICY_DATE_TIME > @StartDate 
		AND POLICY.POLICY_DATE_TIME <= @EndDate )
SELECT C1.*
  FROM CTE C1 
  JOIN (SELECT POLICY_NUMBER,MAX(POLICY_DATE_TIME) POLICY_DATE_TIME FROM POLICY GROUP BY POLICY_NUMBER) C2
     ON C1.PolNum = C2.POLICY_NUMBER AND C1.TransDate = C2.POLICY_DATE_TIME
		

END

Open in new window

0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 800 total points
ID: 26276316
0
 
LVL 41

Accepted Solution

by:
ralmada earned 1200 total points
ID: 26278440
@tobin46,
1) Yes you're correct in comment http:#a26274298 I forgot to add the alias after Order by
....,row_number() over (partition by POLICY.POLICY_NUMBER order by POLICY.POLICY_DATE_TIME desc) rn....

Row_number is a new function in SQL 2005 that basically enumerates all rows in a table "on the fly" and by using the Partition by clause it will enumerate all rows, but it will start over for each group of duplicate POLICY_NUMBER. so basically something like this

policy datetime rn
1 1/1/2010 1 <-- starts with 1
1 2/1/2010 2
2 3/1/2010 1 <-- starts with 1 again!
2 1/1/2010 2
Here's a detailed example of how to find duplicate rows using row_number function
http://www.sqlyoga.com/2009/03/sql-server-find-duplicate-rows-with.html 
 2) The second solution should work like this: BUT keep in mind that this solution will work if the policy_date_time column has the date and the time portion as well. If you just have date it will still bring duplicates.

ALTER PROCEDURE [dbo].[GetBasicPolicyInfo] 
	@Carrier						INT,		
	@StartDate						DATETIME,	-- Start pulling transactions starting at this date.
	@EndDate						DATETIME	-- Stop pulling transaction ending on this date.
AS
BEGIN

	SET NOCOUNT ON;
	SELECT  POLICY.POLICY_NUMBER AS PolNum 
			,(POLICY.POLICY_DATE_TIME) AS TransDate 
			,POLICY.POL_EFF_DATE AS PolEffDate 
			,POLICY.POL_EXP_DATE AS PolExpDate 
			,POLICY.CHANGE_EFF_DATE AS TransEffDate 
			,REPLACE(CASE PB_DETAIL.CHAR_01_02 WHEN  1 THEN 'MWC' + POLICY.POL_ALT_ID + '00' ELSE POLICY.POL_ALT_ID END, '-', '') AS AltPolNum
			,REPLACE(CASE PB_DETAIL.CHAR_01_02 WHEN  1 THEN 'MWC' + POLICY.PRIOR_POLICY + '00' ELSE POLICY.PRIOR_POLICY END, '-', '') AS PriorPolNum
			,CASE  
				WHEN POLICY.POL_TRANS_ID = '-' 
					THEN 
						CASE POLICY.NEW_OR_RENEWAL 
							WHEN 1 
								THEN 'NB' 
							ELSE 
								CASE 
									WHEN POLICY.PRIOR_POLICY IS NULL 
										THEN 'NB' 
									ELSE 'RE' 
								END 
						END
				WHEN POLICY.UNDERWRITER_CANCEL <> 0
					THEN 'CA' 
				ELSE 'EN' 
			 END AS TransType
			,POLICY.AGENCY_ID AS ProdID
			,POLICY.CLIENT_NUMBER AS SDID
			,AGENCY.AGENCY_NAME AS ProdName
			,AGENCY.AGENCY_ADDR_1 AS ProdAddr1
			,AGENCY.AGENCY_ADDR_2 AS ProdAddr2
			,AGENCY.AGENCY_CITY AS ProdCity
			,AGENCY.AGENCY_STATE AS ProdState
			,AGENCY.AGENCY_ZIP_CODE AS ProdZip
			,CLIENT.LNAME1 AS SDName  -- Insured Name
			,CASE WHEN ADDRESS.SEQUENCE_NUMBER = 1 THEN 'Y' ELSE 'N' END AS SDMailingAddr
			,ADDRESS.ADDRESS1 AS SDAddr1 
			,ADDRESS.ADDRESS2 AS SDAddr2
			,ADDRESS.CITY AS SDCity
			,ADDRESS.STATE AS SDState 
			,ADDRESS.ZIPCODE AS SDZip
			,CLIENT.TAX_ID1 AS SDFEIN 
			,PB_DETAIL.CHAR_DESC_01 AS BureauID
			,CASE WHEN PB_DETAIL.CHAR_01_02 = 1 THEN '11111' ELSE '12345' END AS InsCarrier 
			,POLICY.POL_TRANS_ID AS EndNum
			,WC_RATING.EXP_MOD AS ExpRated
			,WC_RATING.EXP_MOD_FCTR AS ExpModFactor
			,WC_RATING.MINIMUM_PREM AS MinPrem
			,WC_RATING.MOD_PREM_INC_CR AS PolEstStandardPrem
			,POLICY.BILLED_PREMIUM AS PolDepPrem
			,WC_RATING.PREM_DISC_PREM AS PremDisc
			,CASE WC_RATING.LIAB_LMT WHEN 1 THEN 100000 WHEN 2 THEN 500000 WHEN 3 THEN 1000000 ELSE 0 END AS AccLiabLimit
			,CASE WC_RATING.LIAB_LMT WHEN 1 THEN 500000 WHEN 2 THEN 500000 WHEN 3 THEN 1000000 ELSE 0 END AS PolLiabLimit
			,CASE WC_RATING.LIAB_LMT WHEN 1 THEN 100000 WHEN 2 THEN 500000 WHEN 3 THEN 1000000 END AS EmpLiabLimit
			,TypeOfBusText.DESCRIPTION AS LegalNatureOfIns
	FROM WC_RATING 
			INNER JOIN
				  (SELECT CODE, DESCRIPTION
					FROM EDIT_SHORT_CODE
					WHERE (NAME = 'TYPE_OF_BUS') AND (TBNAME = 'WC_RATING')) AS TypeOfBusText 
				ON WC_RATING.TYPE_OF_BUS = TypeOfBusText.CODE 
			RIGHT OUTER JOIN
					POLICY 
			LEFT OUTER JOIN
					PB_DETAIL 
				ON POLICY.POLICY_DATE_TIME = PB_DETAIL.POLICY_DATE_TIME 
					AND POLICY.POLICY_NUMBER = PB_DETAIL.POLICY_NUMBER
				ON WC_RATING.POLICY_NUMBER = POLICY.POLICY_NUMBER 
					AND WC_RATING.POLICY_DATE_TIME = POLICY.POLICY_DATE_TIME 
			LEFT OUTER JOIN
					AGENCY 
				ON POLICY.AGENCY_ID = AGENCY.AGENCY_ID 
			LEFT OUTER JOIN
					ADDRESS 
			INNER JOIN
					CLIENT 
				ON ADDRESS.CLIENT_NUMBER = CLIENT.CLIENT_NUMBER 
					AND ADDRESS.SEQUENCE_NUMBER = CLIENT.ADDRESS_NUMBERS
				ON POLICY.CLIENT_NUMBER = CLIENT.CLIENT_NUMBER
	WHERE (POLICY.QUOTE_IND IS NULL)
		AND (POLICY.UNDERWRITER_CANCEL = 0) 
                AND POLICY.POLICY_NUMBER Like '10-%-08' 
		AND PB_DETAIL.CHAR_01_02 = @Carrier 
		AND POLICY.POLICY_DATE_TIME > @StartDate 
		AND POLICY.POLICY_DATE_TIME <= @EndDate 
		AND POLICY.POLICY_DATE_TIME = (select max(a.POLICY_DATE_TIME) from POLICY a where a.POLICY_NUMBER = POLICY.POLICY_NUMBER and a.POLICY_DATE_TIME > @StartDate and a.POLICY_DATE_TIME <= @EndDate)

END

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 26278620
Or the second solution could be like as well.
ALTER PROCEDURE [dbo].[GetBasicPolicyInfo] 
	@Carrier						INT,		
	@StartDate						DATETIME,	-- Start pulling transactions starting at this date.
	@EndDate						DATETIME	-- Stop pulling transaction ending on this date.
AS
BEGIN

	SET NOCOUNT ON;
	;with CTE as (

	SELECT  POLICY.POLICY_NUMBER AS PolNum 
			,(POLICY.POLICY_DATE_TIME) AS TransDate 
			,POLICY.POL_EFF_DATE AS PolEffDate 
			,POLICY.POL_EXP_DATE AS PolExpDate 
			,POLICY.CHANGE_EFF_DATE AS TransEffDate 
			,REPLACE(CASE PB_DETAIL.CHAR_01_02 WHEN  1 THEN 'MWC' + POLICY.POL_ALT_ID + '00' ELSE POLICY.POL_ALT_ID END, '-', '') AS AltPolNum
			,REPLACE(CASE PB_DETAIL.CHAR_01_02 WHEN  1 THEN 'MWC' + POLICY.PRIOR_POLICY + '00' ELSE POLICY.PRIOR_POLICY END, '-', '') AS PriorPolNum
			,CASE  
				WHEN POLICY.POL_TRANS_ID = '-' 
					THEN 
						CASE POLICY.NEW_OR_RENEWAL 
							WHEN 1 
								THEN 'NB' 
							ELSE 
								CASE 
									WHEN POLICY.PRIOR_POLICY IS NULL 
										THEN 'NB' 
									ELSE 'RE' 
								END 
						END
				WHEN POLICY.UNDERWRITER_CANCEL <> 0
					THEN 'CA' 
				ELSE 'EN' 
			 END AS TransType
			,POLICY.AGENCY_ID AS ProdID
			,POLICY.CLIENT_NUMBER AS SDID
			,AGENCY.AGENCY_NAME AS ProdName
			,AGENCY.AGENCY_ADDR_1 AS ProdAddr1
			,AGENCY.AGENCY_ADDR_2 AS ProdAddr2
			,AGENCY.AGENCY_CITY AS ProdCity
			,AGENCY.AGENCY_STATE AS ProdState
			,AGENCY.AGENCY_ZIP_CODE AS ProdZip
			,CLIENT.LNAME1 AS SDName  -- Insured Name
			,CASE WHEN ADDRESS.SEQUENCE_NUMBER = 1 THEN 'Y' ELSE 'N' END AS SDMailingAddr
			,ADDRESS.ADDRESS1 AS SDAddr1 
			,ADDRESS.ADDRESS2 AS SDAddr2
			,ADDRESS.CITY AS SDCity
			,ADDRESS.STATE AS SDState 
			,ADDRESS.ZIPCODE AS SDZip
			,CLIENT.TAX_ID1 AS SDFEIN 
			,PB_DETAIL.CHAR_DESC_01 AS BureauID
			,CASE WHEN PB_DETAIL.CHAR_01_02 = 1 THEN '11111' ELSE '12345' END AS InsCarrier 
			,POLICY.POL_TRANS_ID AS EndNum
			,WC_RATING.EXP_MOD AS ExpRated
			,WC_RATING.EXP_MOD_FCTR AS ExpModFactor
			,WC_RATING.MINIMUM_PREM AS MinPrem
			,WC_RATING.MOD_PREM_INC_CR AS PolEstStandardPrem
			,POLICY.BILLED_PREMIUM AS PolDepPrem
			,WC_RATING.PREM_DISC_PREM AS PremDisc
			,CASE WC_RATING.LIAB_LMT WHEN 1 THEN 100000 WHEN 2 THEN 500000 WHEN 3 THEN 1000000 ELSE 0 END AS AccLiabLimit
			,CASE WC_RATING.LIAB_LMT WHEN 1 THEN 500000 WHEN 2 THEN 500000 WHEN 3 THEN 1000000 ELSE 0 END AS PolLiabLimit
			,CASE WC_RATING.LIAB_LMT WHEN 1 THEN 100000 WHEN 2 THEN 500000 WHEN 3 THEN 1000000 END AS EmpLiabLimit
			,TypeOfBusText.DESCRIPTION AS LegalNatureOfIns
	FROM WC_RATING 
			INNER JOIN
				  (SELECT CODE, DESCRIPTION
					FROM EDIT_SHORT_CODE
					WHERE (NAME = 'TYPE_OF_BUS') AND (TBNAME = 'WC_RATING')) AS TypeOfBusText 
				ON WC_RATING.TYPE_OF_BUS = TypeOfBusText.CODE 
			RIGHT OUTER JOIN
					POLICY 
			LEFT OUTER JOIN
					PB_DETAIL 
				ON POLICY.POLICY_DATE_TIME = PB_DETAIL.POLICY_DATE_TIME 
					AND POLICY.POLICY_NUMBER = PB_DETAIL.POLICY_NUMBER
				ON WC_RATING.POLICY_NUMBER = POLICY.POLICY_NUMBER 
					AND WC_RATING.POLICY_DATE_TIME = POLICY.POLICY_DATE_TIME 
			LEFT OUTER JOIN
					AGENCY 
				ON POLICY.AGENCY_ID = AGENCY.AGENCY_ID 
			LEFT OUTER JOIN
					ADDRESS 
			INNER JOIN
					CLIENT 
				ON ADDRESS.CLIENT_NUMBER = CLIENT.CLIENT_NUMBER 
					AND ADDRESS.SEQUENCE_NUMBER = CLIENT.ADDRESS_NUMBERS
				ON POLICY.CLIENT_NUMBER = CLIENT.CLIENT_NUMBER
	WHERE (POLICY.QUOTE_IND IS NULL)
		AND (POLICY.UNDERWRITER_CANCEL = 0) 
                AND POLICY.POLICY_NUMBER Like '10-%-08' 
		AND PB_DETAIL.CHAR_01_02 = @Carrier 
		AND POLICY.POLICY_DATE_TIME > @StartDate 
		AND POLICY.POLICY_DATE_TIME <= @EndDate 
	)
	select * from CTE a
	where POLICY_DATE_TIME = (select max(POLICY_DATE_TIME) from CTE where POLICY_NUMBER = a.POLICY_NUMBER)

END

Open in new window

0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

581 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