Hi -
I need to create a report (eventaully in SSRS but I'm stuck on the T-SQL portion) that shows the elapsed time between a product's refrigerations (warm time). I was able to get this to work on individual products, but the users will want to view this data for a date range or range of products.
The data looks like...
ID Row# RefrigerationStartOn RefrigerationEndOn
abc 1 1/1/12 01:00 1/1/12 02:00
abc 2 1/1/12 02:30 1/1/12 04:00
abc 3 1/1/12 17:05 1/1/12 18:00
lmn 1 1/7/12 13:30 1/7/12 14:00
zyx 1 1/12/12 10:00 1/12/12 23:45
zyx 2 1/13/12 00:50 1/13/12 14:50
So the separate warm time results would look like....
ID Warm Time
abc 0:30 (02:00-02:30)
abc 13:05 (04:00-17:05)
lmn NULL
zyx 1:05 (1/12 23:45 - 1/13 00:50)
Or...if possible...(but trust me I would be happy with the above!) Total warm times....
ID Warm Time
abc 13:35
lmn NULL
zyx 1:05
The code and temp tables that worked for an individual product are....
SELECT * INTO TempTableRefrigeration FROM
(
SELECT
r.ProductID
, o.ShortName
, dbo.ConvertToOrgTimeZone(RefrigerationStartOn, o.ID) AS Start
, dbo.ConvertToOrgTimeZone(RefrigerationEndOn, o.ID) AS [End]
, ROW_NUMBER() OVER(ORDER BY RefrigerationStartOn ASC) AS RowNumber
FROM
DCITRAINING.dbo.Referral AS ref JOIN
DCITRAINING.dbo.Organization AS o On o.ID = ref.ReferringOrganizationID LEFT JOIN
DCITRAINING.dbo.TissueDonorRefrigeration AS r ON r.PatientID = ref.PatientID JOIN
TC_MasterPatientData AS p ON p.[PatientID-UID] = r.PatientID
WHERE r.PatientID = 'A0BA4B52-39A7-4439-AC84-4ABD203FD4DC' And (r.Deleted = 0)
) AS DT
/*SECOND Temp table to hold the calculated WITs*/
select * into Temp from (
SELECT
DateDiff(minute, (select [End] from TempTableRefrigeration WHERE RowNumber = 1),
(select Start from TempTableRefrigeration WHERE RowNumber = 2)) AS WITFirst
, DateDiff(minute, (select [End] from TempTableRefrigeration WHERE RowNumber = 2),
(select Start from TempTableRefrigeration WHERE RowNumber = 3)) AS WITSecond
, DateDiff(minute, (select isnull([End], 0) from TempTableRefrigeration WHERE RowNumber = 3),
(select isnull(Start, 0) from TempTableRefrigeration WHERE RowNumber = 4)) AS WITThird
, DateDiff(minute, (select [End] from TempTableRefrigeration WHERE RowNumber = 4),
(select Start from TempTableRefrigeration WHERE RowNumber = 5)) AS WITFourth
FROM
TempTableRefrigeration AS r1 JOIN
TC_MasterProductData AS p ON p.[ProductID] = r1.ProductID JOIN
MTData AS t ON t.[ProductID] = r1.ProductID
group by r1.ProductID
) as dt
/*Code used for total Warm Time*/
select WITDeath, WITFirst, WITSecond, WITThird, WITFourth, WITIncision,
SELECT
CONVERT(VARCHAR, (Sum(ISNULL(WITFirst, 0) + ISNULL(WITSecond, 0) + ISNULL(WITThird, 0) + + ISNULL(WITFourth, 0))/60))
+':'+ RIGHT('00' +
CONVERT(VARCHAR, (Sum(WITFirst + ISNULL(WITSecond, 0) + ISNULL(WITThird, 0) + + ISNULL(WITFourth, 0))%60)), 2)
AS TotalWIT
FROM Temp
GROUP BY WITFirst, WITSecond, WITThird, WITFourth
I am happy to use anything to make this work (temp tables, table variables, subqueries, dynamic sql), I just don't know what that would be. Thank you in advance for your time and help with this, and please let me know if I can provide anything else to help work on this!
Jessica