CODEWITHSUNDEEP
sqlsql-server
Krishnamurthy
Krishnamurthy

Reputation: 139

Calculating the difference of a column based on the first value of a day and the last value of a day

I have 4 columns in SQL, out of which I use only three columns. I need to create a view which subtracts the total_energy_generated_till value based on the first value of a day and the last value of a day using the column TSTAMP.

How do I achieve the same ? See the image for the table information.

Previously it had the timestamp but I converted it into a Date data type.

SQL Table

enter image description here

Sample Data

Upvotes: 0

Views: 64

Answers (2)

DineshDB
DineshDB

Reputation: 6193

Try this:

CREATE VIEW Your_View_Name
AS
SELECT TSTAMP,Inverter_ID
    ,MAX(TotalEnergyGenerated)-MIN(TotalEnergyGenerated) [total_energy_generated_till]
GROUP BY TSTAMP, Inverter_ID

Upvotes: 1

Xedni
Xedni

Reputation: 4695

If TimeStamp is unique you could do something like this:

select *
from myTable a
inner join
(
    select 
        MinTimeStamp = min(TimeStamp),
        MaxTimeStamp = max(TimeStamp)
    from myTable
) b
on a.TimeStamp = b.TimeStamp

If not, row_number might work too

Upvotes: 0

Related Questions