This is my input table

but i want to get this table

Explaination: I want to subtract value of segmeted 14/10/22 - 7/10/22 that means (28930-28799)

how could i get this kindly help me to figure it out. I cant format it properly.

This is my table

and i want to subtract value column subtraction by SEGMENTED_DATE wise like (14th october value - 7th october value) that means (28930-28799)

the segment table is created by bellow query

select segment ,count(distinct user_id)as value,SEGMENTED_DATE  from weekly_customer_RFM_TABLE
     where segment in('About to sleep','Promising','champion','Loyal_customer',
                'Potential_Loyalist','At_Risk','Need_Attention','New_customer',
                'Hibernating','Cant_loose')
     and  SEGMENTED_DATE between '2022-10-07' and '2022-10-28'
Group by segment,SEGMENTED_DATE

I want this table as output

This is only value difference only Segment_date wise

CodePudding user response：

You may perform a self join as the following:

SET @rn=1;

SELECT CONCAT('Week',@rn:=@rn 1, ' - Week',@rn-1) AS Change_TIME,
       T.segment,
       D.value-T.value AS Value
FROM table_name T JOIN table_name D
ON D.segmented=T.segmented   INTERVAL 7 DAY

See a

This query is suitable for MySQL engine and will not run on SQL server.

Edit1:

Here is some explanation:
In inner query I used variables for two reasons:

I need a counter (@i) so I can know week index like (week1, week2, ...). This counter will increase with each record by (@i:=@i 1).
I need to know value of previous record so I used (@Prev :=Value) to save that value then I can subtract it from Value in current record (Value- @Prev) AS Prev.

I started with initial values (SET @Prev = 0;) Assuming no previous values and (SET @i = 0;) because @i will increased to (1) at first record.

In outer query I converted (@i named C) to (week(i)-week(i-1)) week1-week0, week2-week1, ....... and removed first record because it will display wrong data.
I can help improving the query if you show me some real data.

Edit2:

According to you last modification at 2022/10/07 the query will be :

SET @Prev = 0;
SET @S = 0;
SELECT Segment, Diffirence, SEGMENTED_DATE FROM (
  SELECT 
    `Value`- @Prev AS Diffirence,  
    POSITION(@S IN Segment) AS NotFirst, 
    @Prev := IF(@S=Segment, `Value`, 0) AS `Value`, 
    @S := Segment AS Segment,
    SEGMENTED_DATE 
  FROM test
) AS t WHERE NotFirst> 0;