I have a table

And so on...

I have to group by id, and also return the difference for each consecutive days (Latter day - Previous day), from the maximum date for an ID, and the date column will show the previous date the subtraction was performed on like, for this table, the result will show

There will be multiple ID's and Multiple dates for each ID.

How can be this achieved without using a cursor?

CodePudding user response：

Assuming that date is a actual date and time value (as a string value in the format d MMMM is not a valid date) then you could use LAG within a Common Table Expression (CTE), and the filter your data to give the non-NULL values:

WITH CTE AS(
    SELECT V.id,
           V.Value,
           V.date,
           LAG(Value) OVER (PARTITION BY id ORDER BY date DESC) - Value AS NewValue
    FROM (VALUES(1,741.4,CONVERT(date,'20221010')),
                (1,752.7,CONVERT(date,'20221009')),
                (1,582.5,CONVERT(date,'20221011')),
                (2,58.58,CONVERT(date,'20221107')),
                (2,390.9,CONVERT(date,'20221105')))V(id,Value,date))
SELECT id,
       NewValue AS Value,
       date
FROM CTE
WHERE CTE.NewValue IS NOT NULL;

If your column date is actually a string with values in the format d MMMM then firstly, the name is a poor choice. A date contains a day, month and year, and your value is missing a year; your column date is by definition not a date. Also string based data types are a very poor choice of a data type for date and time values. For your data, '5 November' would be before '9 October', because '5' has a lower value than '9'. If you have such bad data, you need to fix that and use an actual date and time data type; you have several to choose from.