Below is a CTE I am currently using to count how many times it took to send a transmission over a given period of time. The CTE works by sharing the time to send in seconds (HT.ElapsedTime) by 60. This then produces a value such as 4, 6, 10 etc.
CTE_Duplicated Records_Count
WITH CTE_Duplicate_Records_Count AS
(
SELECT
H.ObjectGuid AS Transmission_ID
, H.TRDateTime AS [Transmission Start Time]
, HT.[ElapsedTime] / 60 AS [totalCount]
FROM [dbo].History AS H (NOLOCK)
LEFT OUTER JOIN [dbo].HistoryTRX AS HT (NOLOCK) ON H.handle = HT.handle
The main select statement pulls through the necessary columns from the transmission table as well as joins the CTE above onto it where the transmission id's match.
Main Select Statement
SELECT
-- Transmission Table
---------------------
H.ObjectGuid AS Transmission_ID
, CAST(H.[TRDateTime] AS DateTime) AS [Transmission Start Date]
, CAST(DATEADD(second, HT.ElapsedTime, H.TRDateTime) AS DateTime) AS [Transmission End Date]
, HT.[ElapsedTime] AS [Time taken to send in seconds]
, HT.GoodPageCount AS Pages
, HT.[ChannelUsed]
-- CTE Duplicate Records Count
--------------------------
, DRC.Transmission_ID
, DRC.totalCount
FROM [dbo].History AS H (NOLOCK)
LEFT OUTER JOIN [dbo].HistoryTRX AS HT (NOLOCK) ON H.handle = HT.handle
INNER JOIN CTE_Duplicate_Records_Count DRC ON Transmission_ID = H.ObjectGuid
WHERE
DRC.Transmission_ID = 'BFE008F7-F9D6-44D8-B06C-916875FC0F6D'
ORDER BY H.ObjectGuid DESC
As an example the above select statement produces something like so:
TransmissionID
Transmission Start Date
Transmission End Date
Time Taken in seconds
Pages
TotalCount
BBBBBB
2015-08-04 12:42:45
2015-08-04 12:56:45
840
5
14 (minutes)
The desired result is for each record to be duplicated by the totalCount number. I.e. the above record would be duplicated 14 times. Also for each row duplicated I would like to add 60 seconds to the start time.
Any ideas as to how this can be achieved?
