is there a sql query I want to get last job_id with the earliest start date. I have a table named employee expected results Answer First, use FIRST_VALUE() window function to get the latest job_id for each employee_id and then use conditional aggregation to get the earliest start_date for this job_id: See the…
Required SQL Query
I have been stuck on this for sometime now. I have the following SQL Tables: department table Id Name 1 DeptA 2 DeptB 3 DeptC users table Id Name Dept 101 Alice 2 102 Bob 3 alpha …
DENSE_RANK() – What’s wrong here?
I have a very simple table as follows: ID NAME PRICE 1 A 10.45 2 B 8.25 3 A 10.45 4 C 5.00 5 D 4.00 6 E 10.45 When running the DENSE_RANK() select [name], [price], …
Creating FISCAL_WEEK column in SQL Server Calendar table
We have a requirement. We need to create a fiscal_week column in a SQL Server table. Table will have data as Normal_date column as fiscal_date and fiscal_year column which will have year part of the …
How to get the value of jsonb data using only the key number id?
I have a jsonb column with the following data: {“oz”: “2835”, “cup”: “229”, “jar”: “170”} I have the key number 0 that represents the first …
How to execute custom logic at pyspark window partition
I have a dataframe in the format shown below, where we will have multiple entries of DEPNAME as shown below, my requirement is to set the result = Y at the DEPNAME level if either flag_1 or flag_2= Y, if both the flag i.e. flag_1 and flag_2 = N the result will be set as N as shown for DEPNAME=personnel
VBA ADO Date Formatting on Mixed Data Input
we are trying to use ADO to read data from a closed workbook, remove any whitespace and convert any incorrectly keyed dates into a valid format. Once the data has been cleansed, it’s uploaded into a …
Getting empty result from a query
I have Client table ClientGuid Email Phone Also have Activity table ActivityTitle Date ClientGuid If client does some “Activity” it is saved in Activity table. I want to get Emails of clients who did not have any activity in last three months and had activities before. Tried this : Getting empty r…
Postgresql – Looping through array_agg
I have a table from which I need to calculate the number of times intent_level changes for each id. Sample Table format : I couldn’t figure out how to loop through array_agg and compare consecutive elements. Below I tried using array_agg, but then I don’t know how to loop through it and compare co…
get most frequent values in every month in 2021
Trying to get the most frequent values in every month from tables inspection table : can be ignored – > FOREIGN key (lno) REFERENCES restaurant) data : query : output: month id 3 333 4 222 5 222 6 333 expected output – month id 3 333 4 222 5 111 6 222 6 333 Answer IMHO you don’t need