SQL medium question

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.
needpleaseAsked:
Who is Participating?
 
timexistConnect With a Mentor Commented:
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
 
rushShahCommented:
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
 
Vipul Patel.NET ExpertCommented:
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
 
needpleaseAuthor Commented:
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
 
rushShahCommented:
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
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.