een række udfra flere rækker
HeyLidt kryptisk titel, men jeg prøver her at forklare:
Jeg ønsker at finde sluttidspunktet(række-slut) - starttidspunktet(række 1) fra en sql-forespørgsel der returnerer flere rækker
Jeg har lavet et script, der laver en tabel og tilføjer rækker, for at hjælpe til at forstå mit problem.
Der returneres følgende:
SELECT * FROM ActivityDetails WHERE ActivityID = 205561
ActivityDetailID ActivityID StatusID Tidspunkt
855312 205561 20 2008-05-02 12:44:03.367
855313 205561 19 2008-05-02 12:44:24.477
.
.
.
875217 205561 21 2008-06-24 09:01:26.647
SELECT * FROM ActivityDetails WHERE ActivityID = 204174
841334 204174 17 2008-04-02 14:43:44.033
841357 204174 5 2008-04-02 14:55:42.753
.
.
875341 204174 21 2008-06-24 11:02:01.970
Jeg ønsker følgende udprint (og problemET ligger i at kunne trække en række fra en anden og få det samlede i en række [lidt kryptisk formulering, men jeg håber I forstår]):
Activityid Start slut dage
205561 2008-04-02 2008-06-24 83
204174 2008-05-02 2008-06-24 53
Håber I kan hjælpe mig. Her er mit script :-)
CREATE TABLE ActivityDetails
(
ActivityDetailID INT IDENTITY NOT NULL,
ActivityID INT NOT NULL,
StatusID SMALLINT NOT NULL,
Tidspunkt DATETIME NOT NULL
)
USE Accounting
TRUNCATE TABLE ActivityDetails
SET IDENTITY_INSERT ActivityDetails ON
-- ActivityID = 205561
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 855312, 205561, 20, '2008-05-02 12:44:03.367' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 855313, 205561, 19, '2008-05-02 12:44:24.477' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 855315, 205561, 10, '2008-05-02 12:46:09.980' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 859824, 205561, 24, '2008-05-14 12:23:19.243' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 864062, 205561, 24, '2008-05-23 13:23:10.663' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 865572, 205561, 24, '2008-05-28 09:10:37.907' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 868315, 205561, 24, '2008-06-04 09:59:04.473' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 871772, 205561, 24, '2008-06-13 14:14:20.730' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 874247, 205561, 24, '2008-06-20 09:19:02.660' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 875138, 205561, 26, '2008-06-24 08:27:03.043' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 875195, 205561, 19, '2008-06-24 08:53:33.690' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 875217, 205561, 21, '2008-06-24 09:01:26.647' )
-- ActivityID = 204174
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 841334, 204174, 17, '2008-04-02 14:43:44.033' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 841357, 204174, 5, '2008-04-02 14:55:42.753' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 841379, 204174, 5, '2008-04-02 15:03:19.833' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 843134, 204174, 5, '2008-04-07 10:45:59.920' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 843434, 204174, 5, '2008-04-07 14:47:54.233' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 843437, 204174, 13, '2008-04-07 14:50:57.840' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 843510, 204174, 7, '2008-04-07 15:38:06.830' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 844617, 204174, 20, '2008-04-09 10:56:15.763' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 845090, 204174, 19, '2008-04-10 08:10:03.010' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 845104, 204174, 10, '2008-04-10 08:20:32.420' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 847915, 204174, 24, '2008-04-16 06:38:10.030' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 849343, 204174, 24, '2008-04-21 08:31:45.677' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 853926, 204174, 24, '2008-04-29 11:33:10.590' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 856479, 204174, 24, '2008-05-06 11:23:47.327' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 858168, 204174, 24, '2008-05-09 08:58:31.260' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 859898, 204174, 24, '2008-05-14 13:04:49.150' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 859912, 204174, 24, '2008-05-14 13:08:24.433' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 864059, 204174, 24, '2008-05-23 13:21:47.880' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 865568, 204174, 24, '2008-05-28 09:09:42.937' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 868330, 204174, 24, '2008-06-04 10:07:28.900' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 871491, 204174, 24, '2008-06-13 09:09:36.333' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 874154, 204174, 24, '2008-06-20 08:23:10.650' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 875161, 204174, 26, '2008-06-24 08:34:00.110' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 875284, 204174, 19, '2008-06-24 09:49:48.877' )
INSERT INTO ActivityDetails (ActivityDetailID, ActivityID, StatusID, Tidspunkt)
VALUES ( 875341, 204174, 21, '2008-06-24 11:02:01.970' )
SET IDENTITY_INSERT ActivityDetails OFF
SELECT * FROM ActivityDetails WHERE ActivityID = 205561
SELECT * FROM ActivityDetails WHERE ActivityID = 204174
