March 27, 2012 at 9:06 am
Hi
Need help again with one query that i'm trying to build.
The business process consists in several lists of items for the warehouse to delivery to the factory.
So i have a master table where i save the lists, a detail table for this master table where i save the items that exist on each list and the quantities. Then i have another two tables, one that saves the deliveries done by the users and is related to the master table (the same list can have several movements), and the other one that it's the details of the movement, and for each movement i have a row for each item in the master details table that indicates the quantity of each item that was delivered.
What i need, i need to see, at some point, to know for each list how many items are missing for delivery.
The relationship it's complicated....
Master 1 - X Details , Mov 1 - X MovDetails , Master 1 - X Mov, Details 1 - X Mov Details
Sample Code:
IF OBJECT_ID('TempDB..#myMaster','U') IS NOT NULL
DROP TABLE #myMaster
CREATE TABLE #myMaster
(
ID INT PRIMARY KEY CLUSTERED,
Def NVARCHAR(150),
)
IF OBJECT_ID('TempDB..#myMasterDetails','U') IS NOT NULL
DROP TABLE #myMasterDetails
CREATE TABLE #myMasterDetails
(
ID INT PRIMARY KEY CLUSTERED,
tblMASTER INT NOT NULL,
Reference NVARCHAR(250),
Quantity INT
)
IF OBJECT_ID('TempDB..#myMov','U') IS NOT NULL
DROP TABLE #myMov
CREATE TABLE #myMov
(
ID INT PRIMARY KEY CLUSTERED,
IDMaster INT,
MovDate SMALLDATETIME
)
IF OBJECT_ID('TempDB..#myMovDetails','U') IS NOT NULL
DROP TABLE #myMovDetails
CREATE TABLE #myMovDetails
(
ID INT PRIMARY KEY CLUSTERED,
IDMov INT,
IDMasterDetail INT,
QuantityDelv INT
)
INSERT INTO #myMaster ( ID, Def )
SELECT 1,'List1' UNION ALL
SELECT 2,'List2' UNION ALL
SELECT 3,'List3'
INSERT INTO #myMasterDetails ( ID, tblMASTER, Reference, Quantity )
SELECT 1,1,'Item1 List1',10 UNION ALL
SELECT 2,1,'Item2 List1',20 UNION ALL
SELECT 3,1,'Item3 List1',30 UNION ALL
SELECT 4,1,'Item4 List1',40 UNION ALL
SELECT 5,2,'ItemA List2',100 UNION ALL
SELECT 6,2,'ItemB List2',101 UNION ALL
SELECT 7,2,'ItemC List2',102 UNION ALL
SELECT 8,2,'ItemD List2',103 UNION ALL
SELECT 9,3,'Item$ List3',999
INSERT INTO #myMov ( ID, IDMaster, MovDate )
SELECT 1,1,GETDATE() UNION ALL
SELECT 2,1,DATEADD(Day,1,GETDATE()) UNION ALL
SELECT 3,2,DATEADD(Day,2,GETDATE())
-- For each Movement i need to add movements for each item in the list
INSERT INTO #myMovDetails ( ID,IDMov, IDMasterDetail, QuantityDelv )
SELECT 1,1,1,1 UNION ALL
SELECT 2,1,2,2 UNION ALL
SELECT 3,1,3,3 UNION ALL
SELECT 4,1,4,4 UNION ALL
SELECT 5,2,1,10 UNION ALL
SELECT 6,2,2,20 UNION ALL
SELECT 7,2,3,30 UNION ALL
SELECT 8,2,4,40 UNION ALL
SELECT 9,3,5,10 UNION ALL
SELECT 10,3,6,11 UNION ALL
SELECT 11,3,7,12 UNION ALL
SELECT 12,3,8,13
Thanks
March 27, 2012 at 9:15 am
I think that i get what i need...
SELECT DISTINCT
MA.Def,
MAD.Reference,
MAD.Quantity,
[Movs] = COUNT(MO.ID),
[Deliver] = SUM(MD.QuantityDelv),
[Remain] = MAD.Quantity -SUM(MD.QuantityDelv)
FROM #myMaster MA
INNER JOIN #myMasterDetails MAD ON MA.ID = MAD.tblMASTER
LEFT OUTER JOIN #myMov MO ON MA.ID = MO.IDMaster
LEFT OUTER JOIN #myMovDetails MD ON MO.ID = MD.IDMov AND MAD.ID = MD.IDMasterDetail
GROUP BY MA.Def,
MAD.Reference,
MAD.Quantity
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply