Solved

SQL medium question

Posted on 2010-09-13
5
367 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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

706 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now