when i use recursive CTE as tally table
and when i use values tally table
the results differ very much
I am doing the same thing
Only difference
Tally table .. recursive cte or values
I
Could not understand
if someone could explain
in one word or two
Why this is happening
thanks
drop create data
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
using values tally table SQL
;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
My Recursive CTE tally table SQL
;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)
