Join - 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 2000 Forums

SQL Server Development (2000)

Join

Author

Topic

tracy5436
Yak Posting Veteran

50 Posts

Posted - 2009-08-25 : 17:06:56

I have the following macro in Excel :

Sub Inventory()
'
' Inventory Macro
'
' Keyboard Shortcut: Ctrl+i
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim sqlstring As String
Dim sqlstring2 As String

Dim rstupdate As New ADODB.Recordset
Dim objConn
Dim cmdString
Dim objRec
Dim startdat As String
Dim enddate As String
Dim procdate As String
Dim exchangerate As Currency

ChDir "E:\KIRK DISTRIBUTORS - ITT\SALES"
Workbooks.Open Filename:="E:\KIRK DISTRIBUTORS - ITT\INVENTRY\INVENTRY4.XLS"
Rows("2:10658").Select
Selection.Delete Shift:=xlUp
Workbooks.Close

Set objRec = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")

With cn
.ConnectionString = "Provider=MSDASQL;DSN=GREATPLAINS;User Id=sa;Password=ramses2;database=KDLTD;"
.CursorLocation = adUseClient
.Open
End With
cmd.ActiveConnection = cn
cmd.CommandType = adCmdText
cn.CommandTimeout = 900000
cmd.CommandTimeout = 900000

startdat = InputBox("Starting Date: (MM/DD/YYYY")
enddate = InputBox("Ending Date: (MM/DD/YYYY)")
procdate = InputBox("Process Date: (YYYYMM)")
exchangerate = InputBox("Exchange Rate")

sqlstring2 = "DELETE INVENTRY"
sqlstring = "INSERT INTO INVENTRY([STOCKLIST CODE], [WAREHOUSE CODE], [PROCESS YYYYMM], [INVENTORY TYPE], [PRODUCT CODE], [OPENING BALANCE QTY.], [OPENING BAL. VALUE],[RECEIPT QUANTITY],[RECEIPT VALUE],[FREE RECEIPT QUANTITY],[FREE RECEIPT VALUE],[RETURN QUANTITY],[RETURN VALUE],[ADJUSTMENT QUANTITY],[ADJUSTMENT VALUE],[ALLOCATED QUANTITY],[ALLOCATED VALUE],[CLOSING QUANTITY],[CLOSING VALUE])"
sqlstring = sqlstring & " SELECT '5555550101','MGD-MAIN',ProcessDate,'S',ProductCode,ISNULL(OpenQty,0.00),ISNULL(OpeningBalValue,0.00),ISNULL(RecQty,0.00),ISNULL(RecValue,0.00),0.00,0.00,ISNULL(RetQty,0.00),ISNULL(RetValue,0.00),ISNULL(AdjQtyb,0.00)+ISNULL(AdjQty,0.00),ISNULL(AdjValue,0.00)+ISNULL(AdjValueb,0.00),ISNULL(AllocQty,0.00),ISNULL(AllocValue,0.00),ISNULL(OpenQty,0.00)+ISNULL(RecQty,0.00)+ISNULL(RetQty,0.00)+ISNULL(AdjQty,0.00)+ISNULL(AdjQtyB,0.00)-ISNULL(AllocQty,0.00),ISNULL(OpeningBalValue,0.00)+ISNULL(RecValue,0.00)+ISNULL(RetValue,0.00)+ISNULL(AdjValue,0.00)-ISNULL(AllocValue,0.00) "
sqlstring = sqlstring & " FROM ( SELECT '" & procdate & "' As ProcessDate12,I11.[CP PRODUCT CODE] as ProductCode11,SUM(I12.TRXQTY*I11.[UNITS/CASE]*-1) as AllocQty,SUM(I12.TRXQTY*I11.[UNITS/CASE]*I11.[UNIT PRICE]*-1) as AllocValue"
sqlstring = sqlstring & " FROM IV30300 I12"
sqlstring = sqlstring & " RIGHT OUTER JOIN [ITEM_MAPPING] I11"
sqlstring = sqlstring & " ON I11.[KIRK ITEM NUMBER] = I12.ITEMNMBR"
sqlstring = sqlstring & " and I12.DOCDATE BETWEEN '" & startdat & "' AND '" & enddate & "' "
sqlstring = sqlstring & " AND I12.DOCTYPE = '6' "
sqlstring = sqlstring & " AND I12.TRXLOCTN = 'MGD_MAIN' "
sqlstring = sqlstring & " GROUP BY I11.[CP PRODUCT CODE]"
sqlstring = sqlstring & " )D12"
sqlstring = sqlstring & " RIGHT OUTER JOIN"
sqlstring = sqlstring & " ( SELECT"
sqlstring = sqlstring & " '" & procdate & "' As ProcessDate,I2.[CP PRODUCT CODE] as ProductCode,SUM(I3.QTYONHND*I2.[UNITS/CASE]) As ClosingQty1,SUM(I3.QTYONHND*I2.[UNITS/CASE]*I1.CURRCOST)/'" & exchangerate & "' As ClosingValue1"
sqlstring = sqlstring & " FROM IV00101 I1 JOIN [ITEM_MAPPING] I2 ON I1.ITEMNMBR = I2.[KIRK ITEM NUMBER] JOIN IV00102 I3 ON I1.ITEMNMBR = I3.ITEMNMBR AND I3.LOCNCODE = 'MGD_MAIN'"
sqlstring = sqlstring & " GROUP BY I2.[CP PRODUCT CODE]"
sqlstring = sqlstring & " )D1"
sqlstring = sqlstring & " ON D12.ProductCode11 = D1.ProductCode "
sqlstring = sqlstring & " LEFT JOIN ("
sqlstring = sqlstring & " SELECT B2.[CP PRODUCT CODE] as Item,SUM(B1.[QTY PER STOCK]*B2.[UNITS/CASE]) as ClosingQty,SUM(B1.[QTY PER STOCK]*(B2.[UNITS/CASE])*B2.[UNIT PRICE]) as ClosingValue,SUM(B1.[QTY PER STOCK]*B2.[UNITS/CASE]) as OpenQty,SUM(B2.[UNIT PRICE]*(B1.[QTY PER STOCK])) As OpeningBalValue"
sqlstring = sqlstring & " FROM OPENINGBAL B1 INNER JOIN [ITEM_MAPPING] B2 ON B1.[ITEM NUMBER] = B2.[KIRK ITEM NUMBER] GROUP BY B2.[CP PRODUCT CODE]"
sqlstring = sqlstring & " )D13"
sqlstring = sqlstring & " ON D1.ProductCode = D13.Item"
sqlstring = sqlstring & " LEFT JOIN ("
sqlstring = sqlstring & " SELECT I6.[CP PRODUCT CODE] as ProductCode2,SUM(I5.TRXQTY*I6.[UNITS/CASE]) as AdjQty,SUM(I6.[UNIT PRICE]*(I5.TRXQTY*I6.[UNITS/CASE])) as AdjValue"
sqlstring = sqlstring & " FROM IV30300 I5 RIGHT OUTER JOIN [ITEM_MAPPING] I6 ON I6.[KIRK ITEM NUMBER] = I5.ITEMNMBR AND DOCDATE BETWEEN '" & startdat & "' AND '" & enddate & "' AND DOCTYPE = '1' "
sqlstring = sqlstring & " GROUP BY I6.[CP PRODUCT CODE]"
sqlstring = sqlstring & " )D2"
sqlstring = sqlstring & " ON D1.ProductCode = D2.ProductCode2"

sqlstring = sqlstring & " LEFT JOIN ("
sqlstring = sqlstring & " SELECT I6.[CP PRODUCT CODE] as ProductCode2B,SUM(I5.TRXQTY*I6.[UNITS/CASE]) as AdjQtyB,SUM(I6.[UNIT PRICE]*(I5.TRXQTY*I6.[UNITS/CASE])) as AdjValueB"
sqlstring = sqlstring & " FROM IV30300 I5 RIGHT OUTER JOIN [ITEM_MAPPING] I6 ON I6.[KIRK ITEM NUMBER] = I5.ITEMNMBR AND DOCDATE BETWEEN '" & startdat & "' AND '" & enddate & "' AND DOCTYPE = '2' "
sqlstring = sqlstring & " GROUP BY I6.[CP PRODUCT CODE]"
sqlstring = sqlstring & " )D2B"
sqlstring = sqlstring & " ON D2.ProductCode2 = D2B.ProductCode2B"

sqlstring = sqlstring & " LEFT OUTER JOIN ("
sqlstring = sqlstring & " SELECT I9.[CP PRODUCT CODE] as ProdCode FROM [ITEM_MAPPING] I9 LEFT OUTER JOIN IV30300 I10 ON I9.[KIRK ITEM NUMBER] = I10.ITEMNMBR GROUP BY I9.[CP PRODUCT CODE]"
sqlstring = sqlstring & " )D4"
sqlstring = sqlstring & " ON D12.ProductCode11 = D4.ProdCode LEFT OUTER JOIN ("
sqlstring = sqlstring & " SELECT I7.[CP PRODUCT CODE] as ProductCode8,SUM(P1.TRXQTY * I7.[UNITS/CASE]) as RecQty,SUM(I7.[UNIT PRICE]*(P1.TRXQTY * I7.[UNITS/CASE])) as RecValue"
sqlstring = sqlstring & " FROM IV30300 P1 RIGHT OUTER JOIN [ITEM_MAPPING] I7 ON P1.ITEMNMBR = I7.[KIRK ITEM NUMBER] AND P1.DOCTYPE = '4' AND TRXLOCTN = 'MGD_MAIN' AND P1.DOCDATE BETWEEN '" & startdat & "' AND '" & enddate & "' GROUP BY I7.[CP PRODUCT CODE]"
sqlstring = sqlstring & " )D3"
sqlstring = sqlstring & " ON D4.ProdCode = D3.ProductCode8 "
sqlstring = sqlstring & " LEFT OUTER JOIN ( "
sqlstring = sqlstring & " SELECT I70.[CP PRODUCT CODE] as ProductCode10,SUM(P10.TRXQTY * I70.[UNITS/CASE]) as RetQty,SUM(I70.[UNIT PRICE]*(P10.TRXQTY * I70.[UNITS/CASE])) as RetValue "
sqlstring = sqlstring & " FROM IV30300 P10 RIGHT OUTER JOIN [ITEM_MAPPING] I70 ON P10.ITEMNMBR = I70.[KIRK ITEM NUMBER] AND P10.DOCTYPE = '5' AND TRXLOCTN = 'MGD_MAIN' AND P10.DOCDATE BETWEEN '" & startdat & "' AND '" & enddate & "' GROUP BY I70.[CP PRODUCT CODE]"
sqlstring = sqlstring & " )D10"
sqlstring = sqlstring & " ON D4.ProdCode = D10.ProductCode10 WHERE OpenQty > 0 Or RetQty > 0 Or AdjQty > 0 Or RecQty > 0"

rstupdate.Open sqlstring2, cn, adOpenDynamic, adLockOptimistic, adCmdText
rstupdate.Open sqlstring, cn, adOpenDynamic, adLockOptimistic, adCmdText
MsgBox ("Process Complete")
cn.Close

End Sub

The problem is that in the OpeningBal table, if the quantity is 0, the product code is not added to the inventry table. What is wrong ?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-25 : 17:29:42

If I was hoping to get help for this I would do a print on the var sqlstring and then post this formatted query, table structure, sample data and wanted output.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources