Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Determine time elapased by hour Expand / Collapse
Posted Saturday, November 2, 2013 5:42 AM



Group: General Forum Members
Last Login: Yesterday @ 4:42 PM
Points: 2,193, Visits: 7,778
brad.mason5 (11/1/2013)
Monster Maghoul thank you for sharing! This is awesome now I just need to understand what is going on here. This guy will perform well under load too!

You are most welcome.

It's really quite simple in terms of "how it works".

The link from Tally (the hours in a day) to your data is where the "hour of the day (n)" falls between the start and end time in your data.

This gives us every data row for each hour, then it is simply a CASE of working out how much of that hour each start/end pair has consumed and SUMming them up.


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw
  • Post #1510852
    Posted Wednesday, November 6, 2013 9:22 AM
    SSC Journeyman

    SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

    Group: General Forum Members
    Last Login: Sunday, May 3, 2015 8:09 AM
    Points: 92, Visits: 266
    Hi Guys,

    Thanks all for your input. I suppose the MM solution is as close as we are going to get to "eloquent", adn certainly beats mine !!

    Thanks again.

    Post #1511923
    « Prev Topic | Next Topic »

    Add to briefcase ««12

    Permissions Expand / Collapse