I have a SQL Server table similar to this: InkitemNo CapacityUnit NewInk OldInk ReturnInk ProdQty Description UsedInk 204 Machine1 5 2 0 4000 Next ? 223 machine2 4 3 1 8000 NULL ? 204 Machine2 0 0 0 5000 Next ? 224 Machine2 4 0 2 3000 Next ? I’m trying to write a query with this formula: Example to
Tag: subquery
How to pipe window function output directly into a new window function in SQL?
I am new to SQL and I have the following query: This doesn’t work due to no such column: MyMax. Even though from my understanding the column for MyMax is being created on the fly, I’m guessing SQL still isn’t able to use its values immediately as an input into the next window function I alre…
SQL Query to fetch the customers registered in the DB without email address(CS can have phonenumber and email in the same field but duplicating)
I need help with writing this query please , in the Database – the customer is registered twice , one row with the email address in the VALUE field and the other row with phone number in the SAME VALUE field . I want to fetch customer who DO NOT HAVE email address in the VALUE FIELD . For example
Extract the record for last hour for specific date
I am trying to extract the last hour (TKT_DT) record for number of tickets (TKT_DN) from sales table (PS_TKT_HIST) for specific date (BUS_DAT). I have the following code but it extracts the number of tickets (TKT_NO) for each hour. I want to filter the last hour only. Here is the code I used: I get the flowin…
Why am I unable to select all records in one table that don’t exist in another table?
I have two tables like: Desired output: I want to select all records in col1 of table_1 that aren’t in col1 of table_2. In actuality, table_1 has 65000+ rows and table_2 has around 2000 rows. My query: This returns no records. However, the reverse works as intended when I select all records in table_2 t…
Using a subquery in a case statement in a where clause
I want to select different results (from a temp table) based on what a certain parameter is set to in the query. I want to exclude certain inventory SKUs if a particular warehouse is selected. I am getting this error, unfortunately. Msg 512, Level 16, State 1, Line 310 Subquery returned more than 1 value. Thi…
Displaying Unique Temperature Values for the US
I am looking to do something similar to what I did in R (below) in SQL: The output of this code looks like this: So far in SQL I have managed to output a single state using the following code: The output looks like this: However, I am unable to get the unique avgTemp for each state. When I take
Getting different results from Subquery and JOIN which seem to be the same
I’m doing a challenge problem on DataCamp. It uses two tables, “economies” which contains economic information by country code, and “countries” which contains general country information by country code. The challenge is: Get country code, inflation rate, and unemployment rate in…
SQL – Ordering table with subquery for select
I am trying to do ordering on query with subquery for a name from uuid. Let’s have those two tables in MySQL: bans: id uuid time reason 1 c6b8eade-7db9-345b-b838-8f8361552cf5 1642369382 Test 2 0138c279-c5fa-3acd-adaa-8edb7b4569d8 1642384565 Spam 3 3c01262c-a3c3-3133-ba43-92a9ded01c27 1631876477 Hax user…
Find the number of employees working under a manager
I have three tables called branch,employee and manager structured like this I want to view the id of each manager and the count of the employees he has under his supervision like this I have made this so far that shows which branch each manager works on but i don’t know how to proceed with the counting.…