Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql sub query

Posted on 2010-08-23
5
Medium Priority
?
263 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 1000 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 1000 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

704 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