October 28, 2013 at 6:20 am
I have this created this table that I want to insert data in via a query:
CREATE TABLE [dbo].[BI1_DW_Fact_InventoryBalances](
[KeyDate] [int] NOT NULL,
[KeyDepot] [int] NOT NULL,
[KeyItem] [int] NOT NULL,
[TransactionQuantity] decimal(11,3) NULL,
[FacilityOpeningBalance] decimal(11,3) NULL,
[BalanceOnDate] decimal(11,3) NULL
This is the query that I am running:
SELECT a.facility,
a.ItemCode,
a.ItemDescription,
SUM(a.Quantity) AS TxnQty,
b.FacOpb,
SUM(a.Quantity)+ b.FacOpb AS BalanceOnDate
FROM
(SELECT w.depotcode AS Facility,
i.itemcode AS ItemCode,
i.itemdescription AS ItemDescription,
SUM(t.transactionquantity) AS Quantity,
SUM(b.OpeningBalance) AS OpeningBal
FROM dbo.BI1_DW_Fact_TransactionHistoryInventory AS T
LEFT JOIN BI1_DW_Dim_CalendarDefinition AS D
ON d.KeyDate = t.KeyDate
LEFT JOIN BI1_DW_Dim_WarehouseMaster AS W
ON w.keywarehouse = t.keywarehouse
LEFT JOIN BI1_DW_Dim_ItemMaster AS I
ON i.KeyItem = t.KeyItem
LEFT JOIN BI1_DW_Dim_ItemClassMaster AS C
ON c.ItemClass = I.ItemClass
LEFT JOIN BI1_DW_Dim_LocationMaster AS L
ON l.KeyLocation = t.KeyLocation
LEFT JOIN BI1_DW_Dim_TransactionEffectMaster AS E
ON e.transactiontype = t.TransactionType
LEFT JOIN BI1_DW_Fact_LocationInventory AS B
ON b.KeyItem = t.KeyItem
AND b.KeyDate = t.KeyDate
AND b.KeyLocation = t.KeyLocation
---Dates should be an input parameter ( from and to)
---Entire query should loop through the days with the date range
WHERE d.TransDateNumeric BETWEEN 20131024 AND 20131027
AND W.DepotCode IS NOT NULL ---You can also include a facility paramater here if required
AND
(E.AffectOpeningBalance ='Y'
OR E.AffectReceipts ='Y'
OR E.AffectAdjustments ='Y'
OR E.AffectIssues ='Y' )
AND l.LocationCode NOT IN('61','62','63')
GROUP BY w.DepotCode,i.ItemCode,i.ItemDescription
)a
--Opening Balance Per Month--
LEFT JOIN(
SELECT FacSum.Depot,
FacSum.ItemCode,
facsum.ItemDesc,
SUM(FacSum.OpeningBalance) AS FacOpb
FROM
(SELECT w.depotcode AS Depot,
i.itemcode AS ItemCode,
i.ItemDescription AS ItemDesc,
w.warehousecode,
t.Keylocation,
OpeningBalance
FROM BI1_DW_Fact_LocationInventory t
LEFT JOIN BI1_DW_Dim_ItemMaster AS I
ON i.KeyItem = t.KeyItem
LEFT JOIN BI1_DW_Dim_CalendarDefinition AS D
ON d.KeyDate = t.KeyDate
LEFT JOIN BI1_DW_Dim_LocationMaster AS L
ON l.KeyLocation = t.KeyLocation
LEFT JOIN BI1_DW_Dim_WarehouseMaster AS W
ON w.keywarehouse = l.keywarehouse
-- 1st of day of the month based on the selected date range
WHERE d.TransDateNumeric >= 20131001
GROUP BY w.depotcode,
i.itemcode,
i.ItemDescription,
w.warehousecode,
t.Keylocation,
OpeningBalance ) FacSum
GROUP BY Depot,
ItemCode,
ItemDesc )b
ON b.Depot=a.Facility
AND b.ItemCode = a.ItemCode
GROUP BY a.facility,
a.ItemCode,
a.ItemDescription,
b.FacOpb
ORDER BY a.Facility, a.ItemCode
What I want to do is to run this query through days in a month, i.e. if I run this query on, for example, the third, it must insert the data for the first, second and third.
Is there anyone that can help?
Kind regards
October 28, 2013 at 6:36 am
I don't understand. What do you get by looping through those dates that you don't get from the results of the query as it stands?
John
October 28, 2013 at 7:23 am
Hi John
Some of those fields gets updated daily so the updated data will also then be pulled into this table. It is essentially to look between branches and see which one has stock and which one has not. If a user then runs the program say, on the fifteenth, said user would want the stock for the branches as if for that certain month. User will then know where the stock is.
Hope that makes sense?
October 28, 2013 at 8:03 am
I'm afraid not. I know you've got a lot of tables, but maybe you could simplify it, please, and then provide some sample data and expected results?
John
October 28, 2013 at 8:10 am
If I can really simplify it, it comes down to basic stock replenishment. If branch A is out of a certain item (stock), we want to know if the item is in branch B, and if it is we want to supply branch A with that item. Thus, the items (stock) will constantly change. As this query is run constantly we need to know how much stock there is on a certain date at a certain branch.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply