I have a query that I'm trying to figure out and I've got my knickers in a real twist.
If you examine the attached spreadsheet, it has test data and the desired results.
I have an original report that was created programatically and I'm trying to recreate it using SQL. The report joins two existing views together using the PK UNIT ID. It RANKS and PARTITIONS the data and then attempts to peform some calculations on the resulting rowset using the LAG function.
There are 4 tabs in the spreadsheet:
- This is the main test data
- This is a static set of cars that is joined to Traction View
SQL And Original Report
- This shows my SQL attempt and sample output from the old report
- This shows how I would like the resulting output to loook
I have put notes in each tab to explain what I am trying to do
My SQL attempt is yielding rubbish and I'm hoping someone can help me figure out what to do so I can learn from this.
Each UNIT checks-in multiple times per day and uploads the distance travelled since the last check-in
I need to find the final entry for each day for each UNIT and display them in order of UNIT then DATE
I then want to use the LAG function to manipulate the current rows values based on data in the previous row.
Thank you for any help you can provide.