SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Having problems removing dups and CASE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cobbdj01
Starting Member

USA
5 Posts

Posted - 10/03/2013 :  13:12:06  Show Profile  Reply with Quote
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

Edited by - cobbdj01 on 10/03/2013 13:13:37

TG
Flowing Fount of Yak Knowledge

USA
5932 Posts

Posted - 10/03/2013 :  13:47:40  Show Profile  Reply with Quote
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

Edited by - TG on 10/03/2013 13:48:49
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/04/2013 :  01:40:02  Show Profile  Reply with Quote
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
Go to Top of Page

cobbdj01
Starting Member

USA
5 Posts

Posted - 10/04/2013 :  14:28:44  Show Profile  Reply with Quote
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

Edited by - cobbdj01 on 10/04/2013 14:30:02
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4354 Posts

Posted - 10/04/2013 :  15:17:52  Show Profile  Reply with Quote
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
Go to Top of Page

cobbdj01
Starting Member

USA
5 Posts

Posted - 10/04/2013 :  15:31:15  Show Profile  Reply with Quote
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?

Edited by - cobbdj01 on 10/04/2013 15:31:51
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/07/2013 :  06:01:08  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000