Databases
StackOverflow2013
Postsdbo
PONeed to write SQL Server stored procedure to save one or more rows based on time calculation
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONeed to write SQL Server stored procedure to save one or more rows based on time calculation
    primarykey
    data
    text
    <p>I need to write a function that rounds time in one column, called StartTime, to display just the hour and insert it to another column called StartHour. In another column, called EndTime, I also need to use DATEPART to round up to the next incremented hour (ex: 23:33:00.0000000 would be 23 (the hour) + 1 (to round it up to the end hour) = 24 and I would need to save that to the EndHour column). </p> <p>I want to insert these new values into new columns called StartHour and StartMinute, EndHour, and EndMinute (the StartTime and EndTime columns are the <strong>original values</strong> I'm working with in time(7) format and for historical purposes, I'm keeping them in their original columns). </p> <p>Here's what I have so far in T-SQL:</p> <pre><code>SELECT (DATEPART(HOUR, [StartTime])) AS StartHour,(DATEPART(MINUTE, [StartTime])) AS StartMinute, (DATEPART(HOUR, [EndTime])) AS EndHour, (DATEPART(MINUTE, [EndTime])) AS EndMinute, StartTime, EndTime FROM [test].[dbo].[Outage_Reports] ORDER BY OutageDate ASC </code></pre> <p>Which produces:</p> <pre><code>StartHour StartMinute EndHour EndMinute Startime EndTime 16 0 17 30 16:00:00 17:30:00 </code></pre> <p><strong>I now need to write this conversion into a stored procedure that also inserts two (or more if the outage goes on for several hours) new rows into the table when the length between start and end is more than one hour. Or just inserts one row when the outage was below or equal to an hour. And then I need to account for that hourly progression in the start and end columns.</strong> Like this:</p> <pre><code> StartHour StartMinute EndHour EndMinute Startime EndTime 16 0 17 00 16:00:00 17:30:00 </code></pre> <p>(the above reflects the first hour of outage, the second row below reflects the second half hour of the outage until it stopped...both will be tied to the same outage ticket in the table)</p> <pre><code> StartHour StartMinute EndHour EndMinute Startime EndTime 17 30 18 00 16:00:00 17:30:00 </code></pre> <p>The idea is to track website outages hour by hour so they can join to an orders table that tracks orders by hour (and the orders never contain minutes...just hours). So the plan is to make two rows for an outage that goes on for 1.5 hours so the 16, 17 and all the way through the rounded up 18 hourly values can be tied to the orders table with the 30 minute column to act as another point of calculation (so an hour and a half would equal a full hour's worth of orders plus a half hour's worth of orders...split out row by row). This way I can better track trends during outages.</p> <p>I'm a bit at a loss as to how to write this logic to a stored procedure. I would conceivably have to declare the new hour and minute values into variables and for the StartHour column always keep it rounded to the DATEPART hour value (even if it was 16:45...I want to insert 16 into the StartHour column). However, with the EndHour column, I want to insert <strong>a rounded up hourly value</strong> (17 and then 18 because we went over an hour in that case) <strong>into the EndHour column</strong>. This would hopefully describe (via these two rows) the close to two hours the outage occurred. And in turn it would allow for an easy join to our orders table</p> <p>Any guidance on a stored procedure for this logic would be appreciated.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USLynn
    1. USLynn
    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. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COIf an outage ends *exactly* on the hour (some chance, I know, but maybe even within the first minute of the hour), do you still round up to the next hour? Also, do you have to deal with outages that span across midnight?
      singulars
      1. PONeed to write SQL Server stored procedure to save one or more rows based on time calculation
      1. USDamien_The_Unbeliever
    2. COYes to both of your questions. If it begins at 3:00 and the outage begins at 3:01...we would record it as StartHour 3 and EndHour 4 and then use the minutes of duration (this is a separate calc I'll be doing) to determine length. We then apply that calculation to the amount of orders. All I need for now is to know how to get the outage length, hour by hour, into the table. Thanks!
      singulars
      1. PONeed to write SQL Server stored procedure to save one or more rows based on time calculation
      1. USLynn
    3. COFor my first question, I meant - if the outage starts at 2:45, and *ends* at 3:00, do we insert one row (2-3) or two rows (2-3 and 3-4)?
      singulars
      1. PONeed to write SQL Server stored procedure to save one or more rows based on time calculation
      1. USDamien_The_Unbeliever
 

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