SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Logic for counting availability based to time
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jh_sql
Starting Member

Finland
24 Posts

Posted - 04/19/2013 :  08:06:55  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/19/2013 :  08:10:52  Show Profile  Reply with Quote
Can you post sample input data and expected output?
It is easy to give feasible solution to you

--
Chandu
Go to Top of Page

jh_sql
Starting Member

Finland
24 Posts

Posted - 04/19/2013 :  08:25:08  Show Profile  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/19/2013 :  08:53:25  Show Profile  Reply with Quote
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
Go to Top of Page

jh_sql
Starting Member

Finland
24 Posts

Posted - 04/19/2013 :  09:04:09  Show Profile  Reply with Quote
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'
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/19/2013 :  09:23:19  Show Profile  Reply with Quote
Means what you are trying to do?
I'm not getting your point...

May be you want Max amount?

--
Chandu
Go to Top of Page

jh_sql
Starting Member

Finland
24 Posts

Posted - 04/19/2013 :  09:34:56  Show Profile  Reply with Quote
@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.



Go to Top of Page

jh_sql
Starting Member

Finland
24 Posts

Posted - 04/22/2013 :  06:12:00  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000