I’ve been trying to query rows with a date field up to a threshold. For reasons beyond my control, all fields are varchar. I’m trying to filter out null values which case an error when cast. My latest attempt: The above code still hits an error trying to cast “” to a date. I thought th…
SQL – Replace a particular part of column string value (between second and third slash)
In my SQLServer DB, I have a table called Documents with the following columns: ID – INT DocLocation – NTEXT DocLocation has values in following format: ‘\fileShare1234storageabxyz.ext’ Now it …
Count distinct id between months previous year and same months current year Bigquery
I have a dataset in bigquery which contains order_date: DATE and customer_id. How can I count distinct customer_id between the months of the previous year and the same months of the current year? For example, from 2020-01-01 to 2021-01-01, then from 2020-02-01 to 2021-01-01, and so on until the current date a…
Compare COUNT results before and after operation/load in SQL
I need help for one of my cases. Lets say that I have one table with only one column named CustomerID with 1500 records. The CustomerID table is loaded in DB 2 times per day – 10am and 10pm I want to compare the CustomerID table in the morning (10am) with the one in (10pm) SELECT COUNT(*) from CustomerI…
Exclude from resultset based on condition
Given the following table FOO; How can I select all the records except where org = Y and status = CLOSED So the resultset looks like: Answer You can do this either of these ways:
Extremely slow “row_number() over order” query
I have a users table with columns (id, xp, …) and around 1.5 million rows. I am getting someone’s position in the XP leaderboard with the following query (which took 33 seconds to execute): EXPLAIN …
Aggregated row count differences between tables
I have two MySQL tables A and B both with this schema ID entity_id asset asset_type 0 12345 x 1 .. ……… ….. ………. I would like to get an aggregated top 10/50/whatever entity_ids with the largest row count difference between the two tables. I think I could do this manual…
Chinese characters in Access SQL Query
After populating the recordsource the next action is clicking on one of the fields populated to “activate” the record. When clicking this, the goal is that the SEC_ID (A GUID, Number Data …
Assign incremental id based on number series in ordered sql table
My table of interview candidates has three columns and looks like this (attempt is what I want to calculate): candidate_id interview_stage stage_reached_at attempt <- want to calculate 1 1 2019-01-01 1 1 2 2019-01-02 1 1 3 2019-01-03 1 1 1 2019-11-01 2 1 2 2019-11-02 2 1 1 2021-01-01 3 1 2 2021-01-02 3 1 3…
SQL Server: How to flatten nested arrays by merging values using
I have 10000 jsons with different ids each has 10000 names. How to flatten nested arrays by merging values usin SQL Server? Jsons can be read in any language, I’m looking for any SQL dialect that can transform the data as I’m using spark connectors. I use many SQL dialects including not limiting S…