I have 2 tables with a parent/child hierarchy and am trying to pull the ID for the latest child record for each parent record, but also with conditions applied on the child record that is being pulled. Here’s what my tables look like: Now, let’s say I want to pull ID of latest QuizAttempt for each…
Tag: sql
Power BI Report Builder Indicator Formula
I am adding in an indicator to a PBI Report Builder Report. The indicator is based off multiple fields from the dataset so I need to use a formula, to create the three up/down/side arrows. Previously in Crystal Reports this could be implemented using a series of IF statements as follows. The below example is …
Excel into Azure Data Factory into SQL
I read a few threads on this but noticed most are outdated, with excel becoming an integration in 2020. I have a few excel files stored in Drobox, I would like to automate the extraction of that data into azure data factory, perform some ETL functions with data coming from other sources, and finally push the …
Order results by “Best Match” SQL
I’m working on a project which requires the user to search for certain categories of products. The products have tags such as: T-shirt, Jumper, hat and I am currently displaying the results ordered by the amount of likes. Here’s my SQL statement: But the issue is that if let’s say someone se…
Unknown column ‘r’ in field list
I’ve been working on a SQL query for a project, and I face an error message when I want to use it. Here is the query itself : MySQL answers me “Unknown column ‘r’ in field list”. But I dont really understand why I get an error here as I define r in my INNER JOIN. I’m kinda …
Select last value in each group
In database I have two table. I want to join two tables and get last row each SensorType Sensors table: Id CodeNumber PortNumber SensorType IsActive 13 Dht11 3 0 1 14 Dht11 3 2 1 17 Global 100 4 1 18 Dht11 3 1 1 19 Dht11 3 3 1 SensorsValue table (SensorId is foreign key Sensors table): Id SensorId
VBA ADODB error (80040e14) when trying to retrieve data from SQL Server
I am trying to connect to SQL Server from Excel VBA using the following code and with some specific simple queries it actually works like a charm. My problem shows up whenever I try to run the query right below to which I get the following error: ‘-2147217900 (80040e14)’ An expression of non-boole…
Convert multiple rows into single row with more columns
I have a SQL Server table of properties related by an id and want to do a query to check if an id has a set of properties. Basically I want to do a transform as follows, grouping by the Id and adding a column for each unique property: Starting table: Id Prop Value 1 P1 V1 1 P2 V2
Oracle SQL hierarchical query from bottom to top
I have a table where I want to go from bottom to top using hierarchical queries. The problem is that I need the get the value of one column from root (top) using CONNECT_BY_ROOT, but since I reverse the way the hierarchical query works (reverse the prior in connect by and the start with), this function (CONNE…
How to extract date from a long string in SQL Server
I a long string like below in my column and I need to extract only the date (2021-07-05) from it. Could anyone please help? Answer We can use PATINDEX with SUBSTRING here: Demo The call to PATINDEX above finds the starting position of the date, while SUBSTRING takes 10 characters from that position.