SQL Query that calculates time

Question

Given these tables :

Item
-----
Id
Description
Status
CreatedBy

ItemLog
--------
Id
ItemId
NewStatus
TimeStamp
ChangedBy

Where Itemlog.ItemId = Item.Id, and Status = { "Created", "Pended", "Cancelled", "Completed" }...

How would you write a SQL query to generate the following results :

Item     Description     ChangeDate       NewStatus     ChangedBy
  1          Test1          2012-01-01    Created    User1
  1          Test1          2012-01-02    Pended     User2
  1          Test1          2012-01-03    Completed  User2
  2          Test2          2012-01-01    Created    User2
  2          Test2          2012-01-02    Pended     User3
  2          Test2          2012-01-09    Cancelled  User1
  3          Test3          2012-01-01    Created    User1
  3          Test3          2012-01-02    Pended     User1

Item   CurrentUser CurrentStatus CreatedOn     TotalTime    TimePended CompletedDate CancelledDate
Test1  User2        Completed     2012-01-01     3 days      1 day      2012-01-03   (null)
Test2  User1        Completed     2012-01-01     9 days      7 days     (null)       2012-01-09
Test3  User1        Pended        2012-01-01     35 days     34 days    (null)       (null)

Which I want to display as a master-detail report in my application.

The first resultset is a simple query with a couple of joins (I haven't included the UserId-UserName tables etc.)

The hard part is the calculation of the total times...

TimeToComplete is the TimeStamp of the last ItemLog minus the TimeStamp of the first ItemLog (where the ItemLog is ordered by date).

TimePended is the sum of the difference in TimeStamps between each change of the status from pended -> something.

Answer 1

Geez... Does that provide a big performance benefit over something like the following?

ALTER FUNCTION [dbo].[GetItemPendedTime] 
(
    -- Add the parameters for the function here
    @ItemId uniqueidentifier
)
RETURNS INT
AS
BEGIN
    -- Declare the return variable here
    DECLARE @status INT
    DECLARE @timespan datetime
    DECLARE @pendTimeMinutes INT = 0
    DECLARE @ispend bit = 0
    DECLARE @lastTimespan datetime  

    DECLARE itemlog_queue CURSOR FOR 
        SELECT Status, Timestamp FROM ItemLog WHERE ItemId = @ItemId

    OPEN itemlog_queue;
    FETCH NEXT FROM itemlog_queue INTO @status, @timespan

    WHILE @@FETCH_STATUS = 0
    BEGIN       
        IF @ispend = 1
            BEGIN
                SET @pendTimeMinutes += DATEDIFF(minute, @lastTimespan, @timespan)
            END
        IF @status = 13
            BEGIN
                SET @ispend = 1
            END
        ELSE
            BEGIN
                SET @ispend = 0         
            END
        SET @lastTimespan = @timespan
        FETCH NEXT FROM itemlog_queue INTO @status, @timespan
    END

    CLOSE itemlog_queue 
    DEALLOCATE itemlog_queue 

    IF @ispend = 1
    BEGIN
        SET @pendTimeMinutes = @pendTimeMinutes + DATEDIFF(minute, @lastTimespan, GETDATE())
    END

    -- Return the result of the function
    RETURN @pendTimeMinutes

END

And then call it like

SELECT Description, Status as CurrentStatus, dbo.GetItemPendedTime(ItemId) FROM Items

Answer 2

Shazam!

create table Item(ID int, Description varchar(200),createdby varchar(20))
create table ItemLog(ID int, ItemID int, NewStatus varchar(200), [TimeStamp] datetime, ChangedBy varchar(20))

insert into Item(ID,Description,CreatedBy) values(1, 'Test 1', 'User1')
insert into Item(ID,Description,CreatedBy) values(2, 'Test 2', 'User1')
insert into Item(ID,Description,CreatedBy) values(3, 'Test 3', 'User1')

insert into ItemLog(ID, ItemID, NewStatus, TimeStamp, ChangedBy) 
    values(1,1,'Created','1/1/2012','User1')
insert into ItemLog(ID, ItemID, NewStatus, TimeStamp, ChangedBy) 
    values(2,1,'Pended','1/2/2012','User2')
insert into ItemLog(ID, ItemID, NewStatus, TimeStamp, ChangedBy) 
    values(3,1,'Completed','1/3/2012','User2')
insert into ItemLog(ID, ItemID, NewStatus, TimeStamp, ChangedBy) 
    values(4,2,'Created','1/1/2012','User2')
insert into ItemLog(ID, ItemID, NewStatus, TimeStamp, ChangedBy) 
    values(5,2,'Pended','1/2/2012','User3')
insert into ItemLog(ID, ItemID, NewStatus, TimeStamp, ChangedBy) 
    values(6,3,'Cancelled','1/9/2012','User1')
insert into ItemLog(ID, ItemID, NewStatus, TimeStamp, ChangedBy) 
    values(7,3,'Created','1/2/2012','User1')
insert into ItemLog(ID, ItemID, NewStatus, TimeStamp, ChangedBy) 
    values(8,3,'Pended','1/2/2012','User1')

select * from item i left outer join itemlog il on il.ItemID = i.ID

select 
    i.Description,
    FirstStatusDate=minIL.TimeStamp,
    CurrentStatus=maxIL.NewStatus,
    CurrentStatusDate=maxIL.TimeStamp,
    CurrentUser=maxIL.ChangedBy,
    CompletedDate=(select max(TimeStamp) from ItemLog where ItemID=i.ID and NewStatus='Completed'),
    [TotalTime (in days)]=case 
        when 
            minIL.TimeStamp is not null and maxIL.TimeStamp is not null
            then datediff(day,minIL.TimeStamp,maxIL.TimeStamp)
        else
            convert(int,null)
        end,
    TimePending=sum(c.Days)
from
    Item i
    left outer join 
    (
        select 
            y.ItemID,
            y.MinDate,MinItemLogID=min(mn.id),
            y.MaxDate,MaxItemLogID=max(mx.id)
        from
            (
                select 
                    ItemID,MinDate=min(timestamp), MaxDate=max(timestamp)
                from 
                    ItemLog il group by ItemID
            ) as y
            left outer join ItemLog mn on mn.ItemID=y.ItemID and mn.TimeStamp=y.MinDate
            left outer join ItemLog mx on mx.ItemID=y.ItemID and mx.TimeStamp=y.MaxDate
        group by
            y.ItemID, y.MinDate, y.MaxDate
    )
    z on z.ItemID = i.ID
    left outer join ItemLog minIL on minIL.ID = z.MinItemLogID
    left outer join ItemLog maxIL on maxIL.ID = z.MaxItemLogID
    left outer join 
    (
        select
            p.ItemId,
            PendTime=p.TimeStamp,
            PendID=p.ID,
            Days=datediff(day,p.TimeStamp,
                coalesce(
                    (select min(TimeStamp) 
                    from 
                        ItemLog b 
                    where 
                        b.ItemID = P.ItemID and TimeStamp > p.TimeStamp )
                    ,
                    getdate()
                )
            )
        from
            ItemLog p 
        where
            p.NewStatus='Pended'
    ) c on c.ItemID = i.ID
group by
    i.ID,
    i.Description,
    minIL.TimeStamp,
    maxIL.NewStatus,
    maxIL.TimeStamp,
    maxIL.ChangedBy