Besværlig sql - Stored Procedure?

Det er godt nok ikke lige den mest elegante løsning jeg længe har fået strikket sammen, men så vidt jeg lige kan forstå, så giver den vist det du vil have.

Jeg har lavet nogle antagelser ud fra din ovenstående beskrivelse. Den dato-difference du er ude efter, er altid tidsrummet der går fra en ActivityID går FRA at være status=26 til noget andet. Hvis en ActivityID går fra 26 til 28 til 30, så giver nedenstående kode KUN differencen i skiftet fra 26 til 28.

Der er et par steps i koden, hvor jeg først får sorteret nogle uinteressante rækker fra, og sidenhen laver den en join med sig selv, for at have "fra-timestamp'et" og "til-timestamp'et" i samme rækker - så man kan lave en DATEDIFF på dem.

Prøv at se om det giver det resultat du ønsker. Den nederste select kan de tilpasse, så du kun får de kolonner ud du har behov for. Jeg har lavet det, så hver række indeholder oplysninger om fra-informationerne og til-informationerne, samt differencen i datoerne i dage.

WITH CTE1
AS
(
SELECT
    ROW_NUMBER() OVER(PARTITION BY ActivityID ORDER BY Timestamp) AS RowNumber,
    *
FROM ActivityDetailsAll
)

SELECT * INTO #tmp
FROM CTE1 t1
WHERE RowNumber IN (SELECT RowNumber + 1 FROM CTE1 t2 WHERE t2.ActivityID = t1.ActivityID AND t2.StatusID = 26)
OR RowNumber IN (SELECT RowNumber FROM CTE1 t2 WHERE t2.ActivityID = t1.ActivityID AND t2.StatusID = 26)
ORDER BY ActivityID, Timestamp
GO

DELETE FROM #tmp
WHERE ActivityID IN (
    SELECT ActivityID
    FROM #tmp
    GROUP BY ActivityID
    HAVING COUNT(*) = 1
)
GO

WITH CTE2
AS
(
SELECT
    ROW_NUMBER() OVER (ORDER BY ActivityID, Timestamp) AS ID,
    *
FROM #tmp
)

SELECT
    t1.ActivityDetailID AS from_ActivityDetailId,
    t2.ActivityDetailID AS to_ActivityDetailId,
    t1.ActivityID AS from_ActivityId,
    t2.ActivityID AS to_ActivityId,
    t1.StatusID AS from_StatusID,
    t2.StatusID AS to_StatusID,
    t1.Timestamp AS from_Timestamp,
    t2.Timestamp AS to_Timestamp,
    DATEDIFF(dd, t1.Timestamp, t2.Timestamp) AS datediff_in_days
FROM CTE2 t1
INNER JOIN CTE2 t2 ON t1.ID = t2.ID -1 
WHERE
    t1.StatusID = 26
    AND t1.StatusID <> t2.StatusID
GO
   

DROP TABLE #tmp

Hej Sjang
Dine antagelser omkring statusid er helt korrekte. Lidt forklaring
StatusID=26, betyder at en reservedel er modtaget. Ændrer status sig til noget andet, betyder det at reservedelen bliver brugt. Det er netop det tidsinterval mellem reservedelen modtaget og til den kommer i brug jeg ønsker. Så ja dine antagelse er korrekt :-).
Din Sql - som jeg ikke helt kan gennemskue - står bare og kører derudaf, uden at spytte et resultat ud..

Jeg har ikke skrevet det oprindeligt, men jeg ønsker et sql-forespørgsel i et tidsinterval. F.eks. Januar md. 2008. Ved at ilægge et tidsinterval, vil det måske også give mig et resultat af din sql. Måske du vil være mig yderligere behjælpelig med det???

Jeg prøvede også kun på et meget lille dataset - faktisk kun på de 10 linjer du angav i dit oprindelige spørgsmål.

