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
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