Skip to content

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 NY0001 FMC0002 2021-11-08 Null NY0002 FMC0003 2021-11-02 2021-11-02 NY0002 FMC0004 2021-09-01 2021-10-10 2) Store Table : Store ID

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 want to check for all

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 create arrays and then

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 tables with