hi ifor
please see my reply to scott regarding recursive cte issue i faced
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
Hi there,
I have this dataset:
what the requirement was
```
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 ?
need output like this
drop create data
DROP TABLE yourtable
go
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')
Scotts 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)