How do I assign a result depending on date using sql in access?
I have two tables: The first is a table of roles where each person is in a team but person c has changed teams in June:
The second is a table of signings where it has the name of who signed it and when it was signed.
What I need is some sql that will create a query that will connect the two tables and then show the team name of that person at the time of signing so signing 5 would be when person c was in team x, but signing 6 was when c was in team y. Potentially, but less important would be to also include ability to have Person d who doesn't exist in the role table so would need to show "not found" or blank
Solution
You can use this in SQL View of your query. I changed field names so they do not include spaces or reserved words:
SELECT
sgn.Signing,
sgn.PName,
sgn.DateOfSigning,
ply.Team
From Signing sgn
Left Join Player ply on sgn.PName = ply.PName
And sgn.DateOfSigning >= ply.StartDate
And sgn.DateOfSigning < ply.EndDate
- Cannot drop PostgreSQL role. Error: `cannot be dropped because some objects depend on it`
- SQL full outer join, default value to column in other table
- Error message: (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
- SQL filter rows by range overlap
- EXPDP doesn't recognise user names
- SSRS 2014 date range parameters not working with an OR clause
- Is there a way of referencing the prior observation's values in SAS/SQL when defining a new variable?
- Calculate duration using T-SQL
- How can I set a numeric wildcard in MySQL?
- SQL replace colum value with a specific string
- Conversion failed when converting the nvarchar value to data type int while inserting records into table
- Possible to Stringize a Polars Expression?
- Weighted hierarchical cumulative sum
- SQL statement evaluation order
- SQL rollup to add a total row for multiple columns
- SQL: query to select all records from join table
- Select SQL Server database size
- Order by the oldest relation Postgres
- INSERT INTO a temp table, and have an IDENTITY field created, without first declaring the temp table?
- TSQL: how to write this query?
- How to create a role in postgress db which supports the CRUD operations but doesn't allow to drop the database and tables with that role
- How Best to Extract Specific JSON Key-Value Pairs in Snowflake
- Are MySql Views Dynamic and Efficient?
- Create a new column based on duplicates of another column
- How to match req.body with update query?
- Dapper - Map join query to DTO using 'splitOn'
- SQL command works fine when run manually (SQL Developer) but gives ORA-00922 in Python's oracledb module
- Derive values from previous Quarters
- Fill null values in BigQuery table with next non-null value from specific column
- Error while trying to migrate data from one table to another in BigQuery