Aggregate sum needed by part number, not lump sum

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)

Aggregate sum needed by part number, not lump sum

Author

Topic

actsql
Starting Member

34 Posts

Posted - 2010-04-13 : 22:43:28

I am new to SQL and formerly used Access, depending heavily on the graphical tools to write queries. I also used the feature where I could graphically link to a query (not just a table). I am writing SQL now but having a lot of trouble doing subqueries.

I need each column in the attached query to be summarized by item number (column one). TTQTY, however, is a lump sum total for all quantities, not just by item number.

Here is the code I am using:

SELECT DISTINCT
dbo.items.item_id,

ISNULL((SELECT DISTINCT sum(dbo.inventory_receipts_d.received_quantity)
FROM dbo.inventory_receipts_d
WHERE dbo.inventory_receipts_d.item_id = dbo.items.item_id),0) AS RecvQuant,

ISNULL((SELECT DISTINCT sum(ISNULL(dbo.inventory_receipts_d.received_quantity,0))
FROM dbo.inventory_receipts_d
WHERE dbo.inventory_receipts_d.posted_flag = 'N'
AND dbo.inventory_receipts_d.item_id = dbo.items.item_id),0) AS PndRcpt,

ISNULL((SELECT DISTINCT

SUM
(ISNULL
(CASE
WHEN dbo.inventory_activity_transfer_d.transfer_type = 'F'
AND dbo.inventory_activity_transfer_d.move_from_to='W'
AND dbo.inventory_activity_transfer_d.inv_rcpt_trx_id IS NULL
AND dbo.inventory_activity_d.item_id = dbo.items.item_id
THEN (-1)*isnull(dbo.inventory_activity_transfer_d.stocking_quantity,0)
WHEN dbo.inventory_activity_transfer_d.transfer_type = 'T'
AND dbo.inventory_activity_transfer_d.move_from_to='W'
AND dbo.inventory_activity_transfer_d.inv_rcpt_trx_id IS NULL
AND dbo.inventory_activity_d.item_id = dbo.items.item_id
THEN (1)*isnull(dbo.inventory_activity_transfer_d.stocking_quantity,0)
END,0)
)

FROM
dbo.items
JOIN dbo.inventory_activity_d ON dbo.items.item_id = dbo.inventory_activity_d.item_id
JOIN dbo.inventory_activity_transfer_d ON dbo.inventory_activity_d.transaction_no = dbo.inventory_activity_transfer_d.transaction_no
AND dbo.inventory_activity_d.line_no = dbo.inventory_activity_transfer_d.line_no
WHERE
dbo.items.primary_item_category_no<>'NONIN'

),0) AS TTQTY,

ISNULL((SELECT DISTINCT sum(ISNULL(dbo.po_sub_item_d.stocking_quantity,0))
FROM dbo.po_sub_item_d
WHERE dbo.po_sub_item_d.order_completion_date IS NULL
AND dbo.po_sub_item_d.item_id = dbo.items.item_id),0) AS OpenPO

FROM
dbo.items
LEFT JOIN dbo.inventory_receipts_d ON dbo.items.item_id = dbo.inventory_receipts_d.item_id
LEFT JOIN dbo.po_sub_item_d ON dbo.items.item_id = dbo.po_sub_item_d.item_id
LEFT JOIN dbo.po_sub_change_item_d ON dbo.items.item_id = dbo.po_sub_change_item_d.item_id
AND dbo.po_sub_change_item_d.po_sub_no = dbo.po_sub_item_d.po_sub_no
LEFT JOIN dbo.inventory_activity_d ON dbo.items.item_id = dbo.inventory_activity_d.item_id
LEFT JOIN dbo.inventory_activity_transfer_d ON dbo.inventory_activity_d.transaction_no = dbo.inventory_activity_transfer_d.transaction_no
AND dbo.inventory_activity_d.line_no = dbo.inventory_activity_transfer_d.line_no

WHERE
dbo.items.record_status<>'D'
AND dbo.items.primary_item_category_no<>'NONIN'

GROUP BY
dbo.items.item_id,dbo.items.reorder_point,dbo.items.max_stocking_qty,dbo.inventory_receipts_d.item_id, dbo.inventory_activity_transfer_d.transaction_no, dbo.inventory_activity_transfer_d.line_no,dbo.inventory_activity_transfer_d.transfer_type,dbo.inventory_activity_transfer_d.stocking_quantity,dbo.inventory_receipts_d.posted_flag,/*dbo.po_sub_item_d.order_completion_date,*/dbo.inventory_activity_transfer_d.move_from_to,dbo.po_sub_item_d.po_sub_no,dbo.po_sub_item_d.order_completion_date,dbo.inventory_receipts_d.item_id,dbo.inventory_activity_d.transaction_no,dbo.inventory_activity_d.line_no,dbo.inventory_activity_d.item_id,dbo.inventory_activity_transfer_d.inv_rcpt_trx_id

ORDER BY
dbo.items.item_id;

The output from this query is:

item_id RecvQuant PndRcpt TTQTY OpenPO
1 40.0000000 0.0000000 -816696.0000000 10.0000000
10 0.0000000 0.0000000 -816696.0000000 0.0000000
100 10000.0000000 0.0000000 -816696.0000000 1000.0000000
10007 857.0000000 0.0000000 -816696.0000000 0.0000000
10008 57.0000000 0.0000000 -816696.0000000 0.0000000
10009 1185.0000000 0.0000000 -816696.0000000 0.0000000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-13 : 23:57:42

[code]
SELECT DISTINCT
t.item_id,

ISNULL((SELECT DISTINCT sum(dbo.inventory_receipts_d.received_quantity)
FROM dbo.inventory_receipts_d
WHERE dbo.inventory_receipts_d.item_id = t.item_id),0) AS RecvQuant,

ISNULL((SELECT DISTINCT sum(ISNULL(dbo.inventory_receipts_d.received_quantity,0))
FROM dbo.inventory_receipts_d
WHERE dbo.inventory_receipts_d.posted_flag = 'N'
AND dbo.inventory_receipts_d.item_id = t.item_id),0) AS PndRcpt,

ISNULL((SELECT DISTINCT

SUM
(ISNULL
(CASE
WHEN dbo.inventory_activity_transfer_d.transfer_type = 'F'
AND dbo.inventory_activity_transfer_d.move_from_to='W'
AND dbo.inventory_activity_transfer_d.inv_rcpt_trx_id IS NULL
AND dbo.inventory_activity_d.item_id = dbo.items.item_id
THEN (-1)*isnull(dbo.inventory_activity_transfer_d.stocking_quantity,0)
WHEN dbo.inventory_activity_transfer_d.transfer_type = 'T'
AND dbo.inventory_activity_transfer_d.move_from_to='W'
AND dbo.inventory_activity_transfer_d.inv_rcpt_trx_id IS NULL
AND dbo.inventory_activity_d.item_id = dbo.items.item_id
THEN (1)*isnull(dbo.inventory_activity_transfer_d.stocking_quantity,0)
END,0)
)

FROM
dbo.items
JOIN dbo.inventory_activity_d ON dbo.items.item_id = dbo.inventory_activity_d.item_id
JOIN dbo.inventory_activity_transfer_d ON dbo.inventory_activity_d.transaction_no = dbo.inventory_activity_transfer_d.transaction_no
AND dbo.inventory_activity_d.line_no = dbo.inventory_activity_transfer_d.line_no
WHERE
dbo.items.primary_item_category_no<>'NONIN'
AND dbo.items.item_id =t.item_id

),0) AS TTQTY,

ISNULL((SELECT DISTINCT sum(ISNULL(dbo.po_sub_item_d.stocking_quantity,0))
FROM dbo.po_sub_item_d
WHERE dbo.po_sub_item_d.order_completion_date IS NULL
AND dbo.po_sub_item_d.item_id = t.item_id),0) AS OpenPO

FROM
dbo.items t
LEFT JOIN dbo.inventory_receipts_d ON t.item_id = dbo.inventory_receipts_d.item_id
LEFT JOIN dbo.po_sub_item_d ON t.item_id = dbo.po_sub_item_d.item_id
LEFT JOIN dbo.po_sub_change_item_d ON t.item_id = dbo.po_sub_change_item_d.item_id
AND dbo.po_sub_change_item_d.po_sub_no = dbo.po_sub_item_d.po_sub_no
LEFT JOIN dbo.inventory_activity_d ON t.item_id = dbo.inventory_activity_d.item_id
LEFT JOIN dbo.inventory_activity_transfer_d ON dbo.inventory_activity_d.transaction_no = dbo.inventory_activity_transfer_d.transaction_no
AND dbo.inventory_activity_d.line_no = dbo.inventory_activity_transfer_d.line_no

WHERE
t.record_status<>'D'
AND t.primary_item_category_no<>'NONIN'

GROUP BY
t.item_id,t.reorder_point,t.max_stocking_qty,dbo.inventory_receipts_d.item_id, dbo.inventory_activity_transfer_d.transaction_no, dbo.inventory_activity_transfer_d.line_no,dbo.inventory_activity_transfer_d.transfer_type,dbo.inventory_activity_transfer_d.stocking_quantity,dbo.inventory_receipts_d.posted_flag,/*dbo.po_sub_item_d.order_completion_date,*/dbo.inventory_activity_transfer_d.move_from_to,dbo.po_sub_item_d.po_sub_no,dbo.po_sub_item_d.order_completion_date,dbo.inventory_receipts_d.item_id,dbo.inventory_activity_d.transaction_no,dbo.inventory_activity_d.line_no,dbo.inventory_activity_d.item_id,dbo.inventory_activity_transfer_d.inv_rcpt_trx_id

ORDER BY
t.item_id;
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

actsql
Starting Member

34 Posts

Posted - 2010-04-15 : 09:13:41

Thank You! That corrected the issue. Very much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources