I have a table that has ContactNumber (Bigint), MemberNumber(bigInt) and ContactDate(date). The table has millions of records (sample data image attached). I want to get all the records where member has not been contacted for last 18 months. How do I do that using tsql? Your help is highly appreciated. Thanks. Answer You must have a Member table containing all
Tag: window-functions
Oracle Remove Duplicates and Update Rows in a Table with a Value from Another Table
In my associates table I have 4,978 people with at least 1 duplicate. asscssn | count(*) ——— ——– 123456789 8 987654321 5 234567890 5 Each duplicate for a person has …
Create a Status column with 3 categories based on conditions in multiple columns in SQL
I have a table like this CREATE TABLE proc_code ( id INTEGER, c_date DATE, code VARCHAR(255) ); INSERT INTO proc_code (id, c_date, code) VALUES (101, ‘2017-12-21’, ‘3027f’), (101, ‘…
How do I do this without a subquery?
I need to make the same query, but without a subquery! I need to get the count of rows in which genre_id is equal to the :deletedGenreId and the org_type_id value of this line in this table is not unique Answer There is nothing wrong with sub-query. Sub-query actually can help boost performance and increase code readability if you do
Compute 2 Columns from current row and from the row above
I have a table that looks like this My target is to look like this Basically what it did is to sum all the way down based on stock in and stock out from beginning balance however I cant achieve it. …
Subtract a number from the rows until the number ends to zero
How can subtract a number from the rows to the end of the subtraction in MySQL with query update If have table like this and i want subtract “80” form qoh to get the output I tried by and not work What is the appropriate adjustment to do? Answer If you are running MySQL 8.0, you can do the computation
MySQL Running Total By Group, Time Interval
I would like to take a table of customer orders like this: And create a table calculating a cumulative running total by week, segmenting the weeks by 7 days starting at the earliest date (2020-03-01, 2020-03-08, etc.). Something like: Thanks for the help! Answer You can use aggregation and window functions (this requires MySQL 8.0). It is easier and more
How to calculate average for every month from start from year in Presto’s SQL (Athena)?
Below is an example of the table data I have I would like to generate a result set as below I tried to use window function OVER() and PARTITION() but I managed to get average on month by month rather than starting from the year. Any suggestions, please. Thanks Answer I think you want: This puts each year in a
Getting values from the first row, last row, and an aggregation in MySQL Window function
For a marketing related analysis I need to provide data on the first- and last-touchpoint and of the number of total interactions with our website. A simplified version of our interaction table looks like this: Our current approach looks like this: Currently, we observe that the runtime scales approximately linear with our data, which will become infeasible to compute soon.
SQL Subquery with aggregate function – seeking alternative
My table (Table1) I did a sort of Pivot and it’s fine: http://sqlfiddle.com/#!18/45b9d/1/0 Now I need to calculate the difference between measures, to get something like this: But if I don’t use an aggregate, the result brings me null cells – http://sqlfiddle.com/#!18/45b9d/5/0 And if I do use it, I get the message: Cannot perform an aggregate function on an expression