Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> Transact-SQL

Displaying a product ledger

Hi

I need some help about making a product ledger.

In my inventory db, I have the following tables -

Products

-

ProductID ProductName

Receives

-

ReceiveID ReceiveDate ProductID ReceiveQty

Issues

IssueID IssueDate ProductID IssueQty

I want to display a product ledger like this -

ProductName Date ReceiveQty IssueQty BalanceQty

How can I provide this functionality with a sproc ?

Regards

Kapalic

[1417 byte] By [Kapalic] at [2008-1-2]
«« Build fails but no errors are returned
»» Shell Extension Issue
# 1

Hi a couple of items:

  1. What data is held in your Date fields as these will be datetimes and so can hold times as well as dates (and so to group by day there will have to be some manipulation). Are you sure it will only be dates (i.e. midnight)
  2. Are you sure that a stored procedure is the best idea. It is much easier to intially create a view and then you can use standard SQL to query it by various criteria. You can write a stored procedure wrapper around the view if you want stored procedure access.
Dhericean at 2007-9-13 > top of Msdn Tech,SQL Server,Transact-SQL...
# 2

I have to agree that this problem is incomplete. First, we have a column "BalanceQty"; however, there is nothing else here that would constitute a balance. I have a starting point example; it is by no means an answer:

declare @product table
( ProductId integer,
ProductName varchar(20)
)
insert into @product
select 1, 'chopsticks' union all
select 2, 'knife' union all
select 3, 'spoon'

declare @receive table
( ReceiveID integer,
ReceiveDate datetime,
ProductId integer,
ReceiveQty integer
)
insert into @receive
select 1, '4/1/7', 2, 24 union all
select 2, '4/1/7', 2, 12 union all
select 3, '4/3/7', 3, 24 union all
select 4, '4/9/7', 2, 24

declare @issue table
( IssueId integer,
IssueDate datetime,
ProductID integer,
IssueQty integer
)
insert into @issue
select 1, '4/2/7', 2, 5 union all
select 2, '4/4/7', 3, 12 union all
select 3, '4/9/7', 2, 10


select productName,
[Date],
receiveQty,
issueQty,
isnull (receiveQty, 0)
- isnull(issueQty, 0)
as BalanceQty
from ( select coalesce (p.productId, q.productId) as productId,
coalesce (p.receiveDate, q.issueDate) as [Date],
sum(receiveQty) as receiveQty,
sum(issueQty) as issueQty
from @receive p
full join @issue q
on p.productId = q.productId
and p.receiveDate = q.issueDate
group by coalesce (p.productId, q.productId),
coalesce (p.receiveDate, q.issueDate)
) a
inner join @product b
on a.productId = b.productId
order by productName,
[Date]

/*

productName Date receiveQty issueQty BalanceQty
-- -- --
knife 2007-04-01 00:00:00.000 36 NULL 36
knife 2007-04-02 00:00:00.000 NULL 5 -5
knife 2007-04-09 00:00:00.000 24 10 14
spoon 2007-04-03 00:00:00.000 24 NULL 24
spoon 2007-04-04 00:00:00.000 NULL 12 -12

*/

KentWaldropAp07 at 2007-9-13 > top of Msdn Tech,SQL Server,Transact-SQL...
# 3

I hope this is what you are looking for:

Select Date, ProductID, SUM(ReceiveQty) as ReceiveQty, SUM(IssueQty) as IssueQty, SUM(ReceiveQty-IssueQty) as BalanceQty from

(

select ReceiveDate as Date, ProductID, ReceiveQty, 0 as IssueQty from Receives

union all

select IssueDate as Date, ProductID, 0 as ReceiveQty, IssueQty from Issues

) X group by ProductID, Date

TonyAbraham at 2007-9-13 > top of Msdn Tech,SQL Server,Transact-SQL...
# 4
It is not clear if the date columns have time part also. If they do then you can do something like below:
Code Snippet
select p.ProductName, coalesce(r1.Dt, i1.Dt) as Date, r1.ReceiveQty, i1.IssueQty
, coalesce(r1.ReceiveQty, 0) - coalesce(i1.IssueQty, 0) as BalanceQty
from Products as p
join ((
select r.ProductID, convert(varchar, r.ReceiveDate, 112), sum(r.ReceiveQty)
from Receives as r
group by r.ProductID, convert(varchar, r.ReceiveDate, 112)
) as r1(ProductID, Dt, ReceiveQty)
full join (
select i.ProductID, convert(varchar, i.IssueDate, 112), sum(i.IssueQty)
from Issues as i
group by i.ProductID, convert(varchar, i.IssueDate, 112)
) as i1(ProductID, Dt, IssueQty)
on r1.ProductID = i1.ProductID
)
on p.ProductID = r1.ProductID
UmachandarJayachandran-MS at 2007-9-13 > top of Msdn Tech,SQL Server,Transact-SQL...
# 5

Hi Guys,

Thank you very much for your answers. It seems like some more information about the post is needed.

My date fields is declared as datetime, but there will be no time part supply so the fields will contain a long date with time part of midnight, like 2007-04-20 00:00:00.

And actually I want to create a view and write some sprocs to wrap around it, its true!

And when I input data in the issue table, I make products could be issued if only sufficient stock of the product is available. So there could be no negative value.

Null values should be replaced with zero.

Regards

Kapalic

Kapalic at 2007-9-13 > top of Msdn Tech,SQL Server,Transact-SQL...
# 6

And about the BalanceQty, I want to display it like this -

ProductName Date ReceiveQty IssueQty BalanceQty

Knife 2007-04-11 50 0 50

Knife 2007-04-12 0 20 30

Knife 2007-04-13 10 40 0

I need all the products like this in a view, so that I could write sproc to wrap the view for different criteria.

Regards

Kapalic
Kapalic at 2007-9-13 > top of Msdn Tech,SQL Server,Transact-SQL...
SQL Server
Transact-SQL

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified