Inventory Report - 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 2008 Forums

Transact-SQL (2008)

Inventory Report

Author

Topic

manhthaodn
Starting Member

3 Posts

Posted - 2012-10-31 : 03:34:30

My database with the following table:

ITDetail Table:

InvTrans Table:

Currency Table:

Database Relationship:

I need to build a report with the time parameter (use the procedure) with the following requirements:

MaterialID, Art, Color, Width is taken from the table ITDetail
Period: 1 to 90 (days)
a. Quantity: with TypeID is 'X' is the OUT quantity (X001, X002, ...), with TypeID is 'N' is the INT quantity (N021, N014, ...)
A: Total quantity IN of 1 to 91 (days)
B: Total quantity OUT of 1 to 91 (days)
C: The quantity need to find

If: A=B then C = A – B; If A<B then C = 0

b. Price:

USD: Price = Quantity * ExchangeRate (USD in InvTrans table)
VND: Price = Quantity * 1
3. Period: After 271 days

X: Time parameter
A: Total quantity IN of X to 271 (days)
B: Total quantity OUT of X to 271 (days)
C: The quantity need to find

If: A=B then C = A – B; If A<B then C = 0

Appreciate your help! Thanks!

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-31 : 04:50:03

Can u post sample data in the form of text?

--
Chandu