Link to home
Create AccountLog in
Avatar of needplease
needpleaseFlag for United States of America

asked on

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.

Avatar of timexist
Flag of Australia image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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
below query might be helpful.

;WITH Level1
      SELECT f.SSN
                  ,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
Avatar of needplease


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".

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..

    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'
INNER JOIN SecondTable s ON c.ID=s.ID

Open in new window