Stack Overflow
Stack Overflow

Questions tagged [lead]

Accesses data from a subsequent row in the same result set without the use of a self-join in SQL Server 2017. LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.

For further reading https://docs.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql

228 questions
2
votes
1 answer

How to find delta for same number and name in pandas?

I have the below dataframe and I need to subtract and find the delta between next quater and current quater plus next to next quater and current quater. Input data: Number Name Year Quater value 1 an 2018 1 2.5 2 bn 2018 1 …
user3222101
  • 922
  • 13
  • 30
2
votes
2 answers

Using lead and lag from dplyr for differential values within a vector

I have a data frame structure(list(Time = structure(c(1531056854, 1531057121, 1517382101, 1517386850, 1517386951, 1517399987, 1517400523, 1517400523), class = c("POSIXct", "POSIXt")), Data = c("Start", "Exit", "Start", "Start", "Exit", "Start",…
Apricot
  • 2,447
  • 2
  • 27
  • 60
2
votes
3 answers

lag function with variable n

I am having some problems using the lag function in dplyr. This is my dataset. ID <- c(100, 100, 100, 200, 200, 300, 300) daytime <- c("2010-12-21 06:00:00", "2010-12-21 09:00:00", "2010-12-21 13:00:00 ", "2010-12-23 23:00:00", "2010-12-24…
Huicong
  • 65
  • 7
2
votes
2 answers

dplyr::filter() based on dplyr::lag() without losing first values

When I filter a dataset based on a lag() function, I lose the first row in each group (because those rows have no lag value). How can I avoid this so that I keep the first rows despite their not having any lag value? ds <- structure(list(mpg =…
Joe
  • 2,225
  • 1
  • 13
  • 29
2
votes
2 answers

Using LEAD in BigQuery

Assume my table structure is this I am planning to group it by (USER and SEQUENCE ) and get the LEAD timestamp for the next sequence. Here is the output that I am looking for Can I solve this without JOIN using LEAD function if possible ?
phaigeim
  • 669
  • 9
  • 30
2
votes
2 answers

LAG works, LEAD returning 1 within the same statement

I am puzzled over LEAD returning always ID=1, which is not even an ID in the table (ID start at ~18k), instead of a valid ID for the next record. NULL values are where they supposed to be, it's just the rows, which supposed to contain a valid ID.…
Oak_3260548
  • 1,453
  • 2
  • 14
  • 32
2
votes
1 answer

The lead list for a facebook campaign ad always returns empty

I'm creating a management that finds impressions, clicks, and leads for Facebook campaigns associated with my business account. For impressions and clicks, there were no problems but I can not find the leads. This is the code (taken from Facebook…
Alessio Mariani
  • 21
  • 2
2
votes
2 answers

SQL Server banding timepoints based on value

I have audit data captured over time points which I would like to aggregate up to display where the value was the same over a period of time using SQL Server 2014. Taking the below example data I would like to transpose it to this ideally without…
Mark Ruse
  • 327
  • 1
  • 4
  • 11
2
votes
1 answer

Correct grouping sql

I am attempting to group some results in a table, by their id and name. For the group, I want to also select the next_id, the lead value of the next record that is part of the group. When I issue a query like this: SELECT id, grade, name, …
angryip
  • 2,095
  • 5
  • 24
  • 55
2
votes
1 answer

Trying to determine what the Next Machine Operation is for specific Part Numbers

I’m working with a table that contains Part number, Order of Operation and Machine Operations. The goal is to determine what the Next Machine Operation is for each part number. I’m able to do that with this example. SELECT MyTable.PARTNO…
Larry Banks
  • 21
  • 2
2
votes
1 answer

Lead() and LAG() functionality in SQL Server 2008

Hope all the SQL GURUS out there are doing great :) I am trying to simulate LEAD() and LAG() functionality in SQL Server 2008. This is my scenario: I have a temp table which is populated using the base query with the business logic for mileage. I…
DBDeveloper
  • 31
  • 4
2
votes
1 answer

LAG/LEAD equivalent with grouping (SQL Server 2008 R2)

Note: I am using SQL Server 2008 R2 and built in LEAD/LAG functions are not available. I need to update a table's column to contain the 'previous' and 'next' values for ProductID - the table needs to store PrevProductID (LAG), ProductID and…
Maa421s
  • 149
  • 1
  • 10
2
votes
1 answer

Marketo Rest API - Update Leads using Id

How does one go about updating a lead using a lookupField of 'id'? I attempted to perform an update but received a '1003 - Field 'id' not allowed' error. Below is my request data for reference. { "action": "updateOnly", "lookupfield": "id", …
geoffo
  • 23
  • 4
2
votes
3 answers

Find clusters of time intervals

I have a table with multiple entries. One entry consists of start Datetime and end datetime. I want to find clusters of entries in such way that: If an entry starts before the prior entry ends then both are part of the cluster. It is some kind of…
tuxmania
  • 869
  • 2
  • 8
  • 24
2
votes
1 answer

Grouping subsequent dates into bins

Assuming I have the following table: date version 2015-02-01 v1 2015-02-02 v1 2015-02-03 v1 2015-02-04 v1 2015-02-02 v2 2015-02-03 v2 2015-02-08 v1 2015-02-09 v1 My query shall group dates in such way I get this…
tuxmania
  • 869
  • 2
  • 8
  • 24
1 2
3
…
15 16