Data source User ID Visit Date 1 2020-01-01 12:29:15 1 2020-01-02 12:30:11 1 2020-04-01 12:31:01 2 2020-05-01 12:31:14 Problem I need advice im trying to do sub query for this result to mark user as retention if they havent visit back for 3 month. i using this query for the data to get user’s latest vis…
Tag: sql
Turn Cross Apply opensjson results into columns
We have a table that has a field with some json. using cross apply I was able to turn those pairs into columns: So how do I turn this into something like : Thanks. Answer If you know the limited set of possible key names in advance, you can just use PIVOT: But you can’t write a PIVOT if you
How to do a conditional statement in SQL where clause
What I’m trying to do is when @aValue parameter is like ‘abc%’ then run Otherwise if the @aValue param is not like ‘abc%’, then select all the records where the f.name is equal to @aValue. I’m trying to accomplish this using a CASE statement within my Where. In my where sta…
Is there a way to update ONLY field.type data onto SQL using dbWriteTable? Without having to pass the whole table’s value
So pretty much I am pulling a whole table from my database using: So this pulls my data as I need. After that perform some tests and to check the size of each column. I reassign field types based on my test. After that I would send the data back to the database using: Because I am not manipulating any
Second member of Azure AD group can not create schema in Azure SQL database
We have a curious issue with our Azure SQL database with AAD authentication. We have created a role (dbt_user) with all permissions, and excluded some schemas: Then, we add an AAD group (myAADGroup) as user, and add this to the role above: There are 2 members of the AAD group: user1@domain.com and user2@domai…
How to count number if special type of value by day in SQL?
I have a table in my database which looks like this: I want to get table with number of upload value_types per day (my time column is not per day as you see). So for this example it must look like: How should this SQL query look like? I dint understand turning time into days part especially. Answer You seem
Return row per key based on precedence in Oracle’s SQL
I have data as below. Some items have more then one type id. I need to select only one from each id. My condition is that if exists (type id of item == 1) select only that row; if not exists type id is 1 check if exists (type id of item == 2) select that row; if not exists
SELECT after specific row with non-sequential (uuid) primary key
With the following table: How could I retrieve n rows after a specific id, ordered by inserted_at ? Answer I want to retrieve n rows after a specific id, ordered by inserted_at. I am expecting something like this: For this, you want one additional index on users(inserted_at).
How to perform Case statement inside a select statement?
I wanted to put ‘No record’ on the column instead of NULL if the datediff function returns a null value. So far this code only throws Incorrect syntax near ‘CAST’, expected ‘AS’. but I don’t know what data type should I put in the CAST parameter , since if there’…
MySQL query shows something like 0x8081 instead of special characters
When i run SELECT CHAR(128,129,130,131,132,133,134,135,136,137); I got 0x80818283848586878889 istead of Çüéâäàåçêë. Does anybody know why? I’m using charset utf8mb4. When I run show variables like ‘%char%’; I got I’m using MySQL version 8.0.26 Answer The output charset is “DOS We…