Databases
StackOverflow2013
Postsdbo
POAggregating schedule data by start and end times
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAggregating schedule data by start and end times
    text
    copied!<p>I have a tricky problem that has had me scratching my head for a little while. I have some data that represents the delivery of "widgets" over a variable number of days, broken down into half hourly slots. </p> <p>For example (apologies for formatting - haven't quite got to grips with it):</p> <pre><code>Date Time NoOfUnits 01-Mar-2010 00:00 0 01-Mar-2010 00:30 0 01-Mar-2010 01:00 0 .... (following half hour intervals have NoOfUnits = 0) 01-Mar-2010 23:00 10 01-Mar-2010 23:30 10 02-Mar-2010 00:00 10 .... (following half hour intervals have NoOfUnits = 1) 02-Mar-2010 07:00 10 02-Mar-2010 07:30 0 .... (following half hour intervals have NoOfUnits = 0) 02-Mar-2010 23:30 0 </code></pre> <p>I need to generate a query that will allow me to group this data into all the distinct blocks where I am delivering a unit. In the above example I need to identify only 1 block - 23:00 to 07:00, and the sum of units for that block (160). The required result would therefore be StartTime, EndTime, TotalNoOfUnits.</p> <p>However, the complexity comes when we have different patterns of delivery - maybe we have a day where we deliver units for 24 hours.</p> <p>I need to be able to query data in the format above and identify all the unique StartTime, EndTime and TotalNoOfUnits combinations where NoOfUnits &lt;> 0.</p> <p>Apologies again for formatting and a slightly rambling question. Please ask any questions you need for me to clarify things.</p> <p><strong>EDIT:</strong> Just to be clear, the data will always run from 00:00 to 23:30 for each day of delivery, and each half hour slot will always be present. It is only the number of days and unit per half hour slot that may vary for any given data set.</p> <p><strong>EDIT2:</strong> Below is a script that will populate a table with 2 days worth of schedule data. The schedule is the same for both days. The result I would expect to see, based on my requirements, would be 13:00, 00:00, 230. As you will see from the below my SQL skills are not great hence the head scratching!</p> <pre><code>declare @DayCount int declare @HalfHourCount int declare @HH int declare @CurrentDate datetime declare @BaseDate datetime declare @NoOfUnits int set @HalfHourCount = 48 set @DayCount = 4 set @BaseDate = '1 Jan 1900' create table #Schedule ( Date datetime , Time datetime , NoOfUnits int ) while @DayCount &gt; 0 begin set @CurrentDate = dateadd(dd, @DayCount * -1, CONVERT(VARCHAR(10),GETDATE(),111)) set @HH = @HalfHourCount while @HH &gt; 0 begin if @HH &gt; 24 set @NoOfUnits = 10 else begin if @DayCount = 4 and @HH &lt; 10 set @NoOfUnits = 10 else set @NoOfUnits = 0 end insert into #Schedule(Date, Time, NoOfUnits) values (@CurrentDate, dateadd(mi, @HH / 2.0 * 60, @BaseDate), @NoOfUnits) select @HH = @HH - 1 end set @DayCount = @DayCount - 1 end </code></pre> <p>Expected result (although test data should start from 00:00 and go to 23:00 instead of 00:30 to 00:00):</p> <pre><code>StartTime TotalUnits EndDate ----------------------- ----------- ----------------------- 1900-01-01 00:30:00.000 90 1900-01-01 04:30:00.000 1900-01-01 12:30:00.000 960 1900-01-02 00:00:00.000 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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