inventory reporting - SQL Server Forums

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)

inventory reporting

Author

Topic

arrchurro
Starting Member

4 Posts

Posted - 2011-05-04 : 19:03:44

Hi Everyone, I'm pretty new to SQL world. I know just the basics, select, Insert, Update.

I'm trying to create a report to estimate my inventory at each store.
We sell calling cards, so Product Batch is the numeric identifier for that Calling Card, the Sequence is the sequence number for the card that was printed. The combination of the 2 "Batch_Seq" is unique.

I have 4 tables.
Table - Purchases
Store ID
Product Batch
Product Sequence
Product ID ("Batch_Seq")
Purchase Date

Table - Returns
Store ID
Product Batch
Product Sequence
Produc ID ("Batch_Seq")
Return Date

Table - Usage
Product Batch
Product Sequence
Product ID ("Batch_Seq")
Usage Date

Table - StockTake
Store ID
Product Batch
QTY
Date

What I want to get is one result that shows
StoreID/Product Batch/QTY STOCKTAKE/QTY Purchased/QTY Returned/QTY used

as of the last stocktake date.

Please let me know if anyone can help me out. my combinations of joins is too confusing with all 4 tables. I'm resorting to using excel vlookups each time.

Thanks!

Arthur Lopez

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-04 : 21:59:38

The first columns that you need - StoreID/Product Batch/QTY STOCKTAKE - are all in Table - StockTake. So, that is easy.

The fourth column - QTY Purchased - presumably is in Table - Purchases. But, I don't see any column there that looks like a quantity. From that table, how do you determine how much was purchased? If you can figure that out, then it is a matter of joining the two tables to get the fourth column.

The fifth and sixth columns (QTY Returned/QTY used) can similarly be found by joining the Returns and Usage tables to the query - but again, in those two tables, I don't see anything that looks like a quantity.

Are there more columns in the Purchases, Returns and Usage tables?

arrchurro
Starting Member

4 Posts

Posted - 2011-05-05 : 18:28:24

Hi! The quantity for the Purchased/Returned/Used are calculated by count() of the number of products within that batch and with that store id.

arrchurro
Starting Member

4 Posts

Posted - 2011-05-05 : 19:00:20

so my problem right now is when i'm trying to join just 2 tables, the first column comes out okay but the qty in the 2nd is either coming out 0 or is coming out with numbers greater than they should be.

select a.store_id, a.batch, c.description, count(a.batch_seq) as purchases, count(b.batch_seq) as usage
from LS_purchases a
left join LS_Usage b
on a.batch_seq = b.batch_seq
left join ls_products c
on a.batch = c.batch
group by a.store_id, a.batch, c.description
order by a.store_id, a.batch, c.description

is getting this result:
Store_ID Batch Description Purchases Usage
1001 1294 Idolian Secreto $3 20 0
1001 1295 Idolian Secreto $5 10 0
1001 382501 Idolian Extranos $2 20 0
1001 382601 Idolian Extranos $5 10 0

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-05 : 21:13:07

One of those four tables would have all the Product Batches, wouldn't it? You need to start with that table and then left join on the other tables. Perhaps Stocktake is that table? If so, then


select
   st.[Store ID],
   st.[Product Batch],
   st.sum(QTY) as QtyStocktake,
   count(pu.[Product Batch]) as QtyPurchased -- see note 1 below
from
   Stocktake st
   left join Purchases pu 
      on pu.StoreId = st.StoreId and pu.[Product Batch] = st.[Product Batch] -- see note 2 below
group by
   st.[Store ID],
   st.[Product Batch]

Note 1: I am guessing as to what you need to count. It may be something else whose count indicates the purchase quantity.

Note 2: I am guessing as to what columns to join on. The two columns I used seemed reasonable.

Run it and see if the results are anywhere close to what you are looking for. If not, post the table DDLs and some sample data along with the expected output. If you can follow the examples/guidelines on Brett's page (http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx), that would make it easy for people on the forum to understand the requirements and propose a solution.

arrchurro
Starting Member

4 Posts

Posted - 2011-05-06 : 13:21:39

Thanks sunitabeck,

This is working well. Just had to clean up the arguements and add some more constraints to the joins.

Thank you for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources