SQLTeam.com | Weblogs | Forums

Custom sequence number generation in sql


#1

Hi - I have a database containing football results I want to do various things with. One is to create a sequence number for each set of football games in the calendar. A sequence will contain all matches played on 1. Friday, Saturday, Sunday or 2. Tuesday or Wednesday. In matchdate order the sequence should increment throughout the season. Other days can be ignored for the purpose of what I'm trying to achieve. The sql I'm running is:
select TO_CHAR(matchdate, 'DY') as DAY,matchdate,league,count(*)
FROM results
where league in ('E1','E2','E3','E4')
and matchdate between TO_DATE('01/07/2015', 'DD/MM/YYYY')
AND TO_DATE('15/06/2016', 'DD/MM/YYYY')
group by matchdate,league
order by matchdate
Which produces output similar to

DAY MATCHDATE LEAGUE COUNT(*)
FRI 07/08/2015 E1 1
SAT 08/08/2015 E1 10
SAT 08/08/2015 E2 12
SAT 08/08/2015 E3 12
SUN 09/08/2015 E1 1
FRI 14/08/2015 E3 1
SAT 15/08/2015 E1 10
SAT 15/08/2015 E2 11
SAT 15/08/2015 E3 11
SUN 16/08/2015 E1 2
SUN 16/08/2015 E2 1
TUE 18/08/2015 E1 7
TUE 18/08/2015 E2 10
TUE 18/08/2015 E3 12
WED 19/08/2015 E1 4
WED 19/08/2015 E2 2
FRI 21/08/2015 E1 1

I would like it do contain the sequence number as follows:

Sequence DAY MATCHDATE LEAGUE COUNT(*)
1 FRI 07/08/2015 E1 1
1 SAT 08/08/2015 E1 10
1 SAT 08/08/2015 E2 12
1 SAT 08/08/2015 E3 12
1 SUN 09/08/2015 E1 1
2 FRI 14/08/2015 E3 1
2 SAT 15/08/2015 E1 10
2 SAT 15/08/2015 E2 11
2 SAT 15/08/2015 E3 11
2 SUN 16/08/2015 E1 2
2 SUN 16/08/2015 E2 1
3 TUE 18/08/2015 E1 7
3 TUE 18/08/2015 E2 10
3 TUE 18/08/2015 E3 12
3 WED 19/08/2015 E1 4
3 WED 19/08/2015 E2 2
4 FRI 21/08/2015 E1 1
4 SAT 22/08/2015 E1 11
4 SAT 22/08/2015 E2 12
4 SAT 22/08/2015 E3 12
5 FRI 28/08/2015 E1 1

Hope that makes sense, any help appreciated or happy to provide more information if any questions
Thanks


#2

Your code looks like Oracle. Keep in mind that this is a SQL Server forum. You may find someone that also knows Oracle, you may not.


#3

Ah apologies! Happy to take an answer in sql server and transform. I'd hope the underlying solution would be the same or transferrable


#4

Hi

I think it's possible

I am working on it

Could be 1 day before i have something
Don't have access to desktop


#5

Hi

I don't have access to my desktop

I used my mind and came up with this

initial thoughts SQL
SELECT Dense_rank() 
             OVER ( 
               partition BY a.date+1 = Min(b.date)
               ORDER BY a.date), 
           A.*, 
           b.* 
    FROM   sametable a 
           JOIN sametable b 
             ON b.date >= a.date 
    Group by a.date
    ORDER  BY a.date

#6

Thanks for the reply. Odd but I'm getting missing right parenthesis with highlight around the = sign on the line of partition by....
I've replaced date with matchdate and sametable with results which I'd expect to work. Any suggestions appreciated


#7

hi i am working on it

those were just initial thoughts

looks like the partition syntax is not correct


#8

Hi if you have sql server 2012 you could see the below article

i dont have 2012


#9

hi

coming up with SQL seems hard

We can use CURSOR and get the DESIRED RESULT ...
shall i try to use cursor ?? and come up with solution


#11

Hi

I have used a cursor solution ..

Its also possible with LEAD LAG functions in 2012
I dont have 2012

Create Data
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
Cursor
DROP TABLE #output 

go 

CREATE TABLE #output 
  ( 
     sequence  INT, 
     day       VARCHAR(100), 
     matchdate DATE, 
     league    VARCHAR(100), 
     countstar INT 
  ) 

DECLARE @day       VARCHAR(100), 
        @matchdate DATE, 
        @league    VARCHAR(100), 
        @countStar INT, 
        @sequence  INT, 
        @prevDate  DATE, 
        @nxtDate   DATE; 
DECLARE curs CURSOR FOR 
  SELECT a.day, 
         a.matchdate, 
         a.league, 
         a.countstar 
  FROM   #data a 
  ORDER  BY a.matchdate 

OPEN curs 

FETCH next FROM curs INTO @day, @matchdate, @league, @countStar 

SET @sequence = 1 
SET @prevDate = @matchdate 
SET @nxtDate = @matchdate 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      SET @prevDate = @matchdate 

      FETCH next FROM curs INTO @day, @matchdate, @league, @countStar 

      SET @nxtDate = @matchdate 

      IF Datediff(d, @prevDate, @nxtDate) > 1 
        SET @sequence = @sequence + 1 

      INSERT INTO #output 
      SELECT @sequence, 
             @day, 
             @matchdate, 
             @league, 
             @countStar 
  END 

CLOSE curs; 

DEALLOCATE curs; 

SELECT * 
FROM   #output
Result


#12

Hi

I have provided a cursor solution EARLIER

Now I am trying with SQL
If somebody can take this SQL way FURTHER .. nice :slight_smile:
I will keep trying

Drop Create Data
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
SQL so far
SELECT a.day, 
       a.matchdate, 
       a.league, 
       a.countstar, 
       Min(b.matchdate) AS NxtDate, 
       CASE 
         WHEN Dateadd(d, 1, a.matchdate) = Min(b.matchdate) THEN 1 
         ELSE 0 
       END              AS Counter 
FROM   #data a 
       LEFT JOIN #data b 
              ON b.matchdate > a.matchdate 
GROUP  BY a.day, 
          a.matchdate, 
          a.league, 
          a.countstar 
ORDER  BY a.matchdate
Result


#13

hi trying with SQL

i got here

result is 99% accurate except for last row

drop create data
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
SQL
SELECT a.*, 
       Dense_rank() 
         OVER( 
           ORDER BY weekending) AS sequence 
FROM   (SELECT * 
        FROM   #data 
               CROSS apply (SELECT Dateadd(week, Datediff(day, 5, 
                                                 Dateadd(day, 5, 
                                                 #data.matchdate)) / 7, 
                                   5) 
                                                             AS 
                                   Weekending) AS calc1) a
Result


#14
  1. Use iso_week as I do not think it relies on DATEFIRST.
  2. ISO week has 1 for Monday so offset by 4 for Friday.
  3. Define Fri, Sat, Sun as first part of the week and the rest as the second part
  4. Will need the ISO year for boundary cases.

Try:

SELECT DENSE_RANK() OVER (ORDER BY X2.ISOYear, X2.ISOWeek, X2.WeekPart)
	,D.*
FROM #data D
	CROSS APPLY(VALUES(DATEADD(day, 4, D.MATCHDATE))) X1(MatchDate)
	CROSS APPLY
	(
		VALUES
		(
			YEAR(DATEADD(day, 26 - DATEPART(iso_week, X1.MatchDate), X1.MatchDate))
			,DATEPART(iso_week, X1.MatchDate)
			,CASE WHEN D.[DAY] IN ('FRI','SAT','SUN') THEN 1 ELSE 2 END
		)

	) X2 (ISOYear, ISOWeek, WeekPart)
ORDER BY D.MatchDate;

#15

Thanks all, very helpful. I got it to work by restricting the league in the output and using a cursor. First time I've used this forum and am well impressed ! Code is below if anyone interested:
DECLARE
my_matchdate results.matchdate%TYPE;
my_league results.league%TYPE;
my_games int default 0;
my_day varchar(10);
weeknum int default 0;
rownumber int default 0;
prevmatchdate date;
CURSOR c1 IS
select TO_CHAR(matchdate, 'DY') as DAY,matchdate,count(*) as games
FROM results
where league in ('E1','E2','E3','E4')
and matchdate between TO_DATE('01/08/2015', 'DD/MM/YYYY')
AND TO_DATE('15/10/2015', 'DD/MM/YYYY')
group by matchdate
order by matchdate;
BEGIN
weeknum:=0;
OPEN c1;
LOOP
FETCH c1 INTO my_day,my_matchdate,my_games;
EXIT WHEN c1%NOTFOUND;
if TO_CHAR(my_matchdate, 'DY') in ('TUE','FRI') then
weeknum:=weeknum+1;
elsif TO_CHAR(my_matchdate, 'DY') in ('SAT') then
if (TO_CHAR(prevmatchdate, 'DY') not in ('FRI') ) then
weeknum:=weeknum+1;
end if;
end if;
prevmatchdate:=my_matchdate;
dbms_output.put_line(' day ' || my_day || ' date ' || my_matchdate || ' seq ' || weeknum );
END LOOP;
END;
Output:
day FRI date 07-AUG-15 seq 1
day SAT date 08-AUG-15 seq 1
day SUN date 09-AUG-15 seq 1
day FRI date 14-AUG-15 seq 2
day SAT date 15-AUG-15 seq 2
day SUN date 16-AUG-15 seq 2
day TUE date 18-AUG-15 seq 3
day WED date 19-AUG-15 seq 3
day FRI date 21-AUG-15 seq 4
day SAT date 22-AUG-15 seq 4
day FRI date 28-AUG-15 seq 5
day SAT date 29-AUG-15 seq 5
day MON date 31-AUG-15 seq 5
day SAT date 05-SEP-15 seq 6
day SUN date 06-SEP-15 seq 6
day FRI date 11-SEP-15 seq 7
day SAT date 12-SEP-15 seq 7
day SUN date 13-SEP-15 seq 7
day TUE date 15-SEP-15 seq 8
day WED date 16-SEP-15 seq 8
day FRI date 18-SEP-15 seq 9
day SAT date 19-SEP-15 seq 9
day SUN date 20-SEP-15 seq 9
day MON date 21-SEP-15 seq 9
day TUE date 22-SEP-15 seq 10
day THU date 24-SEP-15 seq 10
day FRI date 25-SEP-15 seq 11
day SAT date 26-SEP-15 seq 11
day SUN date 27-SEP-15 seq 11
day TUE date 29-SEP-15 seq 12
day FRI date 02-OCT-15 seq 13
day SAT date 03-OCT-15 seq 13
day SUN date 04-OCT-15 seq 13
day SAT date 10-OCT-15 seq 14
day SUN date 11-OCT-15 seq 14


#16

Cursors are fine in Oracle but not so good in SQL Server.
I think the following should work in Oracle with the data in the CTE.

WITH Data
AS
(
  SELECT *
  FROM
  (
    SELECT 'FRI' AS DAY,TO_DATE('07/08/2015', 'dd/mm/yyyy') AS MATCHDATE,'E1' AS LEAGUE,1 AS COUNTStar FROM DUAL
    UNION ALL SELECT 'SAT',TO_DATE('08/08/2015', 'dd/mm/yyyy'),'E1',10 FROM DUAL
    UNION ALL SELECT 'SAT',TO_DATE('08/08/2015', 'dd/mm/yyyy'),'E2',12 FROM DUAL
    UNION ALL SELECT 'SAT',TO_DATE('08/08/2015', 'dd/mm/yyyy'),'E3',12 FROM DUAL
    UNION ALL SELECT 'SUN',TO_DATE('09/08/2015', 'dd/mm/yyyy'),'E1',1 FROM DUAL
    UNION ALL SELECT 'FRI',TO_DATE('14/08/2015', 'dd/mm/yyyy'),'E3',1 FROM DUAL
    UNION ALL SELECT 'SAT',TO_DATE('15/08/2015', 'dd/mm/yyyy'),'E1',10 FROM DUAL
    UNION ALL SELECT 'SAT',TO_DATE('15/08/2015', 'dd/mm/yyyy'),'E2',11 FROM DUAL
    UNION ALL SELECT 'SAT',TO_DATE('15/08/2015', 'dd/mm/yyyy'),'E3',11 FROM DUAL
    UNION ALL SELECT 'SUN',TO_DATE('16/08/2015', 'dd/mm/yyyy'),'E1',2 FROM DUAL
    UNION ALL SELECT 'SUN',TO_DATE('16/08/2015', 'dd/mm/yyyy'),'E2',1 FROM DUAL
    UNION ALL SELECT 'TUE',TO_DATE('18/08/2015', 'dd/mm/yyyy'),'E1',7 FROM DUAL
    UNION ALL SELECT 'TUE',TO_DATE('18/08/2015', 'dd/mm/yyyy'),'E2',10 FROM DUAL
    UNION ALL SELECT 'TUE',TO_DATE('18/08/2015', 'dd/mm/yyyy'),'E3',12 FROM DUAL
    UNION ALL SELECT 'WED',TO_DATE('19/08/2015', 'dd/mm/yyyy'),'E1',4 FROM DUAL
    UNION ALL SELECT 'WED',TO_DATE('19/08/2015', 'dd/mm/yyyy'),'E2',2 FROM DUAL
    UNION ALL SELECT 'FRI',TO_DATE('21/08/2015', 'dd/mm/yyyy'),'E1',1 FROM DUAL
  ) V 
)
SELECT D.DAY
  ,D.MATCHDATE
  ,D.LEAGUE
  ,D.COUNTStar
  ,DENSE_RANK() OVER
    (ORDER BY
      TO_CHAR(D.MATCHDATE + 4,'IYYY')
      ,TO_NUMBER(TO_CHAR(D.MATCHDATE + 4,'IW'))
      ,CASE WHEN D.DAY IN ('FRI','SAT','SUN') THEN 1 ELSE 2 END
    ) AS Seq
FROM DATA D
ORDER BY MATCHDATE;

#17

Hi Ifor

Like your solution


#18

Hi Ifor

Alternative solution using Recursive CTE

bitsmed(senior member here .. solution provider ) is not fond of Recursive CTE
as it has performance issues
please note

Please let me know your thoughts
:slight_smile:
:slight_smile:

i have added a column row number to create data

drop create data with row number column added and populated
use tempdb 
go 

drop table data 
go 

create table data 
(
rn int identity(1,1) not null,
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 * from data 
go
SQL using Recursive CTE
; WITH reccte 
     AS (SELECT *, 
                1 AS [grp] 
         FROM   data 
         WHERE  rn IN ( 1 ) 
         UNION ALL 
         SELECT a.*, 
                CASE 
                  WHEN A.rn <> 2 
                       AND ( A.matchdate > Dateadd(d, 1, B.matchdate) ) THEN 
                  b.grp + 1 
                  ELSE b.grp 
                END AS GRP 
         FROM   data a 
                JOIN reccte b 
                  ON a.rn = b.rn + 1) 
SELECT * 
FROM   reccte 

go
Result


#19

Recursion is occasionally useful but I suspect it could have performance problems here.
If you really want to look at this in depth then you need to start looking at the execution plans preferably with realistic amounts of test data. There are plenty of article on this or you can download a free book PDF here:

http://www.sqlservercentral.com/articles/books/94937/


#20

thanks ifor

i have experience reading execution plans
i am working as a SQL server developer now
used to work as a SQL Server Database Administrator..

logical reads
network round trips
etc etc etc


#21

--DROP TABLE league
create table league
(
DAYS CHAR(5),
MATCHDATE varchar(50),
LEAGUE VARCHAR(2),
)

INSERT INTO league VALUES('FRI','07/08/2015','E1')
INSERT INTO league VALUES('SAT','08/08/2015','E1')
INSERT INTO league VALUES('SAT','08/08/2015','E2')
INSERT INTO league VALUES('SAT','08/08/2015','E3')
INSERT INTO league VALUES('SUN','09/08/2015','E1')
INSERT INTO league VALUES('FRI','14/08/2015','E3')

INSERT INTO league VALUES('SAT','15/08/2015','E1')

INSERT INTO league VALUES('SAT','15/08/2015','E2')
INSERT INTO league VALUES('SAT','15/08/2015','E3')
INSERT INTO league VALUES('SUN','16/08/2015','E1')
INSERT INTO league VALUES('SUN','16/08/2015','E2')
INSERT INTO league VALUES('TUE','18/08/2015','E1')
INSERT INTO league VALUES('TUE','18/08/2015','E2')
INSERT INTO league VALUES('TUE','18/08/2015','E3')
INSERT INTO league VALUES('WED','19/08/2015','E1')
INSERT INTO league VALUES('FRI','21/08/2015','E2')

select DAYS,MATCHDATE,LEAGUE,dense_rank() over(order by grpp desc) FROM
(
SELECT DAYS,MATCHDATE ,LEAGUE,substring(MATCHDATE,1,2) as MATCHDATE1,
( DENSE_RANK() OVER (ORDER BY substring(MATCHDATE,1,2)) - substring(MATCHDATE,1,2)) as grpp
FROM league
) T
order by MATCHDATE1 ASC