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
 General SQL Server Forums
 New to SQL Server Programming
 Check sum of Qty sold against the available Qty
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SergioM
Posting Yak Master

163 Posts

Posted - 09/13/2013 :  14:07:47  Show Profile  Reply with Quote
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

Edited by - SergioM on 09/13/2013 14:09:17

erikhaselhofer
Starting Member

30 Posts

Posted - 09/13/2013 :  15:57:49  Show Profile  Reply with Quote
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

163 Posts

Posted - 09/13/2013 :  16:07:35  Show Profile  Reply with Quote
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

Edited by - SergioM on 09/13/2013 16:08:12
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 09/13/2013 :  16:55:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Belarus
172 Posts

Posted - 09/15/2013 :  19:20:21  Show Profile  Reply with Quote
More simplification!

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 09/16/2013 :  03:08:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Belarus
172 Posts

Posted - 09/16/2013 :  04:59:37  Show Profile  Reply with Quote
Why? this way is very shorter. maybe for ISO standard?

EDIT: make a mistake!

Edited by - sigmas on 09/16/2013 05:31:31
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 09/16/2013 :  06:42:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

163 Posts

Posted - 09/16/2013 :  11:48:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/16/2013 :  13:01:18  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/16/2013 :  13:03:31  Show Profile  Reply with Quote
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

163 Posts

Posted - 09/16/2013 :  13:20:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

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

SergioM
Posting Yak Master

163 Posts

Posted - 09/16/2013 :  13:59:50  Show Profile  Reply with Quote
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

163 Posts

Posted - 09/16/2013 :  14:00:19  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

406 Posts

Posted - 09/16/2013 :  23:43:54  Show Profile  Reply with Quote
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

163 Posts

Posted - 09/17/2013 :  13:08:31  Show Profile  Reply with Quote
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
  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.09 seconds. Powered By: Snitz Forums 2000