select from row1 to row n

[Henrik Hartig]

Hey
I have made an sql.
But it is not what I want. I hope someone can help me create the right sql.

Explanation:
Every activity have more activitydetail. ex activity 500

aID act.detailID statusID datetime
c)500 1000 20 2008-04-28
500 1001 19 2008-04-28
500 1002 10 2008-04-28
a) 500 1003 26 2008-05-02
b) 500 1004 19 2008-05-15
500 1005 21 2008-05-15

This sql will output activities over 9 days from a) to b)

I want from c) to b)
from row1
to the row after status 26

Here is my sql:
SELECT
T3.ActivityID,
T4.TimeStamp AS FirstDateTimeSt amp,
T5.TimeStamp AS NextDateTimeSta mp,
T4.QueueName AS Technican,
DATEDIFF(dd, T4.TimeStamp, T5.TimeStamp) AS Days
FROM
ActivityDetails All AS T4 INNER JOIN
(SELECT
T1.ActivityID,
MIN(T2.Activity DetailID) AS FirstActivityDe tailID,
MIN(T1.Activity DetailID) AS NextActivityDet ailID
FROM
ActivityDetails All AS T1 INNER JOIN
(SELECT
ActivityID,
MAX(ActivityDet ailID) AS ActivityDetailI D
FROM
ActivityDetails All
WHERE
(TimeStamp BETWEEN '2008-05-01' AND '2008-05-31') AND
(StatusID = 26)
GROUP BY
ActivityID, ActivityDetailI D) AS T2 ON T1.ActivityID =
T2.ActivityID
AND T1.ActivityDeta ilID T2.ActivityDeta ilID
GROUP BY T1.ActivityID) AS T3 ON T3.FirstActivit yDetailID
= T4.ActivityDeta ilID
INNER JOIN
ActivityDetails AS T5 ON T3.NextActivity DetailID =
T5.ActivityDeta ilID
WHERE
(DATEDIFF(dd, T4.TimeStamp, T5.TimeStamp) 9)


In other words
a) 20
b) 19
c) 10
d) 26
e) 19
f) 21

want a) to e) (e is just after statusid 26 )

/henrik

[Erland Sommarskog]

Re: select from row1 to row n

Henrik Hartig (henrikhartig@v armmail.dk) writes:

Thank you very much for your answer. I haven't tried your solution,
witch I look forward too.
I have done what you tell me to do. Create a table and insert-statements
plus my own sql (that i describe in my first message).
I hope you will make an afford to look agin at my question.

I'm sorry, but I still don't understanding what you want to achieve.

my own sql return this:
205372 2008-05-02 08:04:58.143 2008-05-15
10:43:09.580 13
from b) to c).

What you mean with "from b) to c)"? Your query return two rows,
which makes sense as far that in your list, b and c comes after
each other, but the second row has a different activity id, and
that does not match your list.

I wan't from a) to c) [the row after statusid 26]
>
a) 853187 205372 20 2008-04-28
12:29:50.627
>
853465 205372 19 2008-04-28
19:27:05.383
>
853466 205372 10 2008-04-28
19:27:09.180
>
b) 855032 205372 26 2008-05-02
08:04:58.143
>
c) 860439 205372 19 2008-05-15
10:43:09.580
>
860685 205372 21 2008-05-15
14:27:06.670

So you want to output five rows? But these rows have different columns
from your query produces? Or do you mean that the aggregation is to
include these five rows?

I could probably make some guess of what you really mean, but I would
really prefer if you could make an effort to explain from the beginning
what these columns means and what your query is supposed to achieve.
The query decently complex, and since you say it's wrong I have not dug
into it.

I'm awfully, but if you cannot express your business problem clearly,
it is very difficult to help you. You may know what you are talking
about when you say "from a) to c)", but I don't.

And did you ever say which version of SQL Server you are using?




--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Henrik Hartig]

Re: select from row1 to row n

I'm using ms sql 2005 server

I will try to explain the case:

The system is used in a workshop that repair computers.

a) 853187 205372 20 2008-04-28
12:29:50.627
>
853465 205372 19 2008-04-28
19:27:05.383
>
853466 205372 10 2008-04-28
19:27:09.180
>
b) 855032 205372 26 2008-05-02
08:04:58.143
>
c) 860439 205372 19 2008-05-15
10:43:09.580
>
860685 205372 21 2008-05-15
14:27:06.670

The above shows the activities for one repairment. The activityID=2053 72
is the identifier for that repairment and 853187 to 860685 is the
details from we get the computer until we have repair the computer. Only
statusID=26 is interesting in this case. StatusID=26 indicate that the
technician have received some parts for the computer (ex. a soundcard).
There might get parts more than one time pr. activity.

If you run my test-data you will get this output:
*205372 2008-05-02 08:04:58.143 2008-05-15 10:43:09.580 13*
*205375 2008-05-06 08:10:51.777 2008-05-16 14:03:29.580 10
*The output for activityID 205372 (row1) shows the interval from b) to
c) that is 13 days.
What I want is the output from the start to the status right after
StatutID=26. That is from a) to c):
28.april to 15.may: 17 days.
I wan't all the activities that is over 9 days from start to the
statusID just after statusID=26.

I hope I have cleared up the case.
/henrik
Erland Sommarskog wrote:

Henrik Hartig (henrikhartig@v armmail.dk) writes:
>

>Thank you very much for your answer. I haven't tried your solution,
>witch I look forward too.
>I have done what you tell me to do. Create a table and insert-statements
>plus my own sql (that i describe in my first message).
>I hope you will make an afford to look agin at my question.
>>

>
I'm sorry, but I still don't understanding what you want to achieve.
>
>

>my own sql return this:
>205372 2008-05-02 08:04:58.143 2008-05-15
>10:43:09.580 13
>from b) to c).
>>

>
What you mean with "from b) to c)"? Your query return two rows,
which makes sense as far that in your list, b and c comes after
each other, but the second row has a different activity id, and
that does not match your list.
>
>

>I wan't from a) to c) [the row after statusid 26]
>>
>a) 853187 205372 20 2008-04-28
>12:29:50.627
>>
>853465 205372 19 2008-04-28
>19:27:05.383
>>
>853466 205372 10 2008-04-28
>19:27:09.180
>>
>b) 855032 205372 26 2008-05-02
>08:04:58.143
>>
>c) 860439 205372 19 2008-05-15
>10:43:09.580
>>
>860685 205372 21 2008-05-15
>14:27:06.670
>>

>
So you want to output five rows? But these rows have different columns
from your query produces? Or do you mean that the aggregation is to
include these five rows?
>
I could probably make some guess of what you really mean, but I would
really prefer if you could make an effort to explain from the beginning
what these columns means and what your query is supposed to achieve.
The query decently complex, and since you say it's wrong I have not dug
into it.
>
I'm awfully, but if you cannot express your business problem clearly,
it is very difficult to help you. You may know what you are talking
about when you say "from a) to c)", but I don't.
>
And did you ever say which version of SQL Server you are using?
>
>
>
>
>

[Henrik Hartig]

Correction

Henrik Hartig wrote:

The above shows the activities for one repairment. The
activityID=2053 72 is the identifier for that repairment and 853187 to
860685 is the details from we get the computer until we have repair
the computer. Only statusID=26 is interesting in this case.
StatusID=26 indicate that the technician have received some parts for
the computer (ex. a soundcard).

There might get parts more than one time pr. activity.

But I only wan't one for the output. Even if two repairment is over 9 days

[Henrik Hartig]

Re: select from row1 to row n

Erland Sommarskog wrote:

8.0 sounds like SQL 2000. The above solution will not run on SQL 2000.
There is no row_number on SQL 2000, and there are no CTEs.
>
row_number can be emulated with a correlated subquery that computes a
COUNT, but it is very inefficient with large volumes of data.
>
If you need a solution for SQL 2000, I will have to look into that later,
unless someone beats me to it.
>
I like to add one thing about the query above: it uses outer joins to b
and c. As the query is written, it works equally well with inner join.
I used outer join because I thought that maybe you wanted to list
activities for which there was no status = 26 yet, or no row after status
= 26. Then I read your requirements a little closer, and saw that you
only wanted to list those that were nine days apart. But you may need
to write similar queries, but where you want to include more data, and
in that case, the outer joins may be the right thing.
>
>

Sql can be complicated - I now experience

I would VERY much like a solution for sql 2000.

Looking forward to hear from you

[Erland Sommarskog]

Re: select from row1 to row n

Henrik Hartig (henrikhartig@v armmail.dk) writes:

I would VERY much like a solution for sql 2000.

OK, here it is:

SELECT b.ActivityID, a.firsttime, c.TimeStamp,
datediff(DAY, a.firsttime, c.TimeStamp)
FROM (SELECT ActivityID, firsttime = MIN(TimeStamp)
FROM TESTActivityDet ailsAll
GROUP BY ActivityID) AS a
JOIN (SELECT ActivityID, stat26time = MIN(TimeStamp)
FROM TESTActivityDet ailsAll
WHERE StatusID = 26
GROUP BY ActivityID
HAVING MIN(TimeStamp) BETWEEN '20080501' AND '20080531') AS b
ON a.ActivityID = b.ActivityID
JOIN TESTActivityDet ailsAll c
ON a.ActivityID = c.ActivityID
AND c.TimeStamp = (SELECT MIN(d.TimeStamp )
FROM TESTActivityDet ailsAll d
WHERE d.ActivityID = b.ActivityID
AND d.TimeStamp b.stat26time)
WHERE datediff(DAY, b.stat26time, c.TimeStamp) 9



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Henrik Hartig]

Re: select from row1 to row n

Erland Sommarskog wrote:

Henrik Hartig (henrikhartig@v armmail.dk) writes:
>

>I would VERY much like a solution for sql 2000.
>>

>
OK, here it is:
>
SELECT b.ActivityID, a.firsttime, c.TimeStamp,
datediff(DAY, a.firsttime, c.TimeStamp)
FROM (SELECT ActivityID, firsttime = MIN(TimeStamp)
FROM TESTActivityDet ailsAll
GROUP BY ActivityID) AS a
JOIN (SELECT ActivityID, stat26time = MIN(TimeStamp)
FROM TESTActivityDet ailsAll
WHERE StatusID = 26
GROUP BY ActivityID
HAVING MIN(TimeStamp) BETWEEN '20080501' AND '20080531') AS b
ON a.ActivityID = b.ActivityID
JOIN TESTActivityDet ailsAll c
ON a.ActivityID = c.ActivityID
AND c.TimeStamp = (SELECT MIN(d.TimeStamp )
FROM TESTActivityDet ailsAll d
WHERE d.ActivityID = b.ActivityID
AND d.TimeStamp b.stat26time)
WHERE datediff(DAY, b.stat26time, c.TimeStamp) 9
>
>
>
>

Thank you. You have been very helpfull :-)