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://learn.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql

309 questions
0
votes
2 answers

Find Next row and Previous Row based on result in sql

In this sample table fruits, I want to get previous row, current row and next row where high_low_adjstmnt < 0. Table id name low high high_low_adjstmnt volume 1 Apple 5 3 -2 1000 2 Orange 6 9 …
Shawn Chowdhury
  • 11
  • 3
0
votes
0 answers

I would like to find a max consecutive range in a Gannt chart

insert into dates(custid,bdate,ndate) values (5,'2013-12-01','2013-12-04'), (5,'2013-11-05','2013-12-01'), (5,'2013-11-15','2013-12-24'), (5,'2010-12-05','2012-12-04'), (5,'2009-12-05','2011-12-04') ; I would like to get '2013-11-05','2013-12-24'…
Zohar Mamut
  • 1
0
votes
0 answers

How to create lead and lag variables by group and conditional on year in python?

I want to create lead and lag values by group and conditional on another columns I want to create lead and lag variables for column 'Value', the 'Value_lead1' only exists when year difference is 1, otherwise it equals NaN. Also, the 'Value_lag1'…
leilei
  • 37
  • 5
0
votes
0 answers

How I delete rows base on complex function (interval time between pings on vessels with VMS)

My goal is to create a VMS point density layer of vessels, but the time interval between pings (points) is every 60min for some ships, and for others every 10min, and I want to equate them to the same time scale (60min). Once I have calculated the…
Karima
  • 1
  • 1
0
votes
1 answer

Find lag in group matching a condition

I need for each group of ID's to find the last (lag) of another column, but not just the last, but the last matching the condition. My problem is, I don't know how to add the condition to the row. My data and desired output: I've tried df %>% …
Louise Sørensen
  • 225
  • 1
  • 11
0
votes
1 answer

Percentage change calculation - mutate and lead functions giving NA values in new column

I am trying to calculate percentage change between elements in a vector of a dataframe: Year Year Count 2010 55302 2011 58943 2012 59633 2013 50194 But I the new column I create is only giving NA values instead of the percentage…
MSMagona
  • 1
  • 1
0
votes
1 answer

Combining group_by, uncount and mutate to transform dataframe with multiple repeating rows in R

I have this example dataframe in R. id <- c(1001, 1001, 1002) status <- c("dog", "cat", "mouse") col3 <- c(5, 8, 4) col4 <- c(9, 9, 6) df <- data.frame(id, status, col3, col4) The first column is ID, but 1001 is duplicated. The second column is…
Zipsa
  • 77
  • 1
  • 9
0
votes
0 answers

PostgreSql single row returned window function by date

Given a simple table of data for Postgres version 14 and higher: item_id p_date low_price A1 2022-11-01 5.00 A1 2022-11-02 5.10 A1 2022-11-04 5.20 A1 2022-11-06 5.30 A1 2022-11-08 4.99 A1 2022-11-09 5.80 A1 2022-11-10 4.85 select p_date,…
SuperDave
  • 373
  • 5
  • 14
0
votes
2 answers

get previous non-NULL value based on LAG function with dynamic offset

I have a tricky situation. I have a source dataset; it has data for four employees and their departments based on an effective date. I need to convert this source dataset to the destination dataset. Both datasets are properly sorted by EmployeeName…
user3812887
  • 439
  • 12
  • 33
0
votes
0 answers

Joining 2 tables and updating with last values for missing keys in Table 2

I have 2 tables. Table A Id version name contact 1 1 abc 123 1 2 abc 456 1 3 abc 678 1 4 abc 456 1 5 abc 678 Table B Id version Job City 1 1 XYZ CA 1 3 XYZ MB 1 5 XYZ LM I want to join both tables on ID and…
RKV
  • 1
  • 1
0
votes
0 answers

Compare column values of different rows - SQL

Currently I have a table with the following columns and data Id Revision Name Desc Status Schema CreatedBy First row 123 xyz new 1 person1 Second row 123 abc modified 1 person2 I am attempting to compare the rows of this table by…
Owen Miller
  • 23
  • 3
0
votes
1 answer

How to merge two leads using an apex Trigger

I'm new to salesforce and I'm trying to learn more. Currently I'm stuck at a point where I don't know what to do further. Kindly point me in the right direction. Any help is appreciated. So what im trying to do is to compare lastnames to find…
Sri Hari Rajan
  • 1
  • 1
0
votes
2 answers

PySpark lead based on condition

I have a dataset such as: Condition | Date 0 | 2019/01/10 1 | 2019/01/11 0 | 2019/01/15 1 | 2019/01/16 1 | 2019/01/19 0 | 2019/01/23 0 | 2019/01/25 1 | 2019/01/29 1 | 2019/01/30 I would like to get the next value of the date column when condition…
Thais Guerra Braga
  • 23
  • 2
0
votes
1 answer

SQL Building time ranges from sequential columns

I have a table with three columns. DAYOFWEEK, STARTTIME, and ENDTIME. For many of the rows (sorted by STARTTIME) the STARTTIME is equal to the previous ENDTIME. I need to create ranges where those times are concurrent. Sample…
Russell Greenleaf
  • 5
  • 2
0
votes
1 answer

Finding next date in a sequence for more than one specific condition

Suppose I have the following dataset: df1 <- data_frame(date = c("2021-01-01", "2021-01-03", "2021-01-05", "2021-01-01", "2021-01-02", "2021-01-03", "2021-01-02", "2021-01-04", "2021-01-06"), group = c("A", "A", "A", "B", "B", "B",…
Gabriel Voelcker
  • 1
  • 3
1 2 3
…
20 21