I’m trying to count active users in the last month by comparing the logged event against today’s date. So for example if a user logged in 2 days ago(09/01/2021), the query would evaluate to true since the logged in month 9 = todays month 9 AND logged year 2021 = todays year 2021. since its only us…
Tag: sql
How do I create a table from distinct values in another table?
I have a table of orders that I want to create a table of possible customers from. I would like to take the distinct names in the customer record and create new table in the same SQL database with and assign it an auto incrementing Id as the PK. How can I script this? Answer
Selecting and comparing columns which contain constants
Follow up to this question. Say that on postgres, I have a table TABLE1 containing the columns id (integer), name (string): And attempt to run the following queries: On sqlfiddle, the former yields a result, whilst the latter fails with the message: On postgres 13.3 which I have installed locally, however, ne…
JSON array to a single row in SQL table
I have a table in PostgreSQL database. With some data in it: receipt column contains lists of 3-element lists. What I’m trying to achieve is write an SQL query that returns table in the form The best I’ve got so far is but I’m stuck on unnesting the inner arrays. I tried using jsonb_to_recor…
Need to add double quotes to SQL SERVER string values
I have integer values that are being passed from a parameter that needed to be input as a string and padded so that they are 7 digits. This will then be passed into another query. This codes sets @control_num as (‘0005675, 0045464, 0002323’). I need this to be passed as (“0005675”, …
I am trying to return Col1 as a value for a list, IF Col2 = NULL
I am trying to return all Col1 values IF the respective Col2 value = NULL I have thought of IF Col2 = NULL Return Col2 END IF, But I am unsure of how to format this, because when I run it I get errors saying invalid relational operator. Thank you for your time. SELECT Col1 FROM mytable WHERE IF NULL
Conversion failed when converting the varchar to numeric and displaying numeric values only
got an error converting data type varchar to numeric. when tried to convert numeric to varchar but that is also an error Conversion failed when converting the varchar value ‘7.350000’ to data type int. Answer I strongly recommend case instead of iif(), but it is the same problem. The expression re…
Adding x work days onto a date in SQL Server?
I’m a bit confused if there is a simple way to do this. I have a field called receipt_date in my data table and I wish to add 10 working days to this (with bank holidays). I’m not sure if there is any sort of query I could use to join onto this table from my original to calculate 10
Mysql duplicate results of query relation with 2 other table , IN operator
I have 3 main table : products, stores and categories product relate many-to-many with stores via table product_store product relate many-to-many with categories via table product_category How can i query all products, belongs a store ( with store_id : 1 ) and belongs one or many category ( with category_id i…
Extract data from a historical table keeps only data with changes
I have a table like this and here is the log table So as you understood, my main table contains data from a source every day. But keeps only those with changes. Thus if everyday if 100k records comming normally, but very few (<100) are really has some changes. Thus my table tracks all the records using the…