Solved

SQL medium question

Posted on 2010-09-13
5
373 Views
Last Modified: 2012-06-27
First Table:
SSN, open_datetime, close_datetime, priority

Second Table:
ID, time, c_time, c_response, c_id

I need all the ssn, with an open date between 4/1/2010 and 4/30/2010, with a priority of 1 and need the c_response and time from table two.  Only problem is that second table has multiple records for the same c_id.  I need the first record of the second table based on c_id = SSN and c_time.

Thanks.
0
Comment
Question by:needplease
5 Comments
 
LVL 4

Accepted Solution

by:
timexist earned 250 total points
ID: 33669121
two step process:
step one,
join them together and put the result into a temp table.
step two,
group them with a max(id) on temp table.
then you have your result.

If you can not finish a process in one step, try to do it in two, three, four....steps.
this is the key to be a master in sql script.
0
 
LVL 8

Expert Comment

by:rushShah
ID: 33669282
try this query..

SELECT            f.SSN, f.open_datetime, f.close_datetime, Max(s.time) AS 'time', Max(c_response) AS 'response'
FROM            FirstTable f
INNER JOIN      SecondTable s ON s.c_id=f.SSN
WHERE            f.open_datetime between '4/1/2010' AND '4/30/2010' AND f.priority=1
GROUP BY       f.SSN, f.open_datetime, f.close_datetime
0
 
LVL 5

Expert Comment

by:Vipul Patel
ID: 33670618
below query might be helpful.


;WITH Level1
AS
(
      SELECT f.SSN
                  ,f.open_datetime
                  ,f.close_datetime
                  ,ROW_NUMBER() OVER (PARTITION BY s.c_id ORDER BY s.c_time ASC) AS RowNumber
      FROM FirstTable f
      INNER JOIN SecondTable s ON s.c_id=f.SSN
      WHERE (f.open_datetime between '4/1/2010' AND '4/30/2010' AND f.priority=1)
)
SELECT * FROM Level1 WHERE RowNumber = 1
0
 

Author Comment

by:needplease
ID: 33673987
timexist, I would prefer in one query.

rushShah, I get the following error, "The text, ntext, and image data types are invalid in this subquery or aggregate expression".

Thanks
0
 
LVL 8

Expert Comment

by:rushShah
ID: 33674231
it means you can't use c_response or c_time in aggregate function text, ntext or image datatype..

so try this CTE query & let me know if still there is an error..

;WITH CTE
AS
(
    SELECT          f.SSN, f.open_datetime, f.close_datetime, Max(s.ID) AS 'ID'
	FROM            FirstTable f
	INNER JOIN      SecondTable s ON s.c_id=f.SSN
	WHERE           f.open_datetime between '4/1/2010' AND '4/30/2010' AND f.priority=1
	GROUP BY		f.SSN, f.open_datetime, f.close_datetime
)

SELECT	c.SSN, c.open_datetime, c.close_datetime, s.time AS 'time', c_response AS 'response'
FROM	CTE c
INNER JOIN SecondTable s ON c.ID=s.ID

Open in new window

0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

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…
In this article I will describe the Backup & Restore 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.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
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…

776 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