Warehouse query - 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 2005 Forums

Transact-SQL (2005)

Warehouse query

Author

Topic

mapidea
Posting Yak Master

124 Posts

Posted - 2010-01-27 : 14:42:53

I have a query.

It has to return All the warehouse name from table [Warehouses].
It returns the on_hand value from the Product_Warehouse table.

Then there is table Order_Items_Shipping_Comments which maintains the allocations.

It is not returning all the warehouse name.

SELECT
w.[warehouse_type_id],
w.[name],
CONVERT(DECIMAL(10,0), ISNULL(pw.on_hand,0)) AS on_hand,
CONVERT(DECIMAL(10,0), (ISNULL(SUM(pw.on_hand),0) - ISNULL(SUM(ioisc.allocated),0))) AS availableTobeAllocated
FROM
[Warehouses] AS w
LEFT JOIN Product_Warehouse AS pw ON pw.warehouse_type_id = w.warehouse_type_id

LEFT JOIN
(SELECT warehouse_id, ISNULL(SUM(quantity),0) as allocated
FROM Order_Items_Shipping_Comments sc
WHERE sc.product_id=@product_id
Group By warehouse_id
) ioisc ON ioisc.warehouse_id = pw.warehouse_type_id

WHERE pw.product_id = @product_id
GROUP BY
w.[warehouse_type_id],
w.[name],
pw.on_hand
ORDER BY on_hand DESC

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-27 : 14:51:05

SELECT
w.[warehouse_type_id],
wn.[name],
CONVERT(DECIMAL(10,0), ISNULL(pw.on_hand,0)) AS on_hand,
CONVERT(DECIMAL(10,0), (ISNULL(SUM(pw.on_hand),0) - ISNULL(SUM(ioisc.allocated),0))) AS availableTobeAllocated
FROM
( Select Disinct name from [Warehouses]) wn
Left join [Warehouses] w on wn.Name = w.Name
LEFT JOIN Product_Warehouse AS pw ON pw.warehouse_type_id = w.warehouse_type_id

LEFT JOIN
(SELECT warehouse_id, ISNULL(SUM(quantity),0) as allocated
FROM Order_Items_Shipping_Comments sc
WHERE sc.product_id=@product_id
Group By warehouse_id
) ioisc ON ioisc.warehouse_id = pw.warehouse_type_id

WHERE pw.product_id = @product_id
GROUP BY
w.[warehouse_type_id],
w.[name],
pw.on_hand
ORDER BY on_hand DESC

Should be ok.

mapidea
Posting Yak Master

124 Posts

Posted - 2010-01-27 : 15:05:14

Thanks for your reply.

That didn't quite work

if I comment the WHERE pw.product_id = @product_id it returns all the records.

SELECT
w.[warehouse_type_id],
w.[name],
CONVERT(DECIMAL(10,0), ISNULL(pw.on_hand,0)) AS on_hand,
CONVERT(DECIMAL(10,0), (ISNULL(pw.on_hand,0) - ISNULL(ioisc.allocated,0))) AS availableTobeAllocated
FROM
[Warehouses] AS w
LEFT JOIN Product_Warehouse AS pw ON pw.warehouse_type_id = w.warehouse_type_id

LEFT JOIN
(SELECT warehouse_id, ISNULL(SUM(quantity),0) as allocated
FROM Order_Items_Shipping_Comments sc
WHERE sc.product_id=@product_id
Group By warehouse_id
) ioisc ON ioisc.warehouse_id = pw.warehouse_type_id

--WHERE pw.product_id = @product_id
GROUP BY
w.[warehouse_type_id],
w.[name],
pw.on_hand,
ioisc.allocated
ORDER BY on_hand DESC

Returns

-2 Missing Inventory 16 16
-1 Default 16 16
213 Pending Returns 16 16
218 DENVER 16 16
222 MANKATO 16 16
220 KANSAS CITY 16 16
221 KINGMAN 16 16
214 INDURFA 16 16
216 CLEVELAND-WESTLAKE 16 16
-1 Default 15 15
217 CORSICANA 15 15
-1 Default 9 9

But if I uncomment it returns

warehouse_type_id name on_hand availableTobeAllocated
----------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- ---------------------------------------
-1 Default 16 16

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-27 : 15:08:28

Right, because your first table is the one you are getting the name from, when you put the where clause it limits the roms in that initial table.

Let me look at my code and see if I can adjust

mapidea
Posting Yak Master

124 Posts

Posted - 2010-01-27 : 15:15:17

This seems to work........ Thanks

SELECT DISTINCT
w.[warehouse_type_id],
w.[name],
CONVERT(DECIMAL(10,0), ISNULL(pw.on_hand,0)) AS on_hand,
CONVERT(DECIMAL(10,0), (ISNULL(pw.on_hand,0) - ISNULL(ioisc.allocated,0))) AS availableTobeAllocated
FROM
[Warehouses] AS w
LEFT JOIN Product_Warehouse AS pw ON pw.warehouse_type_id = w.warehouse_type_id AND pw.product_id = @product_id

LEFT JOIN
(SELECT warehouse_id, ISNULL(SUM(quantity),0) as allocated
FROM Order_Items_Shipping_Comments sc
WHERE sc.product_id=@product_id
Group By warehouse_id
) ioisc ON ioisc.warehouse_id = pw.warehouse_type_id

ORDER BY on_hand DESC

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-27 : 15:24:05

Hmm try and implement something like this, someone else may have some more hints since I dont want you to get back wrong results

Declare @t1 table ( id int , name varchar(20))
Insert @t1
Select 1, 'A' UNION ALL
Select 2, 'B' UNION ALL
Select 3, 'C'

Declare @t2 table ( id int , numvalue int )
Insert @t2
Select 1, 60 UNION ALL
Select 1, 5 UNION ALL
Select 3, 40

Select a.name , sum(b.numvalue) as mySum from @t1 a FULL OUTER join @t2 b on a.id = b.id group by a.name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources