I have a BigQuery table like this id start_date end_date location type 1 2022-01-01 2022-01-01 MO mobile 1 2022-01-01 2022-01-02 MO mobile 2 2022-01-02 2022-01-03 AZ laptop 3 2022-01-03 2022-01-03 AZ mobile 3 2022-01-03 2022-01-03 AZ mobile 3 2022-01-03 2022-01-03 AZ mobile 2 2022-01-02 2022-01-03 CA laptop 4 2022-01-02 2022-01-03 CA mobile 5 2022-01-02 2022-01-03 CA laptop I want to
Tag: sql
How can I merge this query so that it returns only one column?
I have the following table: I tried the following query: This query returns two columns rather than just one. How can I make the query one so that only one column gets returned? Answer You can use UNION tojoin rows vertically
SQL Case Statement: Set the default value for an Case
this time I need an explanation for SQL. I have seen on the internet now nothing special and maybe I have a thinking error. I have a case statement and now I want to ” filter ” it in different ways. Specifically I have a question, whether it is possible to provide rows directly with the “ELSE Value” of the
PostgreSQL: Select latest entries only when values differ in a column
Imagine the data: Per id, I would like to return the two latest entries of different dates (not just timestamp, the date part should be different): I assume I need to use partition and lag on the audit_id but I don’t know how to start structuring it. Answer I would attack this in two parts. The first would make sure
How to create a column that shows if a date appears in the last 7 days in BQ?
I’ve got a table that shows me a user_id and the dates they were active (this is derived from a massive events table). The table looks like this: user_id active_date 1 2022-06-16 2 2022-06-02 1 2022-06-14 1 2022-05-01 I need to create a query to find if a user has been active in the last 7 days, 8-14 ago, 15-21
SQL query to count number of accounts based on criteria A only, B only and both A&B
Basically, I have a table that tells about type of communication to accounts(either by letter or email) like : ACOUNT_ID TYPE_OF_COMM 1 Letter 1 Letter 3 EMail 2 Letter 4 EMail 4 Letter and so on.. A query needs to be written that counts : the number of accounts that received letter only, the number of accounts that received mails
Python how to update a sql server from a dictionary?
I have an INSERT statement that looks like: How do I turn it into an UPDATE statement? I’ve got this so far… Not sure how to join the key/value pairs in the dictionary to make it in the right format. Edit: Answer here is the generated query string that you can use Read More Here
Get different results with the same query in T-SQL and LINQ (EF Core)
I’m trying to retrieve records from a database in Azure and for the checks I run T-SQL queries directly and then pass them to LINQ against the EF Core context, but I’m running into this problem. When I filter for the records with FechaOrientativa greater than or equal to Today, the Estudiar field is equal to ‘pt’, I get 2,296
All records with Pivot table
I have table as below after pivoting, I need like, I need to remove all the null values and list like above table. My Query as below, Thanks in advance. Answer Rather than using the modulus three, you should be using the remainder with a divisor of three. But I would avoid PIVOT here and just use a regular aggregation:
get dates periods
it is necessary to get the date periods. input data: expected result: Answer This is a gaps and islands problem. One trick we can use here is to create a pseudo-group which tracks to which island of continuous dates each record belongs. Demo In the first CTE above, the alias cnt gets assigned to a value of 1 whenever the