Gordon | Lightweight team communication tool built inside Command | Command Line Interface library

I am trying to solve a "gaps and islands" by date issue I'm facing (kudos to Gordon Linoff helping me identify this issue). I want to group the below table by person, office and job while respecting order by person,from_date. consider the table below:

...

I have an Access query (qr1) that returns the following data:

dateField stringField1 stringField2 booleanField 11/09/20 17:15 John Nick 0 12/09/20 17:00 John Mary -1 13/09/20 17:30 Ann John 0 13/09/20 19:30 Kate Alan 0 19/09/20 19:30 Ann Missy 0 20/09/20 17:15 Jim George 0 20/09/20 19:30 John Nick 0 27/09/20 15:00 John Mary -1 27/09/20 17:00 Ann John -1 27/09/20 19:30 Kate Alan 0 28/09/20 18:30 Ann Missy -1 03/10/20 18:30 Jim George -1 04/10/20 15:00 John Nick 0 04/10/20 17:15 John Mary 0 04/10/20 20:45 Ann John 0 05/10/20 18:30 Kate Alan 0 17/10/20 15:00 Jim George 0 17/10/20 17:15 John Nick 0 18/10/20 15:00 John Mary -1 18/10/20 17:15 Ann John 0

Notes:

• The string data may by repetitive or not.
• The date data are stored as string. I use a function to convert it as date.

...

Looking for some help with code in SQL Developer query to flag any 2 temperature readings - every rolling 12 hours - if they are greater than the acceptable benchmark of 101 deg F.

The given data fields are:

• Temp Recorded (DT/TM data type ; down to seconds)
• Reading Value (number data type)
• Patient ID

There are multiple readings taken throughout a patients stay, at random times.

Logically, we can check if two adjacent times total 12 hrs or more & EACH of their temp readings are >101 but not sure how to put it into a CASE statement (unless there's a better SQL syntax).

Will really appreciate if a SQL only solution can be recommended.

Many Thanks

Giving the code below including the CASE statement as provided by @Gordon Linoff underneath. The below sample code is part of a bigger query joining multiple tables:

SELECT CE.PatientID, CE.ReadingValue, CE.TempRecorded_DT_TM, (case when sum(case when readingvalue > 101 then 1 else 0 end) over ( partition by patientid order by dt range between '12' hour preceding and current row ) >= 2 then 'Y' else 'N' end) as temp_flag

FROM edw.se_clinical_event CE WHERE CE.PatientID = '176660214' AND CE.TempRecorded_DT_TM >= '01-JAN-20' ORDER BY TempRecorded_DT_TM

...

EDIT:

This problem was caused by DOS style line ending, and has nothing to do with how large the file is, as I stated in the question, because when try to research the problem I used small samples edited on my linux desktop, which does not have the problem at all.

I kept this question just for reference because for anyone not familiar with this problem, it is very hard to describe to potential helpers!

Thanks to @Sundeep and @jared_mamrot

I have a file test.txt which lists a bunch of filenames, looks like this:

...

This might not be possible, but here this is the request I'm trying to translate into SQL: For every date where at least one entry exists in the ACCOUNT_ENTRY table, I want to find the sum of all entries for all accounts (in the ACCOUNT table) for that date. If an account doesn't have an entry for that particular date, I want to use the next latest entry up to that point.

Note that I'm currently doing this on an H2 DB, but that could change in the future so I'm trying to stay away from vendor specific Stored Procedures.

Example ACCOUNT table ID ACCOUNT_LABEL 1 account 123 2 account 456 3 account 789 ACCOUNT_ENTRY table ID ACCOUNT_ID ENTRY_DATE BOOK_VALUE MARKET_VALUE 7 1 2021-05-31 100 110 5 1 2021-05-28 90 100 6 2 2021-05-28 70 80 4 3 2021-05-28 50 60 3 1 2021-05-27 80 90 2 2 2021-05-27 60 70 1 3 2021-05-27 40 50 Desired result ENTRY_DATE SUM_OF_BOOK_VAL SUM_OF_MARKET_VAL 2021-05-27 180 210 2021-05-28 210 240 2021-05-31 220 250

• 2021-05-27 Book value = 80 + 60 + 40 (Row ID 1 + 2 + 3)
• 2021-05-27 Market value = 90 + 70 + 50 (Row ID 1 + 2 + 3)
• 2021-05-28 Book value = 90 + 70 + 50 (Row ID 4 + 5 + 6)
• 2021-05-28 Market value = 100 + 80 + 60 (Row ID 4 + 5 + 6)
• 2021-05-31 is equal to the results from 2021-05-28 + the new single new entry from 2021-05-31 (Row ID = 7)
  • Book Value = 70 + 50 + 100 = 220
  • Market Value = 80 + 60 + 110 = 250

Result I can get so far ENTRY_DATE SUM_OF_BOOK_VAL SUM_OF_MARKET_VAL 2021-05-27 180 210 2021-05-28 210 240 2021-05-31 100 110

This would be easy to do in my application code, but I was hoping to leave the compute to the DB and not transport all the data over to the application. I could also roll data forward, but then I could end up with significant chunks of data which aren't necessary.

Here's the query I'm using so far, but it doesn't handle situations where it has to look back at previous entries.

Query ...

using a SQL query I am trying to get a max value from multiple rows, using 2 columns as 'key', and then sum them and move on t next 'key'

Here is an example table. It has years, userid and points. Each year has several weeks.

What I want to do is to take each users MAX points for each year and SUM them.

year userid week points 2020 1 1 3 2020 1 3 3 2020 1 3 5 2020 1 4 12 2020 2 1 4 2020 2 2 4 2020 2 3 6 2020 2 4 10 2021 1 1 4 2021 1 2 5 2021 1 3 8 2021 1 4 9 2021 2 1 3 2021 2 2 6 2021 2 3 7 2021 2 4 13

I'd like the result for each year to be

...

I am trying to use Beautiful Soup to scrape the first 3 Columns from a table in this Wikipedia Page.

I implemented the solution found here.

...

I have this table that gives the names of the father and the children:

id names 1 Frank 2 Gabriel 3 Geoffrey 4 George 5 Gordon 6 Heather

This second table tells who the children of each parent are, for example Geoffrey and Gabriel are Frank's children, and George, Gordon and Heather are Geoffrey's children:

id id_parente id_child 1 1 2 2 1 3 3 3 4 4 3 5 5 3 6

I have tried in various ways to find a way to make a query that gives me the name of the father given the name of one or more children, for example if they give me George and Gordon the query should give me Geoffrey; of course if they ask me to obtain the father of Gabriel and Gordon the result of the query must be Null because these two children (Gabriel and Gordon) do not have the same father. Is there a way to do this query in sqlite3? Thanks in advance

...

*** UPDATE *** Code is added below.

I need to calculate and get inventory available date for each Sales Order(SO). Each SO fulfillment is based on first-in-first-fulfill. Fulfillment is based on currently available inventory with purchase (PO) quantity and PO arrival date. For example:

Current inventory quantity: 100 ea

PO quantity and available date is:

...