Getting Data between two date ranges

[raishivam46]

I am pretty new and still learning, so this might be something very basic.
I have 4 columns in a table Location, Start_time, End_time and Qty. I want to pass two datetime and want all the records(or sum of qty) that lies in between those two datetime. One way is to use filter or time dimension but this gives only those records whose start_time and end_time lies inside the dynamic date range provided. The input dynamic range might be not completely inside the start_time and end_time.

I have made an sql query but then again it requires the dynamic date range parameter as input. Also, i tried using FILTER_PARAMS, but no use,
Can anyone help me out?

[igorlukanin]

Hi @raishivam46 👋

I'm sorry but I don't fully understand what you're trying to achieve here. If you can provide an example dataset and a query (your date range in question)—and explain what kind of an output you expect in return, then I can try to give an advice here.

[raishivam46]

Sample Dataset.xlsx
Thanks for response, I have attached a sample dataset in excel.

I will try to explain my problem more clearly. This is the dataset that i have, now i want to get sum of quantity for the location 'Delhi' between '2024-09-01 14:00:00' and '2024-09-03 14:00:00'. According to dataset, the quantity which was recorded on '2024-09-02'(5th record of Delhi) will be included completely but the quantities of 1st and 3rd September to be included partially(overlapped duration multiplied by rate of quantity per hour). Overlapped duration means 20 hrs(from 4th record of Delhi) and 4 hrs (from 6th record of Delhi).
Can you help me with this?
Also, the datetime range provided for query has to be passed dynamically,
a) How can i pass that dynamically?
b) if i want to bring this dynamic datetime values from another cube. is that also possible?