Solved

sql sub query

Posted on 2010-08-23
5
260 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 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

828 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