SQL Query

[grimstoner]

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 TimeToComplete TimePended CompletedDate CancelledDate
  1    User2     Completed     2012-01-01     3 days    1 day      2012-01-03     (null)
  2    User1     Completed     2012-01-01     9 days    7 days     (null)         2012-01-09
  3    User1     Pended        2012-01-01     (null)    30 days    (null)         (null)

Which will then be showed as a master-detail report. CurrentUser in the second resultset is that last user to change the status. TimePended is the sum of the difference in datetimes between each change of the status from pended -> something.

 

[Dan C]

Only way I can see is to create more than one sql statement .. i.e create views to calc the differences then select the last user (but you going to have to query the min and max of the datetimes first). It can be done in one statement , but my mind on a Saturday is not working so well

Maybe some brighter sparks got better ideas

 

[NotFound]

First result:

SELECT 
	Item.id AS Item, 
	Description, 
	TimeStamp AS ChangeDate, 
	NewStatus, 
	ChangedBy 
FROM 
	Item
	JOIN ITEMLOG ON Item.ID = ItemLog.ItemID

Not sure how optimised the following is but it will give you the result you want:

SELECT 
	Item.id AS Item, 
	CurrentIL.ChangedBy AS CurrentUser,
	CurrentIL.NewStatus AS CurrentStatus,
	CreatedIL.TimeStamp as CreatedOn,
	CASE
		  WHEN CompletedIL.TimeStamp IS NOT NULL THEN DATEDIFF(dd, CreatedIL.TimeStamp, CompletedIL.TimeStamp)
		  WHEN CancelledIL.TimeStamp IS NOT NULL THEN DATEDIFF(dd, CreatedIL.TimeStamp, CancelledIL.TimeStamp)
	 END AS TimeToComplete,
	CASE
		  WHEN CompletedIL.TimeStamp IS NOT NULL AND PendIL.TimeStamp IS NOT NULL THEN DATEDIFF(dd, PendIL.TimeStamp, CompletedIL.TimeStamp)
		  WHEN CancelledIL.TimeStamp IS NOT NULL AND PendIL.TimeStamp IS NOT NULL THEN DATEDIFF(dd, PendIL.TimeStamp, CancelledIL.TimeStamp)
		  WHEN PendIL.TimeStamp IS NOT NULL THEN DATEDIFF(dd, PendIL.TimeStamp, GetDate())
	 END AS TimeToComplete,
	CompletedIL.TimeStamp AS CompletedDate,
	CancelledIL.TimeStamp AS CancelledDate
FROM 
	Item
	LEFT JOIN ITEMLOG CurrentIL 
	ON 	Item.ID = CurrentIL.ItemID 
	AND CurrentIL.TimeStamp = (SELECT MAX(TIMESTAMP) FROM ItemLog WHERE ItemLog.ItemID = Item.ID)
	LEFT JOIN ITEMLOG CreatedIL 
	ON 	Item.ID = CreatedIL.ItemID 
	AND CreatedIL.NewStatus = 'Created'
	LEFT JOIN ITEMLOG PendIL 
	ON 	Item.ID = PendIL.ItemID 
	AND PendIL.NewStatus = 'Pended'
	LEFT JOIN ITEMLOG CancelledIL 
	ON 	Item.ID = CancelledIL.ItemID 
	AND CancelledIL.NewStatus = 'Cancelled'
	LEFT JOIN ITEMLOG CompletedIL 
	ON 	Item.ID = CompletedIL.ItemID 
	AND CompletedIL.NewStatus = 'Completed'