I need to find a list of frequent flyers in a database. There are 2 important tables: Demographics and Visits. This is what I have so far: It runs and returns people who visited last year 5 or more times. However, what I ACTUALLY want is people who visited 5 or more times, and have at least 1 visit last
Tag: sql
How to join two tables to determine date ranges when one table contains (id, start_date) and another contains (id, end_date)
I’m new to SQL, hope you guys don’t find it silly. Working with two tables here, one contains start dates and other contains end dates. Entries do not follow sequence/possibility of duplicates. Answer You can use union all and aggregation with some window functions:
MySQL Error – Cannot add foreign key constraint
I know this question has been asked several times but it seemed like the problem was due to different data types between parent and child rows. In my case, the data types are the same but I’m still getting the error. Here’s my code Where DORM_COSTS’ foreign key cannot be added. Thanks! Answe…
MySQL return total COUNT of each value in a column
I have a table of jobseekers with three columns Each jobseeker could have different hiring status for each employer depending on the interview. Now I want to return the COUNT of each total HIRING STATUS But it should only count the jobseeker’s highest hiring status. Say that John was ranked as QUALIFIED…
Is there a way to split text from cell into multiple records in SQL using line break delimiter?
I have a system generated report that exports all expense reports for a given period. All employees attached to the report are exported in one field “Attendees”, separating multiple employees with line breaks. I would like to break the cell into multiple records, still including the expense report…
Pivot table without crosstab/tablefunc
I have a table like this INPUT I want to be able to pivot this table in the following manner OUTPUT All the articles that I can find online pivot tables based on a second column. My ultimate goal is to get one records per ID. FINAL OUTPUT Here the values of the size, python, cpp, non-code columns are aggregat…
Select closest maximal numeric value in Firebird
Imagine there’re 2 tables, let’s call them “Master” and “Detail”: There’re 2 input parameters: list of Master IDs (master_id_list) and numeric value (num_value). For every ID in master_id_list I should get one Detail record: If num_value < MIN( f_value ), it should…
table in database rows
I have a table People, having people_id,people_name,people_lastname. I am facing difficulties creating a pl/sql function to display all the rows of that table. I have done this much so far Answer One option is to return refcursor:
Need to group records by consecutive date SQL
I’m building a query in order to build consecutive dates which is create table #consecutivedates ( sgid nvarchar(max), metric nvarchar(max), targetvalue nvarchar(max), startdate …
In SQL get all rows with same value in column A that have only non-null values in column B
My SQL table has multiple columns, among them A and B. In A there’s some sort of identifier of some arbitrary object (say, a user or an item). Each object instance identified by A can have multiple entries in the database (A is not unique). Each object furthermore has a property B that is also stored in…