SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
 Oct  8 2022 05:58:25 
 Copyright (C) 2022 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



-- Create ClientBillingInfo table
CREATE TABLE ClientBillingInfo (
    Client VARCHAR(50),
    Rate VARCHAR(50),
    Funding VARCHAR(50),
    LastUpdated DATE
);

-- Insert sample data into ClientBillingInfo table
INSERT INTO ClientBillingInfo (Client, Rate, Funding, LastUpdated)
VALUES
    ('7F7CEE3C', '£1570', 'Private', '2024-09-09'),
    ('4A6BBC12', '£1870', 'Private', '2024-01-01'),
    ('7F7CEE3C', '£1470', 'Local Authority', '2024-09-07'),
    ('4A6BBC12', '£1570', 'Private', '2024-01-01');
4 rows affected

WITH RateChanges AS (
    SELECT 
        Client,
        Rate,
        Funding,
        LastUpdated,
        LAG(Rate) OVER (PARTITION BY Client ORDER BY LastUpdated DESC) AS PreviousRate,
        LAG(Funding) OVER (PARTITION BY Client ORDER BY LastUpdated DESC) AS PreviousFunding
    FROM ClientBillingInfo
    WHERE LastUpdated BETWEEN '2024-09-01' AND '2024-09-30'  -- Filtering only September 2024
)
SELECT 
    Client,
    Rate AS LatestRate,
    PreviousRate,
    Funding AS LatestFunding,
    PreviousFunding,
    LastUpdated
FROM RateChanges
WHERE PreviousRate IS NOT NULL  -- Ensures that there's a previous rate (indicating a change)
ORDER BY Client, LastUpdated DESC;


Client
LatestRate
PreviousRate
LatestFunding
PreviousFunding
LastUpdated


7F7CEE3C
£1470
£1570
Local Authority
Private
2024-09-07