I have a table like this: room_id | name | time 1 | Kate | 2019-09-18 10:00:00.000 1 | Michael | 2019-09-18 12:00:00.000 1 | George | 2019-09-18 14:00:00.000 2 | Tom …
Update statement generating null violations on pre-populated column
I’m trying to update rows in PostgreSQL DB. When I try to run an update query to update values in 2 columns (with no constraints). My table structure is as follows (variable names changed) schema….
My query works but I need to make is scalable. I need to remove unions but not sure how to proceed
I need a list of the number of times a file has processed through our system. For example in week 1, for a given warehouseID, x number of files have processed 1 time, x number have processed 2 times, …
Value counts for every field of a table
In PostgresSQL, I am trying to get the value counts of every field and value pair. For illustration, I have 2 columns, but I really have 100 columns. So, I need a programmatic solution. For a table,…
syntax error in a nested “sum(case when” statement
I have a database with information from several companies that record their data differently. To mark what is a credit memo/finance charge/invoice/etc- 3 companies use a certain field, while 3 of the …
How do I find the 2nd most recent order of a customer
I have the customers first order date and last order date by doing MIN and MAX on the created_at field (grouping by email), but I also need to get the customers 2nd most recent order date (the order date right before the last orderdate ) Answer Use window function ROW_NUMBER() (available in MySQL 8.0): This w…
Combine SQL Data into 1 row
I’m attempting to build code to create an SSRS report. The folder type ID 19 indicates a Member ID. If the Folder type ID includes a *1300 it indicates a provider ID that is located in that field, prior to the *1300. The issue with how I am currently doing it is that it is creating 2 rows. One where
Convert varchar to date in Oracle SQL with month name
I have 2 columns in a large data set. Col 1 “review_date” is a varchar “September 16, 2019” I would like to have this information in DATE format as 09/16/2019 Col 2 “complete_date” is a varchar “…
Join three tables based on one key, putting data into same column
I have three tables that I am trying to join together to check that the proper data matches. I have table A which is a list of all accounts that a commission was paid on and what that commission amount was. I have Table B and Table C which are two tables that have commission calculations in it. The goal
How to use timebucket_gapfill when rows can have null values?
I have a time series table where measurements are recorded into “wide” rows. Rows may contain all measurements or only some. The other columns are then set to NULL. I would like to use timebucket_gapfill() to “clean” this table and make sure that every row in the output has data in all…