Logic for counting availability based to time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Logic for counting availability based to time

Author

Topic

jh_sql
Starting Member

24 Posts

Posted - 2013-04-19 : 08:06:55

Hello

I have problem when counting product availabilities based to time,
for example i have amount of 2 total. And i have order where product with amount of 1 is used for example 10:30 - 11:30 and 12:00 - 12:30. And now i face situation where i need to check if product is available 10:00-13:00.

Any ideas how i could do this sql way, or am i stuck doing it with code?

Product table has plain amount, and usage is counted based on orders product table.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-19 : 08:10:52

Can you post sample input data and expected output?
It is easy to give feasible solution to you

--
Chandu

jh_sql
Starting Member

24 Posts

Posted - 2013-04-19 : 08:25:08

Thanks for quick reply and its basicly:

Order product:

order_id | amount | product_id | start_time | end_time
1 | 1 | 1 | 1.1.2013 10:30 | 1.1.2013 11:30
1 | 1 | 1 | 1.1.2013 12:00 | 1.1.2013 12:30

product:
id | total_amount | name
1 | 2 | sample

So the problem is if ill do
SELECT sum(amount) as total FROM order_product WHERE start_time BETWEEN '1.1.2013 10:00' AND '1.1.2013 13:00'

Ill get result of 2 ofcourse, and i'd like to somehow get the result as 1 as those times arent overlapping, and there is really 1 product left.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-19 : 08:53:25

DECLARE @Order TABLE (order_id INT, amount INT, product_id INT, start_time datetime, end_time datetime)
insert into @Order
SELECT 1, 1, 1, '1/1/2013 10:30', '1/1/2013 11:30' union all
SELECT 1, 1, 1, '1/1/2013 12:00', '1/1/2013 12:30'

DECLARE @product TABLE(id INT, total_amount INT, name VARCHAR(20))
INSERT INTO @product VALUES(1, 2, 'sample')

--situation where i need to check if product is available 10:00-13:00. (Your First Post)
Means you want to get the count of products that are available during the given time duration ?
SELECT COUNT(distinct product_id ) as total
FROM @Order WHERE start_time >= '1/1/2013 10:00' AND end_time <= '1.1.2013 13:00'

--
Chandu

jh_sql
Starting Member

24 Posts

Posted - 2013-04-19 : 09:04:09

Yes, thats what im trying to figure out, thanks for the SQL aswell :)

However, if ill change the amount to 2 the result in this case should be 2 aswell (as 10:30-11:30 2 is used):

DECLARE @Order TABLE (order_id INT, amount INT, product_id INT, start_time datetime, end_time datetime)
insert into @Order
SELECT 1, 2, 1, '1/1/2013 10:30', '1/1/2013 11:30' union all
SELECT 1, 1, 1, '1/1/2013 12:00', '1/1/2013 12:30'

SELECT COUNT(distinct product_id ) as total
FROM @Order WHERE start_time >= '1/1/2013 10:00' AND end_time <= '1.1.2013 13:00'

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-19 : 09:23:19

Means what you are trying to do?
I'm not getting your point...

May be you want Max amount?

--
Chandu

jh_sql
Starting Member

24 Posts

Posted - 2013-04-19 : 09:34:56

@order table would contain when product is used, and how many is used. And i need to know if there is still availability left for given time period.

So 10:30-11:30 there is 2 products in use (amount=2) and when i check the availability 10:00-13:00 total usage is 2, but the second row in table gives also 1 amount for the time period eaven tho they arent used at same time, but are anyways between the given time period as parameter ( WHERE start_time >= '1/1/2013 10:00' AND end_time <= '1.1.2013 13:00').

And it gets wery tricky when there is overlapping times, for example 1 product would be used 10:00-11:00 and one 10:30-11:30.

jh_sql
Starting Member

24 Posts

Posted - 2013-04-22 : 06:12:00

Only thing i can think of this is to create temporary table where i insert dates with tiny interval, lets say 15 minutes periods. And to each 15 minute period i'll count sum of amount in @order table. And finally i'll do another select from the new temp table where i sum the amount, and if its over 2 in any 15 minute period, then the product is not available. Feels extremely heavy for the database tho.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources