Menu
select * from TABLE1
where ENTRY_DATE >=trunc(sysdate-365)
ORDER BY ENTRY_TIME
This gives me the following result:
NUMBER_ID | ENTRY_DATE | ENTRY_TIME
----------+------------+------------
1 | 11/21/2014 | 11/21/2014 08:05:00 AM
2 | 11/21/2014 | 11/21/2014 08:08:46 AM
3 | 11/21/2014 | 11/21/2014 08:09:51 AM
4 | 11/21/2014 | 11/21/2014 08:10:05 AM
5 | 11/21/2014 | 11/21/2014 08:10:05 AM
6 | 11/21/2014 | 11/21/2014 08:10:59 AM
7 | 11/21/2014 | 11/21/2014 08:14:34 AM
However I would like to be able to display "Difference" through SQL, where column "Difference" is the difference in time between one entry and the last.
What I need
Can anyone help with adding this to my this SQL code? Thanks
688
To calculate a difference, you need a pair of records; those two records are “the current record” and “the previous year's record”. You obtain this record using the LAG() window function. This function allows you to obtain data from the previous record (based on an order criterion, which here is “ ORDER BY year ”).
Here's the SQL query to compare each row with previous row. In the above query, we join sales table with itself using an INNER JOIN condition g2.id=g1.id + 1 that allows you to compare each row with its previous row. Please note, this condition depends on the fact that our id column has consecutive numbers.
Example 1: Comparing rows of the same table. In the example, we are comparing the immediate rows to calculate the sales made on a day by comparing the amounts of two consecutive days. Syntax for inner join : SELECT column_name(s) FROM table1 t1 INNER JOIN table1 t2 on t1. column1 = t2.
Find Row Differences in ExcelClick “Find & Select” and pick “Go To Special” in the drop-down list. In the window that pops open, choose “Row Differences” and click “OK.” The window will automatically close, and you'll see the differences in your rows highlighted.
Using joins to compare columns by priority among the table. For example, left join returns all values from the first table and null value for the not-matched records from the second table. Similarly, we can use right join, inner join, full join and self join as per our requirements.
In Microsoft SQL Server, we can change the order of the columns and can add a new column by using ALTER command. ALTER TABLE is used to add, delete/drop or modify columns in the existing table. It is also used to add and drop various constraints on the existing table.
You have specified multiple RDBMS. For oracle, a straightforward query would be
SELECT e_id
, e_d - NVL(LAG ( e_d ) OVER ( ORDER BY e_d ), e_d) diff
FROM events
;
assuming a base table events created by
CREATE TABLE events ( e_id NUMBER PRIMARY KEY, e_d DATE );
The difference will be presented in the unit 'days'.
An alternative query does not use the LAG function and - while stillbeing formulated in oracle syntax - should be portable:
SELECT e.e_id
, NVL ( e.e_d - elagged.e_d, 0 ) diff
FROM events e
LEFT JOIN events elagged ON ( elagged.e_id = e.e_id - 1 )
ORDER BY e.e_id
;
This sqlfiddle contains the complete example.
98
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