Hvis du skal have afgrænset dig til et tidsrum, så kan denne lettere modificerede udgave vist hjælpe dig. Der er tilføjet et par linjer aller øverst, og så er der tilføjet en WHERE-klausul i den første select-statement. Prøv om det måske speeder tingene lidt op:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2008-01-01'
SET @EndDate = '2008-01-31'
;
WITH CTE1
AS
(
SELECT
    ROW_NUMBER() OVER(PARTITION BY ActivityID ORDER BY Timestamp) AS RowNumber,
    *
FROM ActivityDetailsAll
WHERE Timestamp BETWEEN @Startdate AND @EndDate
)

SELECT * INTO #tmp
FROM CTE1 t1
WHERE RowNumber IN (SELECT RowNumber + 1 FROM CTE1 t2 WHERE t2.ActivityID = t1.ActivityID AND t2.StatusID = 26)
OR RowNumber IN (SELECT RowNumber FROM CTE1 t2 WHERE t2.ActivityID = t1.ActivityID AND t2.StatusID = 26)
ORDER BY ActivityID, Timestamp
GO

DELETE FROM #tmp
WHERE ActivityID IN (
    SELECT ActivityID
    FROM #tmp
    GROUP BY ActivityID
    HAVING COUNT(*) = 1
)
GO

WITH CTE2
AS
(
SELECT
    ROW_NUMBER() OVER (ORDER BY ActivityID, Timestamp) AS ID,
    *
FROM #tmp
)

SELECT
    t1.ActivityDetailID AS from_ActivityDetailId,
    t2.ActivityDetailID AS to_ActivityDetailId,
    t1.ActivityID AS from_ActivityId,
    t2.ActivityID AS to_ActivityId,
    t1.StatusID AS from_StatusID,
    t2.StatusID AS to_StatusID,
    t1.Timestamp AS from_Timestamp,
    t2.Timestamp AS to_Timestamp,
    DATEDIFF(dd, t1.Timestamp, t2.Timestamp) AS datediff_in_days
FROM CTE2 t1
INNER JOIN CTE2 t2 ON t1.ID = t2.ID -1 
WHERE
    t1.StatusID = 26
    AND t1.StatusID <> t2.StatusID
GO
   

DROP TABLE #tmp

Hej Sjang
Tak for dit store arbejde og hjælp. Sql'en giver lige det jeg vil ha'. Desværre er der lang responsetid. 3 dage tog 3½ minut. Jeg skal lægge hver md. ind, så brugerne bliver nok ikke så tilfredse...

I den forbindelse et lille tillægsspørgsmål:
Vil det være metoden at lægge din sql som en stored Procedure? Og vil jeg kun vinde performance derved?

Du har svaret på mit spørgsmål og det er jeg glad for :-). Hvis du vil lægge et svar og evt. svare på mit tillægsspørgsmål, så vil jeg gi' dig point
Hilsen
Anders

Her er et svar.

Mht. performance, så vil det nok ikke gøre noget mærkbart om man fyrer sql'en af direkte, eller om man putter det i en stored procedure. Men det der derimod virkelig kan gøre en kæmpe forskel, er hvorvidt man har de nødvendige indexes på tabellen. Har du nogle indexer, og i så fald hvilke?
Og hvor mange rækker er det ca. vi taler om pr. måned, og hvor mange rækker er der totalt i tabellen?

Nu er det meget svært at gennemskue en så kompleks query.

Men muligvis tror jeg at følgende kan levere svaret:

SELECT aid,fromstatus,tostatus,days FROM (
SELECT ada1.activityid AS aid, ada1.status as fromstatus, ada2.status as tostatus, ada1.ts AS t1, ada2.ts AS t2, DATEDIFF(dd,ada1.ts,ada2.ts) AS days
FROM activitydetailsall ada1, activitydetailsall ada2
WHERE ada1.activityid = ada2.activityid AND ada1.status=26 AND ada2.ts > ada1.ts
) x WHERE t2 = (SELECT MIN(ts) FROM activitydetailsall WHERE activityid = aid AND ts > t1)
GO

Bemærk at jeg har omdøbt timestamp feltet til ts !

Tak til jer begge!
Og Arne, din sql gav rigtigt resultat, samtidigt med den var en del hurtigere ;)