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
 General SQL Server Forums
 New to SQL Server Programming
 Check sum of Qty sold against the available Qty

Author  Topic 

SergioM
Posting Yak Master

170 Posts

Posted - 2013-09-13 : 14:07:47
I'm usually *almost finished* when I ask. This time I'm just lost. I need to run a query on all of the items in the product table where the amount that I've sold over the past 2 days is greater than the amount I have available. Basically, am I likely to sell out of stock?

I have all of the information I need across 3 tables:

- bvc_product
- bvc_order
- bvc_orderitem

The database is available here: http://charliestickets.com/tmp.zip It's about 400 KB zipped & 3 MB unzipped.

I've written the following query & I can see all of the data that I need, but (even conceptually) I don't know how to make the comparison.

SELECT 
o.[ID]
,o.[TimeOfOrder]
,oi.[Qty]
,p.[ID]
,p.[AggregateQty]
FROM [tmpDB].[dbo].[bvc_Order] o
INNER JOIN tmpDB.dbo.bvc_OrderItem oi
ON o.ID = oi.OrderID
INNER JOIN tmpDB.dbo.bvc_Product p
ON p.ID = oi.ProductID


-Sergio
I use Microsoft SQL 2008

erikhaselhofer
Starting Member

30 Posts

Posted - 2013-09-13 : 15:57:49
Someone will dive on the code, it's not hard, maybe even me, eventually, but first a few questions.

When you say last 2 days I assume you mean, compared to today, 9/11 and 9/12, which would be compared to totals as of the end of day 9/12? If you run them during the day are you after everything since 9/11 or 9/12 through current, which is more like 2-1/2 days? Do you even care because it's not that material?

I'm not that comfortable with this sort of inventory approach but it would probably be fine for small fast moving items as a quick and dirty solution.
Go to Top of Page

SergioM
Posting Yak Master

170 Posts

Posted - 2013-09-13 : 16:07:35
quote:
Originally posted by erikhaselhofer
When you say last 2 days I assume you mean, compared to today, 9/11 and 9/12, which would be compared to totals as of the end of day 9/12? If you run them during the day are you after everything since 9/11 or 9/12 through current, which is more like 2-1/2 days? Do you even care because it's not that material?

I could have written that out. I just meant the past 48 hours

WHERE DATEDIFF(HOUR, o.[TimeOfOrder], ( DATEADD(Hour, -48, GETDATE()) )) < 48


This query is not for automation. As you said, it's quick & dirty. I plan on e-mailing the shipping staff daily to let them know which orders should be consolidated.

-Sergio
I use Microsoft SQL 2008
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-13 : 16:55:13
Simplify!
WHERE o.[TimeOfOrder] >= DATEADD(HOUR, -48, GETDATE())



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-15 : 19:20:21
More simplification!

WHERE o.[TimeOfOrder] >= GETDATE() - 2
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-16 : 03:08:20
Sigmas, it does work for now, but in next version of SQL Server algebra with dates will not work anymore.
The correct way is to use DATEADD.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-16 : 04:59:37
Why? this way is very shorter. maybe for ISO standard?

EDIT: make a mistake!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-16 : 06:42:14
Who knows? Dates are in reality nothing else but a "serial number", starting with zero at January 1st 1900 (SQL Server).
One reason may be that using normal arithmetic increases the ambiguity? Makes it harder for the programmers to keep SQL Server together?

See here http://www.sqltopia.com/?page_id=35

Try this code in SQL Server 2012
DECLARE	@DatePart DATETIME = '20110721 12:14:46';
SELECT CAST(@DatePart AS DATE) + 2;

[red]Msg 206, Level 16, State 2, Line 2
Operand type clash: date is incompatible with int




Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SergioM
Posting Yak Master

170 Posts

Posted - 2013-09-16 : 11:48:21
Looks like the thread has a life of it's own! :-p

Does anyone have any advice on where to start? I've outsourced this on Fiverr, but the guy is lost too. Is this the kind of thing that's too complicated to ask in the forum?

-Sergio
I use Microsoft SQL 2008
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-16 : 13:01:18
quote:
Originally posted by SergioM

Looks like the thread has a life of it's own! :-p

Does anyone have any advice on where to start? I've outsourced this on Fiverr, but the guy is lost too. Is this the kind of thing that's too complicated to ask in the forum?

-Sergio
I use Microsoft SQL 2008

People tend to not want to downlaod zip files they know nothing about.

Can you post your data in a consumable format with expected output? Here are some links to help with that:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-16 : 13:03:31
quote:
Originally posted by sigmas

Why? this way is very shorter. maybe for ISO standard?

EDIT: make a mistake!


Just because it works doens't mean you should do it. :)

Peso already outlined the major reason, it won't work in future versions of SQL. But, it's a bad habit to rely on implicit conversions.
Go to Top of Page

SergioM
Posting Yak Master

170 Posts

Posted - 2013-09-16 : 13:20:27
quote:
Originally posted by Lamprey
People tend to not want to downlaod zip files they know nothing about.

Can you post your data in a consumable format with expected output? Here are some links to help with that:

Thanks. I guess it's a rookie mistake.

To create tables within tmpDB:
http://charliestickets.com/createtmpDB.txt

Sample data for each table
http://charliestickets.com/bvc_Order.txt
http://charliestickets.com/bvc_OrderItem.txt
http://charliestickets.com/bvc_Product.txt

-Sergio
I use Microsoft SQL 2008
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-16 : 13:31:46
Unfortunately, that is not a consumable format. Also, what do you want for output?
Go to Top of Page

SergioM
Posting Yak Master

170 Posts

Posted - 2013-09-16 : 13:59:50
quote:
Originally posted by Lamprey

Unfortunately, that is not a consumable format. Also, what do you want for output?


D'oh!! Thanks for bearing with me! I want the ProductID, Amount Sold & Amount Available

ID	AmntSld AmntAvail
TestItem1 20 15


-Sergio
I use Microsoft SQL 2008
Go to Top of Page

SergioM
Posting Yak Master

170 Posts

Posted - 2013-09-16 : 14:00:19
quote:
Originally posted by SergioM

quote:
Originally posted by Lamprey

Unfortunately, that is not a consumable format. Also, what do you want for output?


D'oh!! Thanks for bearing with me! I want the ProductID, Amount Sold & Amount Available when the amount available is less than the amount sold.

ID	AmntSld AmntAvail
TestItem1 20 15


-Sergio
I use Microsoft SQL 2008



-Sergio
I use Microsoft SQL 2008
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-09-16 : 23:43:54
Maybe this:

select p.id
,sum(oi.qty) as amntsld
,p.aggregateqty as amntavail
from tmpdb.dbo.bvc_product as p
inner join tmpdb.dbo.bvc_orderitem as oi
on oi.productid=p.id
inner join tmpdb.dbo.bvc_order as o
on o.id=oi.orderid
and o.timeoforder>=dateadd(hour,-48,getdate())
group by p.id
,p.aggregateqty
having sum(oi.qty)>p.aggregateqty
Go to Top of Page

SergioM
Posting Yak Master

170 Posts

Posted - 2013-09-17 : 13:08:31
quote:
Originally posted by bitsmed

Maybe this:

select p.id
,sum(oi.qty) as amntsld
,p.aggregateqty as amntavail
from tmpdb.dbo.bvc_product as p
inner join tmpdb.dbo.bvc_orderitem as oi
on oi.productid=p.id
inner join tmpdb.dbo.bvc_order as o
on o.id=oi.orderid
and o.timeoforder>=dateadd(hour,-48,getdate())
group by p.id
,p.aggregateqty
having sum(oi.qty)>p.aggregateqty



Perfect, thanks!

Looking at this, I wonder why I couldn't outsource it. Either way, I'm grateful to the forum

-Sergio
I use Microsoft SQL 2008
Go to Top of Page
   

- Advertisement -