We help IT Professionals succeed at work.
Get Started

Difference between ending date of one row and starting date of next row...???

377 Views
Last Modified: 2012-08-13
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
Comment
Watch Question
This problem has been solved!
Unlock 1 Answer and 8 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE