sql sub query

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

LVL 1
cheryl9063Asked:
Who is Participating?
 
valkyrie_ncConnect With a Mentor Commented:
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
 
mnachuConnect With a Mentor Commented:
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
 
mnachuCommented:
The reason you get the error is the Sub query next to the WHERE nCaseID IN, has more than one column in it.
0
 
mcv22Commented:

;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
 
LCSandman8301Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.