Having problems removing dups and CASE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Having problems removing dups and CASE

Author

Topic

cobbdj01
Starting Member

5 Posts

Posted - 2013-10-03 : 13:12:06

I am running the following query and when I get to the last select statement the results show 3420 when it should be 3247. Does anyone know how can I remove duplicates and list only the dbo.CER_SYMD_MSG_DEF.SYMD_MSG_CD when the #temp1112C.CLST_MCTR_REAS = ''?

SELECT
dbo.CMC_CLCL_CLAIM.GRGR_CK,
dbo.CMC_CLCL_CLAIM.CLCL_ID,
dbo.CMC_CLCL_CLAIM.CLCL_RECD_DT,
dbo.CMC_CLCL_CLAIM.SBSB_CK,
dbo.CMC_CLCL_CLAIM.CLCL_CUR_STS,
dbo.CMC_CLCL_CLAIM.CLST_MCTR_REAS,
dbo.CMC_CLCL_CLAIM.CLCL_CL_TYPE,
dbo.CMC_CLCL_CLAIM.PRPR_ID, datediff(dd,
dbo.CMC_CLCL_CLAIM.CLCL_RECD_DT,
GETDATE()) AS Age
INTO #temp1112d

FROM dbo.CMC_GRGR_GROUP
INNER JOIN dbo.CMC_CLCL_CLAIM
ON dbo.CMC_CLCL_CLAIM.GRGR_CK = dbo.CMC_GRGR_GROUP.GRGR_CK
WHERE dbo.CMC_GRGR_GROUP.GRGR_MCTR_TYPE = 'BBS' AND (dbo.CMC_CLCL_CLAIM.CLCL_CUR_STS = 11
OR dbo.CMC_CLCL_CLAIM.CLCL_CUR_STS = 15)

SELECT
GRGR_CK,
CLCL_ID,
CLCL_RECD_DT,
SBSB_CK,
CLCL_CUR_STS,
CLST_MCTR_REAS,
CLCL_CL_TYPE,
PRPR_ID,
Age
INTO #temp1118
FROM
(SELECT
dbo.CMC_CLCL_CLAIM.GRGR_CK,
dbo.CMC_CLCL_CLAIM.CLCL_ID,
dbo.CMC_CLCL_CLAIM.CLCL_RECD_DT,
dbo.CMC_CLCL_CLAIM.SBSB_CK,
dbo.CMC_CLCL_CLAIM.CLCL_CUR_STS,
dbo.CMC_CLCL_CLAIM.CLST_MCTR_REAS,
dbo.CMC_CLCL_CLAIM.CLCL_CL_TYPE,
dbo.CMC_CLCL_CLAIM.PRPR_ID, datediff(dd, dbo.CMC_CLCL_CLAIM.CLCL_RECD_DT,GETDATE()) AS Age,
ROW_NUMBER() OVER(PARTITION BY dbo.CMC_CLST_STATUS.CLCL_ID
ORDER BY dbo.CMC_CLST_STATUS.CLST_SEQ_NO DESC) AS rn,dbo.CMC_CLST_STATUS.CLST_USID_ROUTE
FROM dbo.CMC_GRGR_GROUP
INNER JOIN dbo.CMC_CLCL_CLAIM
ON dbo.CMC_CLCL_CLAIM.GRGR_CK = dbo.CMC_GRGR_GROUP.GRGR_CK
LEFT JOIN dbo.CMC_CLST_STATUS
ON dbo.CMC_CLST_STATUS.CLCL_ID = dbo.CMC_CLCL_CLAIM.CLCL_ID
WHERE dbo.CMC_GRGR_GROUP.GRGR_MCTR_TYPE = 'BBS' AND dbo.CMC_CLCL_CLAIM.CLCL_CUR_STS = 01
AND dbo.CMC_CLST_STATUS.CLST_MCTR_REAS = '')q
WHERE rn=1 and (CLST_USID_ROUTE = '20' OR CLST_USID_ROUTE = '70')

SELECT *
INTO #temp1112b
FROM #temp1112d
UNION all
SELECT *
FROM #temp1118

SELECT
dbo.CMC_GRGR_GROUP.GRGR_ID,
dbo.CMC_GRGR_GROUP.GRGR_NAME,
dbo.CMC_SBSB_SUBSC.SBSB_ID,
dbo.CMC_SBSB_SUBSC.SBSB_LAST_NAME,
dbo.CMC_SBSB_SUBSC.SBSB_FIRST_NAME,
#temp1112b.CLCL_ID,
dbo.CMC_CLCL_CLAIM.CLCL_CUR_STS,
dbo.CMC_CLCL_CLAIM.CLST_MCTR_REAS,
dbo.CMC_CLCL_CLAIM.CLCL_CL_TYPE,
dbo.CMC_CLCL_CLAIM.CLCL_RECD_DT,
GETDATE() AS today,
#temp1112b.Age,

CASE
WHEN #temp1112b.Age <= 15 THEN 15
WHEN (#temp1112b.Age > 15 AND #temp1112b.Age <= 30) THEN 30
WHEN (#temp1112b.Age > 30 AND #temp1112b.Age <= 45) THEN 45
WHEN (#temp1112b.Age > 45 AND #temp1112b.Age <= 60) THEN 60
WHEN (#temp1112b.Age > 60 AND #temp1112b.Age <= 90) THEN 90
WHEN (#temp1112b.Age > 90 AND #temp1112b.Age <= 120) THEN 120
WHEN (#temp1112b.Age > 120 AND #temp1112b.Age <= 180) THEN 180
ELSE 181
END AS AgeRange,dbo.CMC_PRPR_PROV.PRPR_ID,dbo.CMC_PRPR_PROV.PRPR_NAME

INTO #temp1112C
FROM #temp1112b
INNER JOIN dbo.CMC_CLCL_CLAIM
ON #temp1112b.CLCL_ID = dbo.CMC_CLCL_CLAIM.CLCL_ID
INNER JOIN dbo.CMC_GRGR_GROUP
ON dbo.CMC_CLCL_CLAIM.GRGR_CK = dbo.CMC_GRGR_GROUP.GRGR_CK
INNER JOIN dbo.CMC_SBSB_SUBSC
ON dbo.CMC_CLCL_CLAIM.SBSB_CK = dbo.CMC_SBSB_SUBSC.SBSB_CK
AND dbo.CMC_GRGR_GROUP.GRGR_CK = dbo.CMC_SBSB_SUBSC.GRGR_CK
INNER JOIN dbo.CMC_PRPR_PROV
ON dbo.CMC_CLCL_CLAIM.PRPR_ID = dbo.CMC_PRPR_PROV.PRPR_ID
GROUP BY #temp1112b.CLCL_ID,
dbo.CMC_GRGR_GROUP.GRGR_ID,
dbo.CMC_GRGR_GROUP.GRGR_NAME,
dbo.CMC_SBSB_SUBSC.SBSB_ID,
dbo.CMC_SBSB_SUBSC.SBSB_LAST_NAME,
dbo.CMC_SBSB_SUBSC.SBSB_FIRST_NAME,
dbo.CMC_CLCL_CLAIM.CLCL_CUR_STS,
dbo.CMC_CLCL_CLAIM.CLCL_CL_TYPE,
dbo.CMC_CLCL_CLAIM.CLCL_RECD_DT,
dbo.CMC_PRPR_PROV.PRPR_NAME,
dbo.CMC_PRPR_PROV.PRPR_ID,
dbo.CMC_CLCL_CLAIM.CLST_MCTR_REAS,
#temp1112b.Age;

SELECT #temp1112C.GRGR_ID,
#temp1112C.GRGR_NAME,
#temp1112C.SBSB_ID,
#temp1112C.SBSB_LAST_NAME,
#temp1112C.SBSB_FIRST_NAME,
#temp1112C.CLCL_ID,
#temp1112C.CLCL_CUR_STS,
#temp1112C.CLCL_CL_TYPE,
#temp1112C.CLCL_RECD_DT,
dbo.CER_SYMD_MSG_DEF.SYMD_MSG_CD, "CLST_MCTR_REAS" =
CASE
WHEN (#temp1112C.CLST_MCTR_REAS = '')
THEN CAST (dbo.CER_SYMD_MSG_DEF.SYMD_MSG_CD AS CHAR)
ELSE #temp1112C.CLST_MCTR_REAS
END
FROM dbo.#temp1112C
INNER JOIN dbo.CMC_CLCL_CLAIM
ON #temp1112C.CLCL_ID = dbo.CMC_CLCL_CLAIM.CLCL_ID
INNER JOIN dbo.CER_SYML_MSG_LOG
ON dbo.CER_SYML_MSG_LOG.SYML_CONTEXT_ID = #temp1112C.CLCL_ID
INNER JOIN dbo.CER_SYMD_MSG_DEF
ON dbo.CER_SYMD_MSG_DEF.SYMD_MSG_CD = dbo.CER_SYML_MSG_LOG.SYMD_MSG_CD
AND dbo.CER_SYMD_MSG_DEF.SYMD_ID = dbo.CER_SYML_MSG_LOG.SYMD_ID
GROUP BY (#temp1112C.GRGR_ID),
#temp1112C.CLCL_ID,
#temp1112C.CLST_MCTR_REAS,
dbo.CER_SYMD_MSG_DEF.SYMD_MSG_CD,
#temp1112C.GRGR_NAME,
#temp1112C.SBSB_ID,
#temp1112C.SBSB_LAST_NAME,
#temp1112C.SBSB_FIRST_NAME,
#temp1112C.CLCL_ID,
#temp1112C.CLCL_CUR_STS,
#temp1112C.CLCL_CL_TYPE,
#temp1112C.CLCL_RECD_DT,
#temp1112C.PRPR_ID

Thanks,

Dina

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-03 : 13:47:40

Removing Duplicates from a SELECT:
Assuming your underlying data does not have duplicates - ie the tables are properly constrained - Then you need to GROUP BY only the columns for which you want distinct values. The rest of the columns that you want displayed you will need to aggregate in some way. Like: MIN, MAX, AVG, FIRST, LAST, etc. If any of the columns you want to display that are not in the GROUP BY are expressions then you need to wrap the entire expression in an aggregate function.

Limiting to #temp1112C.CLST_MCTR_REAS = '':
Unless I misunderstand all you need to do is add that condition in a WHERE clause (before the GROUP BY clause).

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-04 : 01:40:02

It would be much more easier for someone to understand you issue if you explain it with some sample data. Unless you do that we can only keep guessing on what you mean by duplicates. Remember that we cant see your system. So try the given suggestion and if it doesnt work please post some sample data and then explain your problem.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

cobbdj01
Starting Member

5 Posts

Posted - 2013-10-04 : 14:28:44

Here's some sample data:

Group ID Group Name Subscriber ID Claim ID Sts Pend Code
111111111C ACADIAN AMBULANCE SERVI 2222222212 13333333300 15
111111111C ACADIAN AMBULANCE SERVI 2222222213 13000000200 11 ITMR

I am trying to replace the spaces in the Pend Code column (within the #temp1112C table) with a message code from the dbo.CER_SYMD_MSG_DEF table

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-10-04 : 15:17:52

Replace spaces?

Here are some links that can help you frame your question, as well as, how to provide sample data and expected output so we can help you better:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

cobbdj01
Starting Member

5 Posts

Posted - 2013-10-04 : 15:31:15

I appreciate your guidance on how to share sample data; however, I do not have privedges to create tables with same data; I am providing the original data as it is generated. This data has spaces instead of NULL values. I am trying to replace these spaces with another value from another table using a CASE statement but to no avail have I had any success:

CASE
WHEN (#temp1112C.CLST_MCTR_REAS = ' ')
THEN CAST (dbo.CER_SYMD_MSG_DEF.SYMD_MSG_CD AS CHAR)
ELSE #temp1112C.CLST_MCTR_REAS
END
FROM dbo.#temp1112C
INNER JOIN dbo.CMC_CLCL_CLAIM
ON #temp1112C.CLCL_ID = dbo.CMC_CLCL_CLAIM.CLCL_ID
INNER JOIN dbo.CER_SYML_MSG_LOG
ON dbo.CER_SYML_MSG_LOG.SYML_CONTEXT_ID = #temp1112C.CLCL_ID
INNER JOIN dbo.CER_SYMD_MSG_DEF
ON dbo.CER_SYMD_MSG_DEF.SYMD_MSG_CD = dbo.CER_SYML_MSG_LOG.SYMD_MSG_CD
AND dbo.CER_SYMD_MSG_DEF.SYMD_ID = dbo.CER_SYML_MSG_LOG.SYMD_ID

Could something be wrong with my JOINS?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-07 : 06:01:08

May be
Unless you show how values are existing in tables for related columns used in join we can only guess

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources