Solved

sql sub query

Posted on 2010-08-23
5
261 Views
Last Modified: 2012-05-10
I have duplicate records except date in a table.. Each record has the case# four times.. I want to put the latest case# in a temp table..
How can I get the case numbers with the max date inserted into a temp table? See my code below..

The error i get is:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Select * INTO ##tblCB
From
(SELECT * FROM CORE.tblStatusTracking ST 
WHERE nCaseID IN (	select distinct st.nCaseID, Max(st.dtCreatedDate)	from CTS.CORE.tblCase c			inner join CTS.CORE.tblStatusTracking st
				On c.nCaseID = t.nCaseID
				inner join CTS.CORE.tblCD_AR_CaseInfo ci on c.nCaseID = ci.nCaseID

				inner join CTSApp.app.tblOracleCustomers oc
				on ci.sCustID = oc.ACCOUNT_NUMBER

				where c.nUserID_PrimaryAgent = 5402  
				and c.bClosed = 0
				and CUSTOMER_TYPE = 'NP & SS'
				group by st.nCaseID)
											)tt

Open in new window

0
Comment
Question by:cheryl9063
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 3

Assisted Solution

by:mnachu
mnachu earned 250 total points
ID: 33502811
Try getting the Case ID and Max Date into a temp table first (or CTE) and then use that to join on the tblSTatusTracking to get that particular row.

Try whether the following would work:

select 
	distinct st.nCaseID, Max(st.dtCreatedDate)	as dtMaxCreatedDate
INTO
	#tmpMaxDt
from 
	CTS.CORE.tblCase c			
	inner join CTS.CORE.tblStatusTracking st On c.nCaseID = t.nCaseID
	inner join CTS.CORE.tblCD_AR_CaseInfo ci on c.nCaseID = ci.nCaseID
	inner join CTSApp.app.tblOracleCustomers oc on ci.sCustID = oc.ACCOUNT_NUMBER
where 
	c.nUserID_PrimaryAgent = 5402  
and c.bClosed = 0
and CUSTOMER_TYPE = 'NP & SS'
group by 
	st.nCaseID

SELECT 
	* 
FROM 
	CORE.tblStatusTracking ST 
	INNER JOIN #tmpMaxDt AS T ON T.nCaseID = ST.nCaseID AND ST.dtCreatedDAte = T.dtMaxCreatedDate

Open in new window

0
 
LVL 3

Expert Comment

by:mnachu
ID: 33502829
The reason you get the error is the Sub query next to the WHERE nCaseID IN, has more than one column in it.
0
 
LVL 9

Accepted Solution

by:
valkyrie_nc earned 250 total points
ID: 33502840
Try this instead (changes are in the WHERE clause)::

SELECT * INTO ##tblCB FROM (SELECT * FROM CORE.tblStatusTracking ST
WHERE EXISTS (SELECT TOP 1 * FROM CTS.CORE.tblCase c INNER JOIN CTS.CORE.tblCD_AR_CaseInfo ci ON c.nCaseID = ci.nCaseID INNER JOIN CTSApp.app.tblOracleCustomers oc ON ci.sCustID = oc.ACCOUNT_NUMBER WHERE c.nUserID_PrimaryAgent = 5402 AND c.bClosed = 0 AND CUSTOMER_TYPE = 'NP & SS'))

hth

valkyrie_nc
0
 
LVL 12

Expert Comment

by:mcv22
ID: 33502871

;WITH RankedCase(nCaseID, OrderID) AS
(
	select st.nCaseID,,ROW_NUMBER() OVER (PARTITION BY st.nCaseID ORDER BY st.dtCreatedDate DESC)
	from CTS.CORE.tblCase c	
	inner join CTS.CORE.tblStatusTracking st
		On c.nCaseID = t.nCaseID
	inner join CTS.CORE.tblCD_AR_CaseInfo ci
		on c.nCaseID = ci.nCaseID
	inner join CTSApp.app.tblOracleCustomers oc
		on ci.sCustID = oc.ACCOUNT_NUMBER
	where c.nUserID_PrimaryAgent = 5402  
	and c.bClosed = 0
	and CUSTOMER_TYPE = 'NP & SS'
)
SELECT
	ST.*
INTO
	##tblCB
FROM
	RankedCase rc
JOIN
	CORE.tblStatusTracking ST
ON
	rc.nCaseID = ST.nCaseID
WHERE
	rc.OrderID = 1

Open in new window

0
 
LVL 6

Expert Comment

by:LCSandman8301
ID: 33503105
if there aren't many columns you could just group the columns together and get the max date
insert into newtable(col1, col2, MaxDate)
select col1, col2, max(dtCreatedDate)
from table1
group by col1, col2

Open in new window

0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard 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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

710 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