October 12, 2010 at 1:12 pm
i would like to know how i do the following;
i have a UNION sp that retrieves receipts and sales based upon start and end parameters
if i select one month the values are fine,
if i select two months i need to substract the receipts from the previous month to get my begining value for the month.
i have a table DBO.MIDYRIVT that captures a beginning inventory value for the month of july. - this value is always static
i have a table dbo.SHIPSKU# that captures shipments within a date range(parameters)
i have a table dbo.RECVSKU# that captures receipts within a date range.
if i select the month greater than july, i need to be able to add the previous months shipments and subtract the previous months receipts to get a new starting beginning value for the month.
this is my stored procedure.
if @START_DATE >='20100701'
begin
SELECT
X.INVTYPE,
X.COMPANY,
X.SEASON,
X.STYLE,
X.COLOR_#,
X.SKU,
X.INV#,
X.ON_HAND,
X.TRANS_DATE,
X.SHIP_QTY,
X.REC_QTY,
X.CRNT_COST
FROM (SELECT 0 AS INVTYPE,
DBO.MIDYRIVT.DIV as COMPANY,
DBO.MIDYRIVT.SEASON AS SEASON,
DBO.MIDYRIVT.STYLE# as style,
DBO.MIDYRIVT.colr# as COLOR_#,
DBO.MIDYRIVT.SKUNBR AS SKU,
'' AS INV#,
DBO.MIDYRIVT.ONHAND AS ON_HAND,
'' AS TRANS_DATE,
'' AS SHIP_QTY,
'' AS REC_QTY,
dbo.MITMAS.MMPUPR AS CRNT_COST
FROM
DBO.MIDYRIVT
LEFT OUTER JOIN
dbo.MITMAS ON DBO.MIDYRIVT.SKUNBR = dbo.MITMAS.MMITNO
GROUP BY
DBO.MIDYRIVT.DIV,
DBO.MIDYRIVT.SEASON,
DBO.MIDYRIVT.style#,
DBO.MIDYRIVT.colr#,
DBO.MIDYRIVT.SKUNBR,
DBO.MIDYRIVT.ONHAND,
dbo.MITMAS.MMPUPR
) X
GROUP BY
X.INVTYPE,
X.COMPANY,
X.SEASON,
X.STYLE,
X.COLOR_#,
X.SKU,
X.INV#,
X.ON_HAND,
X.TRANS_DATE,
X.SHIP_QTY,
X.REC_QTY,
X.CRNT_COST
UNION
SELECT
X.INVTYPE,
X.COMPANY,
X.SEASON,
X.STYLE,
X.COLOR_#,
X.SKU,
X.INV#,
X.ON_HAND,
X.TRANS_DATE,
X.SHIP_QTY,
X.REC_QTY,
X.CRNT_COST
FROM (
SELECT
1 AS INVTYPE,
dbo.SHIPSKU#.SCDIVN AS COMPANY,
dbo.SHIPSKU#.SCSEAS AS SEASON,
dbo.SHIPSKU#.SCSTYL AS STYLE,
dbo.shipsku#.sccolr as COLOR_#,
dbo.SHIPSKU#.SCSKU# AS SKU,
dbo.SHIPSKU#.scinv# AS INV#,
'' AS ON_HAND,
dbo.SHIPSKU#.SCDATE AS TRANS_DATE,
dbo.SHIPSKU#.SCQTY AS SHIP_QTY,
'' AS REC_QTY,
dbo.MITMAS.MMPUPR AS CRNT_COST
FROM dbo.SHIPSKU#
LEFT OUTER JOIN
dbo.MITMAS ON dbo.SHIPSKU#.SCSKU# = dbo.MITMAS.MMITNO
WHERE dbo.SHIPSKU#.SCDATE>=@START_DATE AND dbo.SHIPSKU#.SCDATE <=@END_DATE
) X
GROUP BY
X.INVTYPE,
X.COMPANY,
X.SEASON,
X.STYLE,
X.COLOR_#,
X.SKU,
X.INV#,
X.ON_HAND,
X.TRANS_DATE,
X.SHIP_QTY,
X.REC_QTY,
X.CRNT_COST
UNION
SELECT
X.INVTYPE,
X.COMPANY,
X.SEASON,
X.STYLE,
X.COLOR_#,
X.SKU,
X.INV#,
X.ON_HAND,
X.TRANS_DATE,
X.SHIP_QTY,
X.REC_QTY,
X.CRNT_COST
FROM (
SELECT 2 AS Invtype,
dbo.RECVSKU#.RCDIVN AS COMPANY,
dbo.RECVSKU#.RCSEAS AS SEASON,
dbo.RECVSKU#.RCstyl AS style,
dbo.RECVSKU#.RCCOLR as COLOR_#,
dbo.RECVSKU#.rCSKU# as SKU,
'' AS INV#,
'' AS ON_HAND,
dbo.RECVSKU#.RCDATE AS TRANS_DATE,
'' AS SHIP_QTY,
Dbo.RECVSKU#.RCQTY AS REC_QTY,
dbo.MITMAS.MMPUPR AS CRNT_COST
FROM
dbo.MITMAS
RIGHT OUTER JOIN
dbo.RECVSKU# ON dbo.MITMAS.MMITNO = dbo.RECVSKU#.RCSKU#
WHERE dbo.RECVSKU#.RCDATE>=@START_DATE AND dbo.RECVSKU#.RCDATE <=@END_DATE
GROUP BY
dbo.RECVSKU#.RCDIVN,
dbo.RECVSKU#.RCSEAS,
dbo.RECVSKU#.RCSTYL,
dbo.RECVSKU#.RCCOLR,
dbo.RECVSKU#.rCSKU#,
dbo.RECVSKU#.RCDATE,
Dbo.RECVSKU#.RCQTY,
dbo.MITMAS.MMPUPR
) X
GROUP BY
X.INVTYPE,
X.COMPANY,
X.SEASON,
X.STYLE,
X.COLOR_#,
X.SKU,
X.INV#,
X.ON_HAND,
X.TRANS_DATE,
X.SHIP_QTY,
X.REC_QTY,
X.CRNT_COST
END
October 13, 2010 at 5:37 am
First, get rid of the # in the data element names; it is HIGHLY proprietary and a little dangerous. Can you explain what a sku_nbr is and why it is totally unlike a mere sku? A lot of your data elements seem to have multiple names or vague ones.
--- those table names were not created by me and i cant alter them.
You have no aggregate functions, but you use GROUP BY. You do UNIONs but I see no reason for them. I tried to clean up the code to get it readable. Newspapers and books use lowercase for a reason. So I am going to your narrative instead.
-- i havent removed the group because i have been working with the sp and possibly want to put a sum in.
>> I have a table MidYrIvt (Mid_Yr_Inventory, if you wer to use a readable name?) that captures a beginning inventory value for the month of July. -This table is always static. <<
>> I have a table Shipsku# (Shipments?) that captures shipments within a date range (parameters) <<
Tables do not have parameters; functions have parameters. Where is a table Shipments with each shipment and its shipping date and other details?
-- the stored procedure has the parameters in it. the shipsku# table is the shipment table i am using.
>> I have a table Recvsku# (Received_Goods? or Receipts?) that captures receipts within a date range. If I select the month greater than July, I need to be able to add the previous months shipments and subtract the previous months receipts to get a new starting beginning value for the month. <<
Are you familiar with report period calendar Tables? They hold the start and end dates of reporting periods? Very handy trick for this kind of thing. Here is my guess at a skeleton program:
--- no i am not familiar with this.
SELECT C.report_period_name, sku, SUM(inventory_qty) AS onhand_qty
FROM (SELECT sku, onhand_qty, '2010-07-31', ..
FROM MidYrIvt AS M
UNION ALL
SELECT sku, -onhand_qty, ship_date, ..
FROM Shipments AS S
UNION ALL
SELECT sku, onhand_qty, received_date, ..
FROM Receipts AS R
WHERE received_date BETWEEN @in_start_date AND @in_end_date)
AS Net_Inventory_Changes (sku, inventory_qty, event_date,..)
INNER JOIN
Report_Calendar AS C
ON Net_Inventory_Changes.event_date
BETWEEN C.report_period_start_date AND C.report_period_end_date)
GROUP BY sku, C.report_period;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply