Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Autoincrement a data in table for an id based on the dates of the table

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 ex:if id-294 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:

Id code date days
294 123 12/1/24 1
294 123 13/1/25 1
294 123 15/1/25 1
294 123 16/1/25 1
294 123 17/1/25 1

Expected :

Id code date days
294 123 12/1/25 1
294 123 13/1/25 2
294 123 15/1/25 1
294 123 16/1/25 2
294 123 17/1/25 3

Since 14th not der hence days calculation starts from 1 again.so majorly days is calculted based on date for a particular I’d and code

Tags:

2

Answers


  1. 0 
    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;
    Login or Signup to reply.
  2. 0

    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.

    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

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search