Hi there,
I have this dataset:
CREATE TABLE #yourtable
([Description] varchar(3), [Start Date] datetime, [End Date] datetime)
;
INSERT INTO #yourtable
([Description], [Start Date], [End Date])
VALUES
('ABC', '2015-08-17 10:30:30', '2015-08-17 13:00:30'),
('ABC', '2015-08-18 11:00:00', '2015-08-18 11:30:00'),
('DEF', '2015-08-17 08:25:00', '2015-08-17 10:30:00')
;
I need an ouput like this, what should I do ?
Any help would be appreciated.
Thanks
please explain why 2015-08-17 starts with 10 and not 5 or9 or 7
because the event start at 10:30:30 and ends in 13:00:30
Looks a bit messy, but I belive it'll work for your situation:
with cte_tally
as (select row_number() over(order by (select null))-1 as n
from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as cte_tally1(n) /* 12 hours */
/*****
* uncomment one line at the time, if above hour range is too small
cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as cte_tally2(n) /* 144 hours = 6 days */
cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as cte_tally3(n) /* 1728 hours = 72 days ~= 2½ months */
*
*****/
)
select a.[description]
,cast(dateadd(hour,b.n,a.[start date]) as date) as [date]
,datepart(hour,dateadd(hour,b.n,a.[start date])) as hour
,datediff(second
,case
when b.n=0
then a.[start date]
else dateadd(hour,datediff(hour,0,a.[start date])+b.n,0)
end
,case
when dateadd(hour,b.n+1,a.[start date])>=a.[end date]
then a.[end date]
else dateadd(hour,datediff(hour,0,a.[start date])+b.n+1,0)
end
)
as [duration(secs)]
,datediff(second
,case
when b.n=0
then a.[start date]
else dateadd(hour,datediff(hour,0,a.[start date])+b.n,0)
end
,case
when dateadd(hour,b.n+1,a.[start date])>=a.[end date]
then a.[end date]
else dateadd(hour,datediff(hour,0,a.[start date])+b.n+1,0)
end
)
/60.0
as [duration(min)]
from #yourtable as a
inner join cte_tally as b
on b.n<=datediff(hour,a.[start date],a.[end date])
order by a.[description]
,a.[start date]
,b.n
;
INSERT INTO #yourtable VALUES
('TTT', '2015-08-17 07:11:00', '2015-08-20 23:44:00') /*confirm cross-days handled*/
;WITH
cteTally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally1000 AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
FROM cteTally10 c1
CROSS JOIN cteTally10 c2
CROSS JOIN cteTally10 c3
)
SELECT
yt.Description,
CAST(Start_Hour AS date) AS Date,
DATEPART(HOUR, Start_Hour) AS Hour,
[DurationSecs],
CAST([DurationSecs] / 60.0 AS decimal(6, 2)) AS [Duration(Min)]
FROM #yourtable yt
INNER JOIN cteTally1000 t ON t.number BETWEEN 0 AND DATEDIFF(HOUR, [Start Date], [End Date])
CROSS APPLY (
SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, [Start Date]) + t.number, 0) AS Start_Hour
) AS alias1
CROSS APPLY (
SELECT
DATEDIFF(SECOND, CASE WHEN t.number = 0 THEN [Start Date] ELSE Start_Hour END,
CASE WHEN [End Date] >= DATEADD(HOUR, 1, Start_Hour)
THEN DATEADD(HOUR, 1, Start_Hour)
ELSE [End Date] END
) AS [DurationSecs]
) AS alias2
ORDER BY Description, Date, Houranother
CREATE TABLE #yourtable
([Description] varchar(3), [Start Date] datetime, [End Date] datetime)
;
INSERT INTO #yourtable
([Description], [Start Date], [End Date])
VALUES
('ABC', '2015-08-17 10:30:30', '2015-08-17 13:00:30'),
('ABC', '2015-08-18 11:00:00', '2015-08-18 11:30:00'),
('DEF', '2015-08-17 08:25:00', '2015-08-17 10:30:00')
;with
cte_tally
as
(
select * from (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(x)
)
,cte_tally_2
as
(
select ROW_NUMBER() over(order by (select null)) id
from cte_tally a
,cte_tally b
,cte_tally c
,cte_tally d
)
,cte
as
(
select *
,dateadd(hour, DATEDIFF(HOUR, 0, a.[Start Date]) + b.x - 1 , 0 ) [newStrDt]
,dateadd(hour, DATEDIFF(HOUR, 0, a.[Start Date]) + b.x , 0 ) [newEndDt]
from #yourtable a
outer apply
(
select top ( DATEDIFF(HOUR,a.[start Date], a.[End Date] ) + 1) b.id x
from cte_tally_2 b
order by b.id
) b
)
select a.Description
,convert(varchar(10), a.[Start Date], 103) [Date]
,DATEPART(hour, a.newStrDt) [Hour]
,DATEDIFF(second, (case when a.[Start Date] > a.newStrDt then a.[Start Date] else a.newStrDt end),
(case when a.[End Date] < a.newEndDt then a.[End Date] else a.newEndDt end)) [Duration (Secs)]
,cast(DATEDIFF(second, (case when a.[Start Date] > a.newStrDt then a.[Start Date] else a.newStrDt end),
(case when a.[End Date] < a.newEndDt then a.[End Date] else a.newEndDt end)) / 60.0 as decimal(6,2)) [Duration (Min)]
from cte a
drop table #yourtablehi
trying something different
recursive cte
USE tempdb
go
DROP TABLE data
go
CREATE TABLE data
(
[description] VARCHAR(3),
[startdate] DATETIME,
[enddate] DATETIME
);
INSERT INTO data
(
[description],
[startdate],
[enddate])
VALUES ('ABC',
'2015-08-17 10:30:30',
'2015-08-17 13:00:30'),
('ABC',
'2015-08-18 11:00:00',
'2015-08-18 11:30:00'),
('DEF',
'2015-08-17 08:25:00',
'2015-08-17 10:30:00');
select * from data
go
;WITH rec__cte
AS (SELECT TOP 1 rn =1,
N = startdate,
Dateadd(hour, CASE WHEN Datepart(minute, startdate) < 30
THEN 1
ELSE 0 END +
Datepart( hour,
startdate), Cast(
CONVERT(VARCHAR(10), startdate,
112
)
AS DATETIME)
) AS ok,
enddate
FROM data
UNION ALL
SELECT rn + 1,
ok,
Dateadd(hour, 1, ok),
enddate
FROM rec__cte
WHERE enddate >= Dateadd(hour, 1, ok))
SELECT *
FROM rec__cte a
go
Recursion is much slower than a tally table. It's also a more complex technique. My suggestion is to avoid recursion unless it's required.
Thanks Scott..will keep it in mind
As you suggested Scott .. thanks
I have tried with tally table ..
Its not the complete RESULT
but my proof of concept
USE tempdb
go
DROP TABLE data
go
CREATE TABLE data
(
[description] VARCHAR(3),
[startdate] DATETIME,
[enddate] DATETIME
);
INSERT INTO data
(
[description],
[startdate],
[enddate])
VALUES ('ABC',
'2015-08-17 10:30:30',
'2015-08-17 13:00:30'),
('ABC',
'2015-08-18 11:00:00',
'2015-08-18 11:30:00'),
('DEF',
'2015-08-17 08:25:00',
'2015-08-17 10:30:00');
select * from data
go
;WITH tallycte
AS (SELECT N=0
UNION ALL
SELECT n + 1
FROM tallycte
WHERE n + 1 < 100),
cte
AS (SELECT *
FROM data)
SELECT a.startdate,
Dateadd(hour, n, a.startdate),
a.enddate,
n
FROM cte a
JOIN tallycte b
ON Dateadd(hour, n, a.startdate) <= a.enddate
ORDER BY a.startdate,
Dateadd(hour, n, a.startdate),
a.enddate
go
hi Scott
One doubt here
I was trying to see how you were doing the solution
to learn how others do
so that i can pick up other's skills
I notice the following
when i use recursive CTE as tally table
and when i use your tally table
the results differ very much
Could not understand
if you could explain
in one word or two
thanks
;WITH ctetally10
AS (SELECT *
FROM (VALUES(0),
(0),
(0),
(0),
(0),
(0),
(0),
(0),
(0),
(0)) AS numbers(number)),
ctetally1000
AS (SELECT Row_number()
OVER(
ORDER BY (SELECT NULL)) - 1 AS number
FROM ctetally10 c1
CROSS JOIN ctetally10 c2
CROSS JOIN ctetally10 c3)
SELECT yt.description,
Cast(start_hour AS DATE) AS Date,
Datepart(hour, start_hour) AS Hour,
[durationsecs],
Cast([durationsecs] / 60.0 AS DECIMAL(6, 2)) AS [Duration(Min)]
FROM yourtable yt
INNER JOIN ctetally1000 t
ON t.number BETWEEN 0 AND Datediff(hour, [start date], [end date]
)
CROSS apply (SELECT Dateadd(hour, Datediff(hour, 0, [start date]) +
t.number, 0)
AS
Start_Hour
) AS alias1
CROSS apply (SELECT Datediff(second, CASE
WHEN t.number = 0 THEN
[start date]
ELSE start_hour
END, CASE
WHEN
[end date] >= Dateadd(hour, 1,
start_hour)
THEN
Dateadd(hour, 1, start_hour)
ELSE [end date]
END) AS [DurationSecs]) AS
alias2
ORDER BY description,
date,
hour
;WITH recursivetallycte
AS (SELECT N=1
UNION ALL
SELECT n + 1
FROM recursivetallycte
WHERE n + 1 <= 1000)
SELECT yt.description,
Cast(start_hour AS DATE) AS Date,
Datepart(hour, start_hour) AS Hour,
[durationsecs],
Cast([durationsecs] / 60.0 AS DECIMAL(6, 2)) AS [Duration(Min)]
FROM yourtable yt
INNER JOIN recursivetallycte t
ON t.n BETWEEN 0 AND Datediff(hour, [start date], [end date])
CROSS apply (SELECT Dateadd(hour, Datediff(hour, 0, [start date]) + t.n,
0) AS
Start_Hour)
AS
alias1
CROSS apply (SELECT Datediff(second, CASE
WHEN t.n = 0 THEN [start date]
ELSE start_hour
END, CASE
WHEN
[end date] >= Dateadd(hour, 1,
start_hour)
THEN
Dateadd(hour, 1, start_hour)
ELSE [end date]
END) AS [DurationSecs]) AS
alias2
ORDER BY description,
date,
hour
OPTION (maxrecursion 1000)
`
