Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Step 1: set up a temp table containing the desired "time blocks" that you want to total for. These blocks could be for any range of time; in your example, it would be one entry for ever day (24-hour period) in the month.</p> <pre><code>CREATE TABLE #TimeRanges ( RangeStart datetime not null ,RangeEnd datetime not null ) </code></pre> <p>Left-outer-joining this table on your data ensures you get at least one row per time block (day), even if there were no alarms occuring that day:</p> <pre><code>SELECT tr.RangeStart -- Use start of each time block to identify the block ,md.CompId -- With left outer join, will be 0 or more rows for each block ,sum(datediff(hh ,case when tr.RangeStart &gt; md.StartAlarmDate then tr.RangeStart else md.StartAlarmDate end ,case when tr.RangeEnd &gt; md.EndAlarmDate then tr.RangeEnd else md.EndAlarmDate end)) HoursInRange from #TimeRanges tr left outer join MyData md on md.StartAlarmDate &lt; tr.RangeEnd and md.EndAlarmDate &gt; tr.From group by tr.RangeStart ,md.CompId </code></pre> <p>(I can't test this code, some debugging may be required--but the concept is solid. I'll let you worry about rounding partial hours, and whether you want > and &lt;, or >= and &lt;= (things may get tricky if an alarm starts and/or ends at the exact same point in time as a block boundary).</p> <hr> <p><strong>Edit/Addenda</strong></p> <p>Here's a fairly basic way to set up the temp table used in the routine (this code, I tested):</p> <pre><code>-- Set up and initialize some variables DECLARE @FirstDay datetime ,@NumberOfDays int SET @FirstDay = 'Oct 1, 2011' -- Without time, this makes it "midnight the morning of" that day SET @NumberOfDays = 91 -- Through Dec 31 -- Creates a temporary table that will persist until it is dropped or the connection is closed CREATE TABLE #TimeRanges ( RangeStart datetime not null ,RangeEnd datetime not null ) -- The order in which you add rows to the table is irrelevant. By adding from last to first, I -- only have to fuss around with one variable, instead of two (the counter and the end-point) WHILE @NumberOfDays &gt;= 0 BEGIN INSERT #TimeRanges (RangeStart, RangeEnd) values ( dateadd(dd, @NumberOfDays, @FirstDay) -- Start of day ,dateadd(dd, @NumberOfDays + 1, @FirstDay)) -- Start of the next day SET @NumberOfDays = @NumberOfDays - 1 END -- Review results SELECT * from #TimeRanges order by RangeStart -- Not necessary, but doesn't hurt, especially when testing code DROP TABLE #TimeRanges </code></pre> <p>Note that by making RangeEnd the start of the next day, you have to be careful with your greaterthans and lessthans. The details can get very finicky and fussy there, and you'll want to do a lot of edge-case testing (what if alarm starts, or ends, exactly at Dec 16 2011 00:00.000). I'd go with that, because overall it's simpler to code for than for junk like 'Dec 16, 2011 23:59.997'</p>
    singulars
    1. This table or related slice is empty.
    1. PONeed SQL Server Query help, Newbie
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USPhilip Kelley
    1. USPhilip Kelley
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COFYI, the way I solve problems like this is with paper and pencil. Draw line segments for all possible overlapping and non-overlapping time-spans, and work out the logic accordingly.
      singulars
      1. PO
      1. USPhilip Kelley
    2. COIve never used a temp table before! How do I tell it what range to use? thanks for the help on this. I can do basic sql stuff but this advanced stuff is way over my head!
      singulars
      1. PO
      1. USAlbert Lambert
    3. COAdded the section on the temp table
      singulars
      1. PO
      1. USPhilip Kelley
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload