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.

Thanks.
ASKER CERTIFIED SOLUTION
Avatar of timexist
timexist
Flag of Australia image

Link to home
membership
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
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
Avatar of needplease

ASKER

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