Challenging T-SQL Requirement
-
Hi,
I have a test transaction table defined as follows:
create table TRX
(
acct_no char(6),
category char(4),
trx_date datetime,
id integer identity(1, 1),
code char(1),
qty int
)
When an item is received, a transaction record is inserted for the acct_no, category and trx_date combination with an "R" in the code column along with the quantity. When an item is sold, another row is added with an "S" and the quantity sold. So, there could be a series of transaction rows for the same acct_no, category and trx_date combination each of which would be either an "R" or "S" in the type column. On a regular basis, a report table will be built by passing thru this transaction table and for each acct_no, category and trx_date combination, when an "S" row is found, each of the prior "R" rows (in acct_no, category, trx_date and id combination sequence) for that acct_no, category and trx_date combination needs to have its quantity on hand decreased from the amount of the "S" row until the quantity sold is decremented to zero. "R" rows that have been reset to zero are excluded from the output table; only rows with a positive, non-zero quantity are included. For example, if the item is hoopers and there are five "R" rows each of which has a quantity of 50 followed by an "S" row with a quantity of 175, this process will set the first three rows to zero and will re-calculate the forth row as 25 and skip the first three in the output table. Only the forth and fifth rows will be included in the output. No updates are done to the transaction table, only inserts to the report table. These are big tables so speed is important. So, can anybody offer some suggestions for the fastest and cleanest way to do this?
The following are inserts to help:
INSERT TRX values ('000101', '0100', '2003-10-1', 'B', 100)
INSERT TRX values ('000101', '0100', '2003-10-1', 'B', 150)
INSERT TRX values ('000101', '0100', '2003-10-1', 'B', 200)
INSERT TRX values ('000101', '0100', '2003-10-1', 'B', 300)
INSERT TRX values ('000101', '0100', '2003-10-1', 'S', 100)
INSERT TRX values ('000101', '0100', '2003-10-1', 'S', 200)
INSERT TRX values ('000101', '0200', '2003-10-15', 'B', 300)
INSERT TRX values ('000101', '0200', '2003-10-15', 'B', 400)
INSERT TRX values ('000101', '0200', '2003-10-15', 'B', 600)
INSERT TRX values ('000101', '0200', '2003-10-15', 'S', 200)
INSERT TRX values ('000101', '0200', '2003-10-16', 'B', 300)
INSERT TRX values ('000101', '0200', '2003-10-16', 'S', 500)
INSERT TRX values ('000101', '0200', '2003-10-17', 'B', 600)
INSERT TRX values ('000102', '0200', '2003-10-1', 'S', 200)
INSERT TRX values ('000102', '0200', '2003-10-1', 'S', 300)
INSERT TRX values ('000102', '0300', '2003-10-1', 'B', 500)
INSERT TRX values ('000102', '0300', '2003-10-1', 'B', 100)
INSERT TRX values ('000102', '0400', '2003-10-1', 'B', 200)
INSERT TRX values ('000102', '0300', '2003-10-1', 'S', 500)
Thanks.
Eaton
-
First, thank you for the table and data code...
always a pleasure to NOT have to write code just to do a test setup for somebody!I have no idea of what you consider to be a "big" table...
First, let me tell you that doing these type of reports on a "real" transactional table will pretty much freeze any GUI (or Trigger) making inputs to it when the report runs. The optimizer hint "WITH (NOLOCK)" included on the code below will help with this situation but still a "Bozo NoNo"
. You should never to any processing on a transactional table except to collect, hold, and when ready, move the transactions to a "staging" or "intermediate processing" table. Proper indexing (perhaps an index for Acct_No, Category, TRX_Date) will certainly help the situation.Ok, I'm done with that soapbox... here's one way to do it without using an infinitely slower (yeeeech) Cursor (HAAAACK... Patoooooooi!
)... look Ma, no temp table, either. Could use this in an INSERT/SELECT to populate a "report table"- SELECT
b.Acct_No,
b.Category,
b.TRX_Date,
(b.BCount - s.SCount) AS QtyOnHand
FROM
- ------- "Derived table" holds all the "B" transaction summaries
(
SELECT Acct_No, Category, TRX_Date, COUNT(Qty) AS BCount
FROM TRX WITH (NOLOCK)
WHERE Code = 'B'
GROUP BY Acct_No, Category, TRX_Date
) b
- INNER JOIN
- ------- "Derived table" holds all the "S" transaction summaries
(
SELECT Acct_No, Category, TRX_Date, COUNT(Qty) AS SCount
FROM TRX WITH (NOLOCK)
WHERE Code = 'S'
GROUP BY Acct_No, Category, TRX_Date
) s
- ------- Off course, the derived tables are joined to each other
- ON b.Acct_No = s.Acct_No
AND b.Category = s.Category
AND b.TRX_Date = s.TRX_Date
- ------- And, we have to filter out anything where "B" would be <= 0
AND b.BCount > s.SCount
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - SELECT
-
Jeff,
I appreciate the feedback. I need to provide a little more detail on the requirements. First, the following is the correct output for the above inserts:
Acct_no categ. trx_date id orig_qty qty_left
000101 0100 2003-10-01 00:00:00.000 3 200 150
000101 0100 2003-10-01 00:00:00.000 4 300 300
000101 0200 2003-10-15 00:00:00.000 9 600 600
000101 0200 2003-10-16 00:00:00.000 11 300 300
000101 0200 2003-10-17 00:00:00.000 13 600 600
000102 0300 2003-10-01 00:00:00.000 17 100 100
000102 0400 2003-10-01 00:00:00.000 18 200 200
There can be multiple transactions for each acct_no, category and trx_date and each of the surviving trx rows needs to show the detail as above, i.e., acct_no, categegory, trx_date, qty_left. Also, the order of the transactions is significant, i.e. this is basically as "first in first out" (FIFO) design so the id column should correctly order the rows within acct_no, category and trx_date.
Hope this helps.
Eaton
-
why not use this as it is and have a "balance" table, updated on a trigger every time a row is inserted into this table ?
On the trigger you'd do
if inserted.code = 'R'
update bal_tbl set stock = stock + inserted.quantity where pk = pk
else if inserted.code = 'S'
update bal_tbl set stock = stock + inserted.quantity where pk = pk
end
pk would be the account number / category etc.
-
Pls Solve my Problem iam sukhoi971, i have posted 2 messages nobody is even vieweing it..
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
I guess spamming other threads won't help you here.
Please refrain from doing so!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Sorry for the bad formatting.. But it does what it is intended to ..
---------------------------------
We sum up the qty for Code 'S' as table D.
We also make a running summary of qty for at each record, grouped by
the codes S, and B as table E.
We join these two tables, and take the difference between the
quantities in D and E.
select E.acct_no,
e.category,
e.trx_date,
e.code,
e.id,
CASE when (e.sum - dqty) < e.eqty then (e.sum - dqty)
else e.eqty
END
from
(
SELECT acct_no,
category,
sum (qty) dqty
From TRX
WHERE code = 'S'
Group By acct_no,
category
) as D right outer Join
(
-- The running summary in the subquery is joined with the base table
-- to get qty which was not part of the group by clause
SELECT s.acct_no,
s.category,
s.trx_date,
s.code,
s.qty * (CASE WHEN s.code = 'B' THEN 1 ELSE -1 END) eqty,
s.id,
c.sum FROM TRX S JOIN
(
-- Get a running summary of qty grouped by acct, category and code
SELECT b.acct_no,
b.category,
b.code bCode,
b.id,
sum (a.qty ) sum
FROMTRX A,
TRX B
WHEREA.acct_no = B.Acct_no and
A.category = B.Category and
A.Code = B.Code AND
B.ID >= A.ID
GROUP BY b.acct_no,
b.category,
b.code,
b.id
) c
on S.acct_no = c.acct_no and
s.category = c.category and
s.id = c.id
) as E
on ( d.acct_no = e.Acct_no or e.acct_no is null )and
(d.category = e.Category or e.category is null)
where ( e.sum - dqty > 0 OR dqty is NULL)
-
. . . a tough one in deed. i'm sure this could be a bit more elegant but it does work.
don't know why this post has the face icons . . . they should be ")"
SET NOCOUNT ON
-- SET UP TEST DATA
DECLARE @t1 TABLE
(
Acct_No char(6),
Category char(4),
TRX_Date datetime,
Code char(1),
Qty int,
[id] integer identity(1,1)
)
INSERT @t1 values ('000101', '0100', '2003-10-1', 'B', 100)
INSERT @t1 values ('000101', '0100', '2003-10-1', 'B', 150)
INSERT @t1 values ('000101', '0100', '2003-10-1', 'B', 200)
INSERT @t1 values ('000101', '0100', '2003-10-1', 'B', 300)
INSERT @t1 values ('000101', '0100', '2003-10-1', 'S', 100)
INSERT @t1 values ('000101', '0100', '2003-10-1', 'S', 200)
INSERT @t1 values ('000101', '0200', '2003-10-15', 'B', 300)
INSERT @t1 values ('000101', '0200', '2003-10-15', 'B', 400)
INSERT @t1 values ('000101', '0200', '2003-10-15', 'B', 600)
INSERT @t1 values ('000101', '0200', '2003-10-15', 'S', 200)
INSERT @t1 values ('000101', '0200', '2003-10-16', 'B', 300)
INSERT @t1 values ('000101', '0200', '2003-10-16', 'S', 500)
INSERT @t1 values ('000101', '0200', '2003-10-17', 'B', 600)
INSERT @t1 values ('000102', '0200', '2003-10-1', 'B', 500)-- CHANGED FROM 200
INSERT @t1 values ('000102', '0200', '2003-10-1', 'S', 300)-- BECAUSE THIS ONE IS BIGGER
INSERT @t1 values ('000102', '0300', '2003-10-1', 'B', 500)
INSERT @t1 values ('000102', '0300', '2003-10-1', 'B', 100)
INSERT @t1 values ('000102', '0400', '2003-10-1', 'B', 200)
INSERT @t1 values ('000102', '0300', '2003-10-1', 'S', 500)
SELECT *
FROM @t1
ORDER BY
Acct_No,
Category,
TRX_Date
-- START SOLUTION
SELECT R.Acct_No,
R.Category,
R.TRX_Date,
R.[id],
R.Qty,
(
SELECT CASE
WHEN R.Qty < SUM(B.Qty) - SUM(ISNULL(S.Qty,0)) THEN R.Qty
ELSE SUM(B.Qty) - SUM(ISNULL(S.Qty,0))
END
FROM @t1 B
WHERE R.Acct_No = B.Acct_No
AND R.Category = B.Category
AND R.Code = B.Code
AND R.[id] >= B.[id]
 
AS [qty_left]FROM @t1 R LEFT JOIN
@t1 S
ON R.Acct_No = S.Acct_No
AND R.Category = S.Category
AND S.Code = 'S'
GROUP BY
R.Acct_No,
R.Category,
R.TRX_Date,
R.[id],
R.Qty,
R.Code
HAVING R.Code = 'B'
AND (
SELECT CASE
WHEN R.Qty < SUM(B.Qty) - SUM(ISNULL(S.Qty,0)) THEN R.Qty
ELSE SUM(B.Qty) - SUM(ISNULL(S.Qty,0))
END
FROM @t1 B
WHERE R.Acct_No = B.Acct_No
AND R.Category = B.Category
AND R.Code = B.Code
AND R.[id] >= B.[id]
 
> 0ORDER BY
R.Acct_No,
R.Category,
R.TRX_Date
-
Thank you all for your help!
Eaton
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply