Custom sequence number generation in sql

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 ..

hi

i know this topic is from long ago ...

i had given a recursive cte solution ..for this earlier
However recursive cte is not good for performance at all

Ifor has also given a solution using cross apply etc

I was thinking of a simple easy WAY to do this ..
Please have a look
Any thoughts comments are Welcome !!! :slight_smile: :slight_smile:

please click arrow to the left for drop create data SCRIPT...
drop table #data 
go 

create table #data 
(
DAY	varchar(100),
MATCHDATE	date,
LEAGUE	varchar(100),	
COUNTStar int
)
go 

SET dateformat dmy

insert into #data select 'FRI','07/08/2015','E1',1
insert into #data select 'SAT','08/08/2015','E1',10
insert into #data select 'SAT','08/08/2015','E2',12
insert into #data select 'SAT','08/08/2015','E3',12
insert into #data select 'SUN','09/08/2015','E1',1 
insert into #data select 'FRI','14/08/2015','E3',1
insert into #data select 'SAT','15/08/2015','E1',10
insert into #data select 'SAT','15/08/2015','E2',11
insert into #data select 'SAT','15/08/2015','E3',11
insert into #data select 'SUN','16/08/2015','E1',2
insert into #data select 'SUN','16/08/2015','E2',1
insert into #data select 'TUE','18/08/2015','E1',7
insert into #data select 'TUE','18/08/2015','E2',10
insert into #data select 'TUE','18/08/2015','E3',12
insert into #data select 'WED','19/08/2015','E1',4
insert into #data select 'WED','19/08/2015','E2',2
insert into #data select 'FRI','21/08/2015','E1',1
go

select 'data', * from #data
go

please click arrow to the left for SQL .. easy way I think
; WITH cte 
     AS (SELECT Datepart(dd, matchdate) AS dd, 
                * 
         FROM   #data), 
     cte_dr 
     AS (SELECT Dense_rank () 
                  OVER ( 
                    ORDER BY dd ) AS dr, 
                * 
         FROM   cte), 
     cte_ok 
     AS (SELECT dd - dr AS ok, 
                * 
         FROM   cte_dr) 
SELECT Dense_rank() 
         OVER( 
           ORDER BY ok), 
       * 
FROM   cte_ok 

go