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_orderitemThe 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] oINNER JOIN tmpDB.dbo.bvc_OrderItem oiON o.ID = oi.OrderIDINNER JOIN tmpDB.dbo.bvc_Product pON p.ID = oi.ProductID -SergioI 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. |
|
|
SergioM
Posting Yak Master
170 Posts |
Posted - 2013-09-13 : 16:07:35
|
quote: Originally posted by erikhaselhoferWhen 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 hoursWHERE 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.-SergioI use Microsoft SQL 2008 |
|
|
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 |
|
|
sigmas
Posting Yak Master
172 Posts |
Posted - 2013-09-15 : 19:20:21
|
More simplification!WHERE o.[TimeOfOrder] >= GETDATE() - 2 |
|
|
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 |
|
|
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! |
|
|
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=35Try this code in SQL Server 2012DECLARE @DatePart DATETIME = '20110721 12:14:46';SELECT CAST(@DatePart AS DATE) + 2;[red]Msg 206, Level 16, State 2, Line 2Operand type clash: date is incompatible with int Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
SergioM
Posting Yak Master
170 Posts |
Posted - 2013-09-16 : 11:48:21
|
Looks like the thread has a life of it's own! :-pDoes 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?-SergioI use Microsoft SQL 2008 |
|
|
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! :-pDoes 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?-SergioI 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 |
|
|
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. |
|
|
SergioM
Posting Yak Master
170 Posts |
|
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? |
|
|
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 AvailableID AmntSld AmntAvailTestItem1 20 15 -SergioI use Microsoft SQL 2008 |
|
|
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 AmntAvailTestItem1 20 15 -SergioI use Microsoft SQL 2008
-SergioI use Microsoft SQL 2008 |
|
|
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 |
|
|
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-SergioI use Microsoft SQL 2008 |
|
|
|