November 21, 2008 at 6:20 am
Hi I am trying to generate a Stock Ledger Report. I am wondering my requirement can be met without a cursor.
Table structure
Item Master
------------
ItemID BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
ItemName VARCHAR(255),
QOH NUMERIC(18,2), -- Quantity on Hand
ROL NUMERIC(18,2), -- RE-ORDER LEVEL
DATECREATED DATETIME NOT NULL DEFAULT(GETDATE())
Item Issue Master
IssueID BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
ItemID BIGINT NOT NULL, -- Reference to Item Master table with non clustered Index
IssueQty NUMERIC(18,2),
IssueDate DATETIME,
Description VARCHAR(255)
Item Receipt Master
ReceiptID BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
ItemID BIGINT NOT NULL, -- Reference to Item Master table with non clustered index
ReceiptQty NUMERIC(1,2)
ReceiptDate DATETIME,
Description VARCHAR(255)
Now I want to Generate a Report that has the following details
ItemID | Item Name | Date | Description | Receipt Qty | Issue Qty | Stock On Hand
1 | Pencils | 01-01-2008| Opening Stock| 50.00 | 00.00 | 50.00
1 | Pencils | 01-02-2008| Issue to XYZ | 00.00 | 5.00 | 45.00
1 | Pencils | 01-02-2008| Issue to ABC | 00.00 | 15.00 | 30.00
1 | Pencils | 01-04-2008| Receipt | 25.00 | 00.00 | 55.00
I could get the stock on hand using a cursor by looping through and adding/deducting the quantiy, I believe this should be possible using a straight query any help would appreciated.
Thanks
November 21, 2008 at 7:43 am
Please refer to the link in my signature for an example of how to post table DDL / Sample Data here. Once we have that, we can get you a tested solution to this issue.
November 21, 2008 at 8:28 am
Ravi (11/21/2008)
Table structureItem Master
------------
ItemID BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
ItemName VARCHAR(255),
QOH NUMERIC(18,2), -- Quantity on Hand
ROL NUMERIC(18,2), -- RE-ORDER LEVEL
DATECREATED DATETIME NOT NULL DEFAULT(GETDATE())
Item Issue Master
IssueID BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
ItemID BIGINT NOT NULL, -- Reference to Item Master table with non clustered Index
IssueQty NUMERIC(18,2),
IssueDate DATETIME,
Description VARCHAR(255)
Item Receipt Master
ReceiptID BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
ItemID BIGINT NOT NULL, -- Reference to Item Master table with non clustered index
ReceiptQty NUMERIC(1,2)
ReceiptDate DATETIME,
Description VARCHAR(255)
I have a couple of questions and statements:
1) is the ItemMaster QOH column updated real time for each issue and receipt transaction? If so then you will need to determine some sort of starting point for your quantity on hand in the report (even if that is the beginning of time). If it's only updated on periodic basis, then that is your starting point for quantity on hand, and you'll need to know what that date was so you know what transactions to include.
2) calculating the running total for quantity on hand will be difficult if not impossible to do without a cursor, and may be better done in the reporting tool or application you're presenting this data in.
November 21, 2008 at 9:02 am
Ravi,
I actually want to point you to both articles listed below in my signature block. The first, which you already have been referred to, will help you get better responses to your requests for help.
The second, running totals, will help you with your current problem.
Please read them both, then get back to us if you still have questions.
November 21, 2008 at 10:21 am
This is one of those situations where using a cursor is the more natural approach and will probably be more efficient than any possible single query approach.
The following query that uses a correlated subquery nearly does what you are asking for, but there is a problem if any rows in the [Item Issue Master].IssueDate field and [Item Receipt Master].ReceiptDate field have identical datetime values for the same ItemId.
SELECT [ItemId] = M.ItemId,
[Item Name] = M.ItemName,
[Date] = RM.ReceiptDate,
[Description] = RM.Description,
[Receipt Qty] = RM.ReceiptQty,
[Issue Qty] = 0.00,
[Stock On Hand] = COALESCE(M.QOH, 0.00)
+ COALESCE((SELECT SUM(R.ReceiptQty)
FROM [Item Receipt Master] R
WHERE (R.ItemId = RM.ItemId)
AND (R.ReceiptDate <= RM.ReceiptDate)), 0.00)
- COALESCE((SELECT SUM(I.IssueQty)
FROM [Item Issue Master] I
WHERE (I.ItemId = RM.ItemId)
AND (I.IssueDate <= RM.ReceiptDate)), 0.00)
FROM [Item Receipt Master] RM
INNER JOIN [Item Master] M ON (RM.ItemId = M.ItemId)
UNION ALL
SELECT [ItemId] = M.ItemId,
[Item Name] = M.ItemName,
[Date] = IM.IssueDate,
[Description] = IM.Description,
[Receipt Qty] = 0.00,
[Issue Qty] = IM.IssueQty,
[Stock On Hand] = COALESCE(M.QOH, 0.00)
+ COALESCE((SELECT SUM(R.ReceiptQty)
FROM [Item Receipt Master] R
WHERE (R.ItemId = IM.ItemId)
AND (R.ReceiptDate <= IM.IssueDate)), 0.00)
- COALESCE((SELECT SUM(I.IssueQty)
FROM [Item Issue Master] I
WHERE (I.ItemId = IM.ItemId)
AND (I.IssueDate <= IM.IssueDate)), 0.00)
FROM [Item Issue Master] IM
INNER JOIN [Item Master] M ON (IM.ItemId = M.ItemId)
ORDER BY [ItemId], [Date]
The following amended query does better by using the primary key IDENTITY fields to unambiguously order rows if they are from the same table, and (arbitrarily) ordering rows from the [Item Receipt Master] table before rows from the [Item Issue Master] table for the same ItemId and Date. However, this approach assumes that the IDENTITY field values will always increase with time
SELECT [ItemId] = M.ItemId,
[Item Name] = M.ItemName,
[Date] = RM.ReceiptDate,
[Description] = RM.Description,
[Receipt Qty] = RM.ReceiptQty,
[Issue Qty] = 0.00,
[Stock On Hand] = COALESCE(M.QOH, 0.00)
+ COALESCE((SELECT SUM(R.ReceiptQty)
FROM [Item Receipt Master] R
WHERE (R.ItemId = RM.ItemId)
AND (R.ReceiptId <= RM.ReceiptId)), 0.00)
- COALESCE((SELECT SUM(I.IssueQty)
FROM [Item Issue Master] I
WHERE (I.ItemId = RM.ItemId)
AND (I.IssueDate <= RM.ReceiptDate)), 0.00),
[ReceiptId] = RM.ReceiptId,
[IssueId] = NULL
FROM [Item Receipt Master] RM
INNER JOIN [Item Master] M ON (RM.ItemId = M.ItemId)
UNION ALL
SELECT [ItemId] = M.ItemId,
[Item Name] = M.ItemName,
[Date] = IM.IssueDate,
[Description] = IM.Description,
[Receipt Qty] = 0.00,
[Issue Qty] = IM.IssueQty,
[Stock On Hand] = COALESCE(M.QOH, 0.00)
+ COALESCE((SELECT SUM(R.ReceiptQty)
FROM [Item Receipt Master] R
WHERE (R.ItemId = IM.ItemId)
AND (R.ReceiptDate < IM.IssueDate)), 0.00)
- COALESCE((SELECT SUM(I.IssueQty)
FROM [Item Issue Master] I
WHERE (I.ItemId = IM.ItemId)
AND (I.IssueId <= IM.IssueId)), 0.00),
[ReceiptId] = NULL,
[IssueId] = IM.IssueId
FROM [Item Issue Master] IM
INNER JOIN [Item Master] M ON (IM.ItemId = M.ItemId)
ORDER BY [ItemId], [Date], [ReceiptId], [IssueId]
Andrew Smith
November 21, 2008 at 10:23 am
andrewd.smith (11/21/2008)
This is one of those situations where using a cursor is the more natural approach and will probably be more efficient than any possible single query approach.Andrew Smith
Not true! Read the article about running totals in my signature block.
November 21, 2008 at 10:24 am
A temp table and the running totals method should handle this with no need for a cursor or a correlated subquery. You'll also get the additional benefit of being able to order by whatever you want for your final output.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply