I am a little confused out a CTE works. I need to convert my statement from a rollup with a UNION to a CTE… but the CTE is a bit confusing me. Any Ideas how to approach this? Answer You can express this using a CTE as:
Tag: sql
SQL Join using a Junction Table not returning expected results
I’m joining 3 tables in SQL Server for the purpose of mapping users to their managers. The purpose of the junction table is to map their full usernames to their network ID. Here’s the query: The first 3 columns appear as I’d expect, showing the Network ID, Full User Name and the Manager̵…
Laravel Migration: Combine multiple values in a new colum
i’m trying to make a migration using Laravel. The idea is to get the values of columns ‘code’ and ‘id’ in different tables, and merge them into a column ‘name’ with ‘-‘ separators. So in part it is a SQL problem. Currently i’m trying something like t…
Selecting first element in Group by object Postgres
I have the following table and I want to get the specidic Amount per loan_ID that corresponds to the earliest observation with greater than or equal to 10 dpd per month. The expected output: Answer Returns: Loan_ID month Amount 1 2017-01-01 50000 2 2017-01-01 8784 You can find test case in db<>fiddle
sql join gives cumulative results
I want to join county and county_type tables and get only those county that doesnt have a type =30. From below example id = 1 and 3 should only be the expected output county output: Tried this: what i am getting is cumulative: Answer You want not exists:
How to union all, but separate the columns in mySQL
The first query returns the data for the last 7 days, and returns 90 in total, while the second query returns data for the previous 7 days, returns 55 in total. Current output is: But the output should be: Answer You can use conditional aggregation:
How to get the time difference of first occurrence of a record with another record
We have a monitoring system that posts data to our postgres database in this format: Our goal is to calculate the time between wa-event-triggered events and wa-event-recovered events, if i have data that looks like this, my query below works just fine. This gives me this table which is great! However the data…
How to save date in SQLite flutter database?
I am making an app in which users can fill a form and can save their financial transaction details for every transaction they make, for this, I want to add a date as well and also I want to fetch data using the date as well. I am creating my database as follows: I am mapping as follows: I have
SQL Server 2019: Cannot combine two queries, when 1 parses string
There are two tables (table1 and table2). Table 2 has a column called TaskDescription which has a string with several ‘tags’ embedded in it. I cannot change how that data is stored, as it comes from a a third party. I am able to pull out the taskID I want to grab all of the information from table …
How can I conditional join two tables to get results based on an evaluation (SQL Server)?
I have two tables in my database: Offices Positions How can I select all entries from Positions based on their Department and Section, but one above their level? I’m guessing I need a join with some kind of condition that gets evaluated to do this. For example, the Senior Dev position is at level 1. So,…