December 6, 2013 at 10:54 am
I'm looking for a solution where I can take a single user entry with a start time, stop time and total occurrences and split it into multiple records based on the total number of occurrences. It would divide the total number of minutes from start/stop time by the total occurrences and then insert those parsed records into another table. There would be several thousand of these records to parse so I'm thinking I create a CTE to hold the calculated minutes per record after parse, then run the whole thing through a cursor or something but wanted to see if there were other ideas.
For example:
A user enters start time of 9:00 and stop time of 9:12 with total occurrences of 6. So the result is 6 records with 2 minutes apiece.
Record StartTime 9:00 EndTime 9:12 Occurrences 6
I need to create the following as the end result:
Record1 StartTime 9:00 EndTime 9:02
Record2 StartTime 9:02 EndTime 9:04
Record3 StartTime 9:04 EndTime 9:06
Record4 StartTime 9:06 EndTime 9:08
Record5 StartTime 9:08 EndTime 9:10
Record6 StartTime 9:10 EndTime 9:12
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
December 6, 2013 at 1:18 pm
This feels like homework...
I'm having a hard time figuring out how this would be used and what use it provides?
CEWII
December 6, 2013 at 1:37 pm
This is actually for a client I help support. They have a system that they enter downtime entries in but it doesn't allow for individual entries to be bulk loaded...they have to hand enter each incident. Instead of paying the application vendor to edit the front end...they are building a simple web page and they can enter the data as I explained. I think need to write a parsing script that will simulate them entering each line item individually. Thereby saving them a huge amount of time per operator each day.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
December 6, 2013 at 2:22 pm
You can accomplish this by using a Tally table. If you don't know what it is, check the following article: http://www.sqlservercentral.com/articles/T-SQL/62867/
In this example, I'm using a CTE for your sample data which you need to replace with your actual table, and a cteTally instead of a physical Tally Table.
WITH
SampleData(StartTime, EndTime, Occurrences) AS(
SELECTCAST( '09:00' AS time),
CAST( '09:12' AS time),
6
),
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
cteTally(N) AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2
)
SELECT N,
DATEADD( SS, (DATEDIFF( SS, StartTime, EndTime) / Occurrences) * (N - 1), StartTime),
DATEADD( SS, (DATEDIFF( SS, StartTime, EndTime) / Occurrences) * (N), StartTime)
FROM SampleData s
JOIN cteTally c ON s.Occurrences >= c.N;
Note that I'm limiting the rows to 100. If you need more, add another cross join.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply