331 questions
-1
votes
1
answer
106
views
Grouping data using the LEAD() function
I've got this query:
SELECT TOP (1000) [TRANS_ID]
,[VSN_VENDR_EFF_DT]
,LEAD([VSN_VENDR_EFF_DT], 1, '9999-12-31') OVER (ORDER BY [ACCT_NUM],[VSN_VENDR_EFF_DT]) AS NEXT_EFF_DT
,[...
- 8,502
0
votes
2
answers
71
views
SAS: Conditionally summing a column without having the lead funtion
I cannot conditionally sum a column where the condition looks at rows further down the table. Below is what I am expecting:
Identifier
MONTH
Sum_Me_Pls
On_Off_Flag
focus
seq
Need
1
May-23
0
0
0
1
1
...
- 3
0
votes
3
answers
95
views
SQL window functions
I've been playing around with dense_rank, LAG, LEAD, ... all kinds of sorting but don't seem able to get this one solved.
Here's a sample of my data and the result I expect/need:
pk_id
pk_id_row_num
...
- 13
0
votes
0
answers
26
views
The "Slider" option is not visible while creating Lead Form ads on Facebook
The "Slider" option is not visible in the question type section while creating an Instant Form on Facebook.
enter image description here
Based on this article the "Slider" Option ...
2
votes
2
answers
98
views
Generate list of lead values
I have the below piece of code in R/Dplyr, which finds the lead value at t+5 for column VALUE. If that is not available, the latest value between t+1 and t+5 is used:
data <- data %>%
group_by(...
- 133
0
votes
0
answers
70
views
How to pick change values from a column other than using window functions in Snowflake
I have a Snowflake table with data like below:
Table1
Col1 Col2 Col3
G1 1 9:15
G1 1 9:16
G1 2 9:17
G1 1 9:18
G2 1 9:15
G2 2 9:16
I want to ...
- 564
0
votes
1
answer
32
views
Acumatica Find Duplicate Leads
I would like to identify if a lead being created is yet on the database, base on its email. The following sql query is working in SSMS but not in bql :
'''SELECT [CRLead].[ContactID] FROM [dbo].[...
- 31
-1
votes
1
answer
74
views
Trouble with sort order using Lead with window function
I'm trying to put a flag on claims that has an Approved followed by a Terminated on the same date. So in the example below ClaimID = 123 has both an Approved and a Terminated on 12/12/2024 and this ...
- 641
0
votes
0
answers
74
views
Integrating Active Prospect Trusted Form with Meta Lead ads
I want to integrate active prospect's trusted form with meta lead ads to generate a certificate for every lead I get. Now the certificate generation on trusted form is free and the cost incur on ...
- 21
0
votes
0
answers
57
views
How do I update the lead conversion screen in Salesforce so required information can be completed?
In Salesforce, I have set required fields on the Opportunity record and Account record:
Account: Billing Address, Type, Website, Number of Employees, Industry
Opportunity: TCV, Term, Type, Lead Source,...
- 1
1
vote
0
answers
60
views
Facebook Leadgen Webhook Returning Mock Data Instead of Real Leads
I'm developing an application to capture leads from a Facebook lead form. Here's what I've done so far:
I created an App in the Facebook Developer Dashboard.
I verified the webhook for this app, and ...
0
votes
0
answers
36
views
Fill null values in BigQuery table with next non-null value from specific column [duplicate]
I have a BigQuery table with the following structure:
[
{
"case_id": "1234567890",
"reply_date": "2024-06-25 13:31:07.000000 UTC",
"next_reply_target"...
- 309
-3
votes
1
answer
186
views
MySQL Lead function returns NULL
I have this MySQL table:
id
Name
1
Spanish
2
French
3
German
I am using this statement:
SELECT id, Name, Lead(Name) OVER (ORDER BY id) AS 'next' from MyTable WHERE Name='French'
The expected result ...
- 1,484
0
votes
2
answers
65
views
Postgresql select nearest max value row within 30 seconds timestamp
I need to extract data from a postgres table, where I want to look at nearby records within a 30 seconds of timesstamp with two columns match and fetch the value which is having max value.
id | ...
- 347
2
votes
2
answers
56
views
Pandas window-like function to rid off time-stamp overlapping
I have a dataframe that contains promo campaign duration and I need to rid off time-stamp overlapping.
import pandas as pd
import numpy as np
from pandas import Timestamp
t1 = np.array([['Store A', ...
1
vote
1
answer
53
views
obtain maximum date from multiple leading dates
Sample data:
data <- data.frame(
year = c(2018, 2018, 2018, 2018, 2018, 2018, 2018, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020),
patient_id = ...
- 39
0
votes
0
answers
123
views
Using lead() in Postgres with multiple columns
I have the following query:
select
calendar,
column1,
column2,
.
.
.
columnN,
count(some_value)/30 as some_value
from some_table
group by 1,2,...,N
Assuming my calendar is a date_trunc on 'month' ...
1
vote
1
answer
61
views
Extract rows with consecutive zero values if they are preceded by significant values in R
I'm working with a dataset in R called "data," originating from data collection on a Fronius inverter. This dataset contains one record per minute and a column named "pac_w," which ...
- 43
0
votes
2
answers
142
views
Teradata query to Calculate Start and End dates Based on a condition
I have a dataset for certain STS codes and I need to retain the Start and End Date When Status =03
I have the following Data
HAVE
ID CHGDATE STS
101 11/21/2022 ...
- 593
0
votes
2
answers
91
views
Assign Previous Record Information to New Rows Based on Number Gap
I have a basic temp table called #rowCreate with a gap in the count column. It copies an existing table called dbo.rowSource.
parentID
name
count
1
A
1
1
B
2
1
C
5
1
D
6
1
D
8
2
A
3
2
B
4
My goal is ...
- 365
1
vote
2
answers
84
views
SQL: Find AVG value of X samples before & after a found event
How do I find the average value of X samples before and after a found event from a sorted table?
In the example below, I have sales_list table. I want to sort the table by date and find the events ...
- 139
0
votes
0
answers
133
views
Submission of a form will be added as a lead for Bitrix24
I have a form that collects names, emails, and positions. While my PHP code successfully sends the names and emails to Bitrix as new leads, the position data is not being transmitted.
<?php
...
2
votes
1
answer
740
views
Is this a bug with LEAD() IGNORE NULLS?
I was testing and comparing various forms of the LAST(), LEAD(), FIRST_VALUE() and LAST_VALUE() window functions and appear to have found a bug with LEAD() IGNORE NULLS.
Instead of the expected ...
- 10.6k
0
votes
1
answer
42
views
Merge overlapping time frames (min and max) into one and blanket assign to relevant user if detection time criteria is matched
I have a database that records user access. User access is requested and users are given a two hour window to access their space. At the moment, users can request multiple requests, which sometimes ...
- 1
1
vote
2
answers
87
views
Combine chain of dates where dates are less than few seconds apart
I have a list of timestamps for data acquisition. timestamps close together belong to a cycle. I want to enumerate these cycles. So whenever between two timestamps is more then 100 seconds then it ...
- 23
2
votes
3
answers
86
views
Lead and lag multiple values together in same new column
Here is my data:
structure(list(date = structure(c(19662, 19663, 19664, 19665,
19666), class = "Date"), tmax = c(12, 13, 12, 11, 15)), class = "data.frame", row.names = c(NA,
-5L)...
- 357
0
votes
4
answers
77
views
Selecting records where autoincrement ID is bigger, but date column is in the past
I have a table with two columns. First one is an autoincrement ID column, second one is a datetime column where next data should be in future to the previous. I need to select values where it is not ...
-1
votes
1
answer
387
views
How to use Lead in Update SQL command
This works with SELECT:
(LEAD(AltCode,2) OVER (ORDER BY AltCode) ) from [clroot].[Material]
but not with Update
USE [MEKON-EPE]
UPDATE [clroot].Material
SET String1 = (LEAD(AltCode, 2) OVER (ORDER ...
-1
votes
1
answer
440
views
Formula field is not working in salesforce
I am creating a formula field on Lead object.
If country of lead is one of the field from picklist value of high risk countries picklist field then show some image and text which i have stored in ...
- 17
0
votes
1
answer
40
views
get max of value - lead and keep corresponding rows that generated the max using dplyr
How would one keep the corresponding two rows (all columns preserved) that produced the max difference after grouping, arranging, and subtracting the 4th lead from the current value? I can currently ...
- 105
0
votes
1
answer
736
views
Using LEAD and LAG on ragged time series
I have a large dataset and want to retrieve previous and subsequent observations for small windows. Below is a snippet around a window of interest (2024-09-23 to 2024-09-27).
item date price
A ...
- 169
0
votes
1
answer
105
views
LEAD function in SQL query not getting desired result
I'm trying to get an entry from a table that has START_TIME and FINISH_TIME.
If FINISH_TIME is null then have to get the next START_TIME.
Below is the query I tried, also have to use the lead function ...
- 25
0
votes
2
answers
61
views
How to get the latest change from a table per ID
I have a table like
ID
TYPE
START
END
TIMESTAMP_
1
A
NULL
5
'2022-03-03'
1
B
NULL
7
'2023-07-19'
1
A
5
NULL
'2023-07-20'
1
B
NULL
7
'2023-07-21'
4
A
NULL
20
'2023-06-30'
4
A
20
17
'2023-07-01'
I am ...
- 25
1
vote
1
answer
191
views
Lead Function to Perform in DataStage
I am looking to do LEAD() and LAG() functions to perform in DataStage.
Input
C1 C2
1 100
2 200
3 300
4 400
Output - 1
C1 C2 C3
1 100 200
2 200 300
3 300 400
4 400 NULL
Output - 2
C1 C2 C3
1 100 ...
- 11
0
votes
2
answers
135
views
Find time difference between non-consecutive rows depending on column values in Athena
I have a dataset with conversation data between a User and a Customer Service. I need to find the response time (time difference) between after a user sent a message and first time the Customer ...
- 169
0
votes
1
answer
164
views
Desire correct row numbers using over PARTITION BY for invoices transaction
I have a set of data containing invoice information. The goal is to find the credit and rebill invoice number ('InvcNbrKey') associated with a particular 'Standard Order' line. Think of it as a person ...
- 55
-2
votes
1
answer
83
views
SQLite Studio query works different when I make it a subquery
I'm using the Superstore_test dataset here: https://www.kaggle.com/blurredmachine/superstore-time-series-dataset
I want to make a column that shows the sales of the next row down. I put this query in ...
- 9
0
votes
1
answer
107
views
Assign Unique Group ID based on the time an event happened (within 60 minutes of each other)
Is there a way to assign unique Group ID to rows in SQL based off on how much time is between each one (in this case 60 minutes). For patient 614 I would like to start the first row at 12:50pm and ...
-2
votes
2
answers
43
views
how to count number of records from 3rd day to 62nd day for each person by using SQL query?
I want to get the count of 'unitsold' for each Name from 3/1/2022 and 4/1/2022. For example Name 'a' required to count its units sold from 3rd to 8th day.
SQL query:
select Name, sum(unitsold) over (...
- 335
0
votes
3
answers
137
views
SQL query to pick the next change Date from the next row based on same Brand
Requirement is to pick the next change date from the following table as
RetailDate
Brand
IdBusinessUnit
2023-02-05
B Series
2
2023-02-07
B Series
2
2023-02-07
Equity
2
2023-02-15
Equity
2
2023-02-21
...
0
votes
0
answers
25
views
Get the Time_stamp When value of tag changed from 1 to 2 and toggled [duplicate]
I am having a Table contain two columns named Time_stamp and PWPH_Pmp_2_FBSts and table name is per_hour_log The Sample of A Screen Shot I embedded
for the reference.The Query Which I Wrote is as ...
0
votes
2
answers
3k
views
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 ...
-1
votes
1
answer
143
views
Snowflake partitioning
I have records like below:
login_id
login_type
login_name
login_timestamp
page_id
1
event
null
2023-01-13 00:22:02.560
1
1
event
null
2023-01-13 00:22:02.634
1
1
page
login
2023-01-13 00:22:02.882
1
1
...
- 151
1
vote
1
answer
86
views
postgresql - find Discontinuous id and get read_time
I have a table like this, and there are three cases,
## case a
| rec_no | read_time | id
+--------+---------------------+----
| 45139 | 2023-02-07 17:00:00 | a
| 45140 | 2023-02-07 17:15:...
- 13
0
votes
1
answer
39
views
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 ...
- 257
0
votes
1
answer
232
views
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 ...
- 1
1
vote
2
answers
2k
views
find the difference between current and previous value - postgresql
I'm trying to write a query that will show me the difference between the current value and the previous one from the query result. It seems to me that this can be done with the "OFFSET" or &...
- 25
0
votes
1
answer
228
views
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, ...
- 77
0
votes
0
answers
37
views
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-...
- 413
-1
votes
3
answers
2k
views
How to use lead() efficiently and return next different value?
I have a table that records user transactions like this (simplified version) in BigQuery
user
transaction_date
label
cost
a
2021-10-31 10:30:00
y1
10
b
2021-10-31 10:30:00
y2
10
c
2021-10-31 10:30:00
...
- 93