InHouseToday - IHT InHouseToday - IHT - 6 months ago 9
SQL Question

Need Custom Query in SQL Server

I have data in table

enter image description here

I want Last Month Consumption and Last Three Month Consumptions

for example basis on Reading

enter image description here

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

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 
Comments