Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql sub query

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

885 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