I’m trying to model a SQL table to store salary changes for employees. One approach is using a bi-temporal table as follows: Here an employee was hired on 10/1/2015 with salary of 100,000. Then on 2/15/2016 he had a performance review and his boss said, “we’re bumping your salary to 110,000 effective start of current year (1/1/2016). To store this
Tag: sql
2 SELECT with INTERSECT or 2 condition with WHERE
I have a task that “Get the makers producing both PCs having a speed of 750 MHz or higher and laptops with a speed of 750 MHz or higher. Result set: maker” from https://www.sql-ex.ru For this task I wrote this query which is return nothing (empty screen) But the right query is I wonder what is the differences between these
Modify a query recordset to complete information (MS Access)
im working in a project in Microsoft access to query the date, hour and a quantity of products by line of production. However, I need to display all of the 24 hours of production in a column but cannot do it since I don’t have records for the missing hours. I’m using Totals to sum the quantity of products produced.
IIF or case when in window function
I have some timestamps in a table and I want to create a dummy variable (0 or 1) that tests if the row above is equal to the current row, after the timestamps are sorted. I need to do this in different partitions. Is there a window function that can do this in SQL Server? So I know my partition
MySQL – summing up products from several orders
I want to sum up the number of items purchased from several orders. I have this query, however it shows me each order on a separate row. I want have it all added up in one row. This is woocommerce database. Example output: and this is how it should be: Answer You want the aggregation string over all those items,
Query that merges latest reference in multiple tables into shared rows
I have the following schema which is pretty simple and straight forward: I want to write an optimized query that returns me a list of all phones with their latest message and latest picture taken. In this case with the latest “CreatedAt” for both fields: Example expected data-set: Right now I’m not sure how to write such a query so
How to let DELETE to remove the rows with no constraint problem anyways?
When there is a constraint and one tries to DELETE data on a relation, but some rows can’t be deleted because some constraint, how can one Select those rows that had no problem with the constraint to delete them anyways? Example: Table h1 has 2 elements: h1_1 and h1_2 Table h2 has 1 element: h2_1 h2_1 refers by Foreign Key
MySql improve GROUP BY ORDER BY query with index
I have a table like this: the goal is to query how many products have been sold per day, so the query is: The output is: Ok. Now let us suppose we have a scenario with several records and this query has to be run several times per day, we need to care about performance now in order to retrive
Is It Possible to Derive an Attribute from other Tables Attribute
Is it possible to derive the attribute “total cost” from “Total Cash Purchases” and “Total Salary Paid”. Im also open for suggestion if there are any problems in my …
StartDate invalid identifier
I have two SELECT statment which I connect using UNION and I get error Since I create first one SELECT statment and run and I get result, and second also, so I run separatly and it works perfect. But when I use UNION I get error pusd2.StartDate invalid identifier. What is wrong here ? What I made wrong ? Table