I have a data set like the sample below. I need to query and summarize the number of transactions per shift. The problem is that the shift cycle does not align with the normal hours of a day. Eg. The shift for day 2020-01-01 will start at 06:00 AM on 2020-01-01 and end at 06:00 AM on 2020-01-02 Below are
SQL selecting most recent row inside join
I have 2 tables companies and invoices I want to select all companies with their most recent invoice price. I don’t seem to get it working. This is what I tried: But the query loads very long and I don’t know why. The structure looks like this: companies invoices The BC_ID is the same as the compa…
Running total of positive and negative numbers where the sum cannot go below zero
This is an SQL question. I have a column of numbers which can be positive or negative, and I’m trying to figure out a way to have a running sum of the column, but where the total cannot go below zero. I have tried a number of different window functions on this, but haven’t found a way to prevent t…
Overlap the intervals using mysql
+——+————+————+ | id | start_date | end_date | +——+————+————+ | 1 | 2019-01-01 | 2019-01-12 | | 1 | 2019-01-10 | 2019-01-27 | | …
SELECT with a variable
I have 3 tables : t_object : With all the classes t_operation : With all the operations, link with t_object.Object_ID = t_operation.Object_ID t_operationtag : With all the tag value for each operation, link with t_operation.OperationID = t_operationtag.ElementID I try to get an list for each of my operation (…
How to get the last amount paid grouped by city and year?
I would like to know how to get the amounts paid for the last two months paid per city ordered per year. If I group by year, city name and two months : select year, nameCity, twoMonths, payment …
Unique constraint violated while insert
I’m running this code INSERT INTO ADW12_DW.WC_MFG SELECT * FROM ADW12_DW.WC_MFG_bkp; COMMIT; Prior to this, ADW12_DW.WC_MFG was truncated to update column size. So ADW12_DW.WC_MFG is empty before …
SQL multiple SELECTs very slow to run
I have the following query (Postgres) which gives the correct results, but it is very slow. Any ideas how I can make it faster? SELECT DISTINCT(vesselimo), vesselname, (SELECT COUNT(portid) FROM …
Select distinct values from a text column in PgAdmin [closed]
Using pgAdmin4 I imported text files to a table with 13 columns. The table columns are all of type text. The first column, named Date, contains text of dates (YYYY-MM-DD). There are multiple rows …
Why Select Distinct stored ‘As T’ and how to alias?
From this helpful response on another question on stackoverflow.com (How do I count unique items in field in Access query?), the answer contained: SELECT Count(*) AS N FROM (SELECT DISTINCT Name FROM …