a query

5 pts.
Tags:
Query
I have query of Placement. as fallow: client OpenedDate ClosedDate 1 1/2/05 1/20/06 1 2/05/06 3/2/06 1 4/2/07 2 1/2/06 I want to add another field to this query to tell me each time how long a take this client to open again. and another field to tell me this is his 1st or 2nd time been open. Thanks in advance for your help.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

I’m not sure I understand the problem. From the raw data you gave, do you want to produce this output?

<pre>
client p_num openeddate closeddate next_opened days_between
1 1 2005-01-02 2006-01-20 2006-02-05 16
1 2 2006-02-05 2006-03-02 2007-04-02 396
1 3 2007-04-02 NULL NULL NULL
2 1 2006-01-02 NULL NULL NULL
</pre>

where:
<b>p_num</b> identifies each row as being the 1st (2nd, 3rd, etc.) placement for this client, in order by openeddate
<b>next_opened</b> is the openeddate from the next placement for this client (or NULL if this is the last placement)
<b>days_between</b> is the number of days between the closeddate of one placement and the openeddate of the next

If so, you can
(1) Use ROW_NUMBER to assign p_num
(2) Do a self-join to link each placement with the next placement for the same client (if there is one)
(3) Use the WITH-clause to avoid having to compute p_num twice.

The following shows how to do this in SQLServer 2005. In Oracle, use “n.openeddate – c.closeddate” instead of “DATEDIFF (DAY, c.closeddate, n.openeddate)”.

<pre>
WITH p AS
(
SELECT client
, openeddate
, closeddate
, ROW_NUMBER () OVER
( PARTITION BY client
ORDER BY openeddate
) AS p_num
FROM placement
)
SELECT c.client
, c.p_num
, c.openeddate
, c.closeddate
, n.openeddate AS next_opened
, DATEDIFF (DAY, c.closeddate, n.openeddate) AS days_between
FROM p AS c
LEFT OUTER JOIN p AS n
ON c.client = n.client
AND c.p_num + 1 = n.p_num
ORDER BY c.client
, c.openeddate;
</pre>

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    What platform are you using and what version?
    66,070 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following