Need Custom Query in SQL Server

Question

I have data in table

I want Last Month Consumption and Last Three Month Consumptions

for example basis on Reading

I have tried this:

SELECT tbl.CustomerID
    ,tbl.CustomerName
    ,tbl2.Last3Months
    ,tbl.LastMonth
FROM (
    SELECT events.event_item_id CustomerID
        ,items.item_name CustomerName
        ,MAX(events.event_additional_info) maxR
        ,MIN(events.event_additional_info) minR
        ,(MAX(events.event_additional_info) - MIN(events.event_additional_info)) AS LastMonth
    FROM events
    INNER JOIN items ON events.event_item_id = items.item_id
    WHERE (events.event_additional_info <> '0.000000')
        AND (
            events.event_timestamp BETWEEN '08-MAY-15 12:00:00 AM'
                AND '08-Jun-15 6:21:59 PM'
            )
    GROUP BY events.event_item_id
        ,items.item_name
    ) tbl
    ,(
        SELECT events.event_item_id CustomerID
            ,items.item_name CustomerName
            ,MAX(events.event_additional_info) maxR
            ,MIN(events.event_additional_info) minR
            ,(MAX(events.event_additional_info) - MIN(events.event_additional_info)) AS Last3Months
        FROM events
        INNER JOIN items ON events.event_item_id = items.item_id
        WHERE (events.event_additional_info <> '0.000000')
            AND (
                events.event_timestamp BETWEEN '08-MAR-15 12:00:00 AM'
                    AND '08-Jun-15 6:21:59 PM'
                )
        GROUP BY events.event_item_id
            ,items.item_name
        ) tbl2
WHERE tbl2.CustomerID = tbl.CustomerID

Answer 1

You didn't provide a schema, so I worked from this:

DECLARE @MeterReading TABLE( ID INT, ReadingDate DateTime, Reading MONEY)

The SQL DateDiff function is the key. Given an input of an "As Of" date (that can default via GETDATE(), you can get the readings within the last month (DATEDIFF = 0) and within months 0, 1, and 2 (therefore 3 months).

The first query (in the WITH clause) both limits the data to 3 months and finds the max/min for each month.

The final query is then pretty simple

DECLARE @ASOF DateTime = GETDATE();  -- semicolon is important here!

WITH monthreadinds as (SELECT id
                        , DATEDIFF(MONTH,mr.ReadingDate , @ASOF) as WhichMonthAgo
                        , MIN(reading) as MinReading
                        , max(reading) as MaxReading
                    FROM @MeterReading mr
                    WHERE DATEDIFF(MONTH,mr.ReadingDate , @ASOF)  <= 2  -- very important (limits to 3 months)
                    group by ID, DATEDIFF(MONTH,mr.ReadingDate , @ASOF) 
                    )
SELECT id,
    SUM( CASE WHEN mr.WhichMonthAgo = 0 THEN mr.MaxReading -  mr.MinReading  ELSE 0 END ) as LastMonthConsumption,
    MAX(mr.MaxReading) -  MIN(mr.MinReading)   as Last2MonthsConsumption
FROM  monthreadinds mr
GROUP by id