I have a table that shows date ranges of an object. For reference The object needs some sort of event in order to prevent it from expiring. We have a rule where we can apply a protection against the object from expiring, so that there is no need to action the object. The date ranges are a period of time
Tag: date-range
How to calculate sum of a value with only “day of week” using a date-range?
I have 2 tables namely Item table with details of item_id, store_id, offer_start_Date and offer_end_date Store table has store_id, day_of_week, store_hours The structure of both tables are follows – 1) Item_Table : Store ID Item ID offer_start_Date offer_end_date NY0001 FMC0001 2021-10-30 2021-11-04 NY0…
MySQL- Find records with overlapping period
I have a table hotel_booking reference start_date numberOfDays ABC 11-08-2021 2 DEF 12-08-2021 2 GHI 13-08-2021 3 JKL 10-08-2021 2 So the first task I wanted was to get all bookings which has startDate between x and y date which is straight forward query This returns me ABC and DEF which is perfect. Now I wan…
Generate a range of records depending on from-to dates
I have a table of records like this: Item From To A 2018-01-03 2018-03-16 B 2021-05-25 2021-11-10 The output of select should look like: Item Month Year A 01 2018 A 02 2018 A 03 2018 B 05 2021 B 06 2021 B 07 2021 B 08 2021 Also the range should not exceed the current month. In example above
Create an array in Snowflake
I want to create an array constructed from two timestamps. After that, use this as a range to find the difference between the first element and the second element. The goal is to find the time difference between timestamps within business hours (9 am to 17 pm). What should be the right approach here? Can I cr…
How to find the most appropriate date between overlapping date periods in mysql
I`w got an 2 tables with such data: Table 1 id s_id s_date 1 33 2021-03-05 10:36:59 2 33 2021-03-06 10:36:59 3 33 2021-03-09 10:36:59 4 33 2021-03-10 13:36:59 5 33 2021-03-11 12:36:59 6 33 2021-…
insert extra rows in query result sql
Given a table with entries at irregular time stamps, “breaks” must be inserted at regular 5 min intervals ( the data associated can / will be NULL ). I was thinking of getting the start time, making a subquery that has a window function and adds 5 min intervals to the start time – but I only…
Group Records based on predefined date range in SQL (Oracle)
Is it possible to group records based on a predefined date range differences (e.g. 30 days) based on the start_date of a row and the end_date of the previous row for non-consecutive dates? I want to take the min(start_date) and max(end_date) of each group. I tried the lead and lag function with partition by i…
Create new date ranges from overlapping date ranges and assign an ID
I have the following table ID | START_DATE | END_DATE | FEATURE ————————————— 001 | 1995-08-01 | 1997-12-31 | 1 001 | 1998-01-01 | 2017-03-31 | 4 001 | 2000-06-14 | 2017-…
Postgres Left Join with where condition
I need to left join two tables with a where condition: time_table record_table I need to get all those records which are present under given date range. In the above example, I need those records that lie under range for rid = 2 only. Hence the output for the above query needs to be: Answer left join two tabl…