SQLTeam.com | Weblogs | Forums

Custom sequence number generation in sql


#22

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
:slight_smile:
:slight_smile:

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
db

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
using SCOTTS sql Results

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)
My Results ..