I am trying to get a list of new products. For that, I should look at the date they had movement. If the movement was in 2022, I consider it as new, but if the movement is before 2022, it is no longer new. If i’m set as a condition that the date of the movement be greater than 2022,
Tag: sql
How to specify information from the table being inserted on PostgreSQL upsert?
I am trying to insert values from a temporary table (NewThings) with columns Thing and Counts into my main table (MyThings) with columns Thing and Count. If a thing does not exist in the main table I want to insert a new row, otherwise I want to add to the counter. However, I am struggling with the correct sy…
How to use `ROW_NUMBER` but grouping by days in PostgreSQL?
I have a table (t1) like this: (and so on) What I want to do is to get a new autoincrement column based on day index, starting by 1. I guess I could achieve that by using something like ROW_NUMBER: But with this query I get the following: Which is in a sense something similar to what I’m looking for,
User function return query and insert into
I trying to make function, that merge where select query return result as value(not table) and insert into that use value from select statement. The function where argument gets from user, and this argument provide into select query on table_foo that return value id where I try set and use in insert into to d…
Can I use a CTE to make these two tables become one?
Is there any way I can make these two queries become one query? I need all of this to be in one table. Currently I’m not using any common table expression and I believe if I use them I can make these two codes into one single table. I would also like a column called ‘G’ which is a sum
troubleshooting a SQL code for cohort analysis
Having a problem with a code I wrote to perform a cohort analysis for repeat purchases. Can someone help me? My code was able to identify repeat purchases and assigning cohort for the most part BUT it is unable to assign a proper label for the last purchase of that customer. It’s being labeled as “…
How do I create a recordset of nested queries from different databases
The following 2 queries are taken from tables on different databases I’d like to nest these in the following query However, I am getting an error: The connection cannot be used to perform this operation On the following line: rrst.Open mkQry I guess it has to do with MyQuery and MyQuery2, both being fro…
Does a Self Join or EXISTS Help here?
I’m Using Advantage Server 12. I need a result set that finds all the records when the supplied parameter occurs in either of a master or a detail table when the supplied value is not the linking field. Customer Table N < 2 Contact Table N >=0 Require the Row from Customer and All rows with matchi…
Postgres – sort and compare strings
I have the below table: I want to concatenate all A strings and all B strings, sort them and compare the 2. This gives me the concatenation: This is the output: As seen, both of these strings are same if we sort them and compare. How can I do this in Postgres? Here is the fiddle to try: http://sqlfiddle.com/#…
Get the average difference between two values in time period
What I have: A single table with ids and prices for date and time. What I want to do: To get the average difference of price for every id between the first and 15 minute of every day. Conditions: I also want to use the last available price in 15 minutes if the 15 is missing. For example if 15