I have the following table

create table summry(
  id  varchar(12),
  Desc varchar(50),
  Qty decimal(25),
  code varchar(20),
  Date int,
  Days int)

Days is calculated based on Date for that id and Code.

For example:

• If id-294 and code-123 has Date 16/1 then Days is 1
• If the same id-294 and code-123 has Date 17/1 then Days is 2 with second entry followed

How to write SQL query for this update?

Insert into summary(id, code,date,days)values(294,123,'12/1/24',1)

Data:

I have the following table

create table summry(
  id  varchar(12),
  Desc varchar(50),
  Qty decimal(25),
  code varchar(20),
  Date int,
  Days int)

Days is calculated based on Date for that id and Code.

For example:

• If id-294 and code-123 has Date 16/1 then Days is 1
• If the same id-294 and code-123 has Date 17/1 then Days is 2 with second entry followed

How to write SQL query for this update?

Insert into summary(id, code,date,days)values(294,123,'12/1/24',1)

Data:

Expected :

Since 14th not there hence days calculation starts from 1 again. So majorly days is calculated based on date for a particular Id and code.

• sap-ase
• sybase-ase15

• there are a few datatype mismatch issues between the create table command and the insert into command (eg, how do you insert 294 into a varchar(12) column? how do you insert a 12/1/25 into an int column?) so the given insert into command will fail; also, Desc is a reserved word so the given create table command will fail; please review and update the question to insure we have a valid and workable create table command – markp-fuso Commented Jan 19 at 16:24
• is this expected to be a one-time update of the table? what happens if/when new rows are inserted, or old rows are deleted, or the ordering is modified via an update of the Date column? are you going to manually run this update at some regular interval or do you need the Days column updated in real time (in which case you're likely looking at needing a trigger) – markp-fuso Commented Jan 19 at 16:33
• generally speaking ... you're denormalizing your table by adding this Days/counter column ... and having to maintain a static counter as rows are inserted/deleted/updated is going to be major headache; and let's not forget the performance and locking issues should this be a heavily active table with concurrent inserts/deletes/updates of large sets of rows with the same id/code combo – markp-fuso Commented Jan 19 at 16:35
• the title/subject mentions Autoincrement while the description mentions update; the former seems to imply you wish to set Days on-the-fly (ie, during an insert/update/delete) in which case you're looking at a much more involved operation (ie, this is not going to be addressed by a simple 'update') – markp-fuso Commented Jan 19 at 16:52
• please update the question with your coding attempt(s) to address this issue and the (wrong) results generated by your code – markp-fuso Commented Jan 19 at 16:53

3 Answers 3

First create some data

create table summary
(id     int
,code   int
,date   date
,days   int
)
go

insert summary values (294,123,'2025/01/12',1)
insert summary values (294,123,'2025/01/13',1)
insert summary values (294,123,'2025/01/15',1)
insert summary values (294,123,'2025/01/16',1)
insert summary values (294,123,'2025/01/17',1)
insert summary values (666,777,'2025/01/20',1)
insert summary values (888,999,'2024/12/30',1)
insert summary values (888,999,'2024/12/31',1)
insert summary values (888,999,'2025/01/01',1)
go

Now find the start of each date range by finding where a previous day row does not exist

select  s1.id,
        s1.code,
        range_start = s1.date
from    summary s1
where   not exists(select       1
                   from         summary s2
                   where        s2.id    = s1.id
                   and          s2.code  = s1.code
                   and          s2.date  = dateadd(dd,-1,s1.date))
order by 1,2,3
go

 id          code        range_start
 ----------- ----------- ----------------
         294         123      Jan 12 2025
         294         123      Jan 15 2025
         666         777      Jan 20 2025
         888         999      Dec 30 2024

Now for each row we find the first row of its date range and count the number of days since the start of the range to the current day

select  s1.id,
        s1.code,
        s1.date,
        days = datediff(dd, max(ranges.range_start) ,s1.date) + 1
from    summary s1
join    (select s2.id,
                s2.code,
                range_start = s2.date
         from   summary s2
         where  not exists(select       1
                           from         summary s3
                           where        s3.id    = s2.id
                           and          s3.code  = s2.code
                           and          s3.date  = dateadd(dd,-1,s2.date)) ) ranges
on      ranges.id           = s1.id
and     ranges.code         = s1.code
and     ranges.range_start <= s1.date
group by s1.id, s1.code, s1.date
order by 1,2,3
go

 id          code        date             days
 ----------- ----------- ---------------- -----------
         294         123      Jan 12 2025           1
         294         123      Jan 13 2025           2
         294         123      Jan 15 2025           1
         294         123      Jan 16 2025           2
         294         123      Jan 17 2025           3
         666         777      Jan 20 2025           1
         888         999      Dec 30 2024           1
         888         999      Dec 31 2024           2
         888         999      Jan  1 2025           3

I think below SQL code will give you the expected result. please try it:

WITH RankedData AS (
    SELECT
        id,
        code,
        date,
        ROW_NUMBER() OVER (PARTITION BY id, code ORDER BY date) AS rn
    FROM
        summary
),
DateDifferences AS (
    SELECT
        id,
        code,
        date,
        rn,
        DATEADD(DAY, -rn, CAST(date AS DATE)) AS group_identifier
    FROM
        RankedData
),
GroupedData AS (
    SELECT
        id,
        code,
        date,
        DENSE_RANK() OVER (PARTITION BY id, code ORDER BY group_identifier) AS group_number
    FROM
        DateDifferences
),
FinalDaysCalculation AS (
    SELECT
        id,
        code,
        date,
        ROW_NUMBER() OVER (PARTITION BY id, code, group_number ORDER BY date) AS days
    FROM
        GroupedData
)
UPDATE summary
SET days = f.days
FROM FinalDaysCalculation f
WHERE summary.id = f.id
  AND summary.code = f.code
  AND summary.date = f.date;

EDIT :

This was previously tagged as Mysql, however based on comment I tried including syntax for sybase, but was unable to test

It seems like a gap and island problem where island is a continuous sequence and gaps are missing value like in your case missing dates.

SELECT
id, code, date,
date_sub(date, INTERVAL row_number() OVER (PARTITION BY id, code ORDER BY date) DAY) AS island_id
FROM  test;

Island CTE identifies the continuous dates by first ranking within each id and code and then subtracting those many days.It outputs

id  code    date    continuous_date
294 123 2025-01-12  2025-01-11
294 123 2025-01-13  2025-01-11
294 123 2025-01-15  2025-01-12
294 123 2025-01-16  2025-01-12
294 123 2025-01-17  2025-01-12

Then outer query ranks within each id, code and continuous dates

select id,code,date,
  row_number() over (partition by id, code, continuous_date order by date) as days
from  islands

Final Query combining the two steps

Example Fiddle

with islands as (
select id, code, date,
date_sub(date, interval row_number() over (partition by id, code order by date) day) as continuous_date
from test)
select 
  id,code,date,
  row_number() over (partition by id, code, continuous_date order by date) as days
from  islands
order by 
  id, code, date;

Output

id  code    date    days
294 123 2025-01-12  1
294 123 2025-01-13  2
294 123 2025-01-15  1
294 123 2025-01-16  2
294 123 2025-01-17  3

Sybase equivalent converted the CTE to a subquery as mentioned in the comment and replaced DATESUB with DATEADD, but DATEADD returns timestamp as per documentation so casted it to Date.

select 
id, code,  date,
row_number() over (partition by id, code, continuous_date order by date) as days
from (

    select 
        id, code, date, 
        cast(dateadd(DAY, - (row_number() over (partition by id, code order by date)), date) as date) as continuous_date
    from test
) as islands
order by 
    id, code, date;

I have included an update statement but was unsure if you wanted to update

with islands as (
select id, code,date,
  date_sub(date, interval row_number() over (partition by id, code order by date) day) as continuous_date
from test)
update 
  test
inner join 
  (select  id, code, date, 
  row_number() over (partition by id, code, continuous_date order by date) as days
   from islands) as calculated_days
on 
  test.id = calculated_days.id and test.code = calculated_days.code and test.date = calculated_days.date
set 
  test.days = calculated_days.days;

Result

select * from test;

Output after update

id  code    date    days
294 123 2025-01-12  1
294 123 2025-01-13  2
294 123 2025-01-15  1
294 123 2025-01-16  2
294 123 2025-01-17  3

Sybase syntax

update test
set days = (
    select row_number() over (partition by id, code, continuous_date order by date)
    from 
    (
        select id,  code,  date,
        cast(dateadd(day, - (row_number() over (partition by id, code order by date)), date) as date) as continuous_date
        from test as t
    ) as islands
    where test.id = islands.id and test.code = islands.code and 
   test.date = islands.date
);