Skip to Content
0

Query parameters problem

Jan 11, 2017 at 07:07 AM

124

avatar image

Hi experts

The query below was done by previous employee. This query is used as a database in Excel and has a lot of information from various tables.

The PO+4 column does not populate the information (even though there is and should be information), the columns PO+3, PO+2 etc does.

I have tried various options as in change the date etc but do not know what or where in the query it is that drives or why it does not populate seems its limited maybe to 3 months??

Would appreciate if someone can spot or advise on this.

/* SELECT FROM [dbo].[OFCT] T0 */ DECLARE @ForecastCode nvarchar(25) /* WHERE */ SET @ForecastCode = /* T0.Code */ '[%1]' --TEST PARAMETERS --DECLARE @ForecastCode nvarchar(25) --SET @ForecastCode = 'CA09' SET ARITHABORT OFF SET ANSI_WARNINGS OFF declare @Period Int set @Period = (Select T1.AbsEntry from OFPR T1 where (YEAR(T1.F_RefDate) = YEAR(getdate()) and MONTH(T1.F_RefDate) = MONTH(GETDATE())) and( YEAR(T1.T_RefDate) = YEAR(getdate()) AND MONTH(T1.T_RefDate) = MONTH(getdate()) ) ) SELECT T1.ItemCode ,T1.ItemName , T1.frgnname as 'Item Group' , T3.Descr as 'Chain/Trade' , T2.ItmsGrpNam as 'Customer Group' , t4.FirmName as 'Species' , t1.LstEvlPric , T1.LstEvlDate as 'Last Reval Date' , T1.LastpurDat , ISNULL(Z2.OnHand,0) as 'SOH' , ISNULL(Z2.[Open PO's],0) as 'Open POs' --, ISNULL(Z2.[IsCommited],0) as 'Open SO' ,ISNULL(Z7.[SO+00] ,0)+ISNULL(Z7.[SO+01],0)+ISNULL(Z7.[SO+02],0)+ISNULL(Z7.[SO+03],0)+ISNULL(Z7.[SO+04],0)+ISNULL(Z7.[SO+05],0)+ ISNULL(Z7.[SO+06],0)+ISNULL(Z7.[SO+07],0)+ISNULL(Z7.[SO+08],0)+ISNULL(Z7.[SO+09],0)+ISNULL(Z7.[SO+10],0)+ISNULL(Z7.[SO+11],0)+ISNULL(Z7.[SO+12],0)[IsCommited] ,isnull(z3.[SALE-01],0)+isnull(Z3.[SALE-02],0)+isnull(Z3.[SALE-03],0) as 'Total Sales 3 Months' ,Z1.* ,ISNULL(isnull(z3.[SALE-01],0)+isnull(Z3.[SALE-02],0)+isnull(Z3.[SALE-03],0),1)/ISNULL(ZA.DocAmt,1)[Ave 3 Month Total Sales] ,Z5.MAXSales[MAX Total Sales] ,Z5.MINSales[MIN Total Sales] ,CASE WHEN ISNULL(isnull(z3.[SALE-01],0)+isnull(Z3.[SALE-02],0)+isnull(Z3.[SALE-03],0),0) = 0 THEN 0 ELSE ISNULL(Z2.OnHand,0)/ISNULL(Z1.[Ave 3 Month Sales],0) END [Ave Month Holding] ,CASE WHEN ISNULL(isnull(z3.[SALE-01],0)+isnull(Z3.[SALE-02],0)+isnull(Z3.[SALE-03],0),0) = 0 THEN 0 ELSE ISNULL(Z2.OnHand,0)/ISNULL(Z1.[Max],0) END [Max Month Holding] ,casE WHEN ISNULL(isnull(z3.[SALE-01],0)+isnull(Z3.[SALE-02],0)+isnull(Z3.[SALE-03],0),0) = 0 THEN 0 ELSE ISNULL(Z2.OnHand,0)/ISNULL(Z1.[Min],0) END [Min Month Holding] ,Z3.* ,Z4.* ---- ,ISNULL(Z6.[PO+00],0)[PO+00], ISNULL(Z6.[PO+01],0)[PO+01], ISNULL(Z6.[PO+02],0)[PO+02], ISNULL(Z6.[PO+03],0)[PO+03], ISNULL(Z6.[PO+04],0)[PO+04], ISNULL(Z6.[PO+05],0)[PO+05], ISNULL(Z6.[PO+06],0)[PO+06], ISNULL(Z6.[PO+07],0)[PO+07], ISNULL(Z6.[PO+08],0)[PO+08], ISNULL(Z6.[PO+09],0)[PO+09], ISNULL(Z6.[PO+10],0)[PO+10], ISNULL(Z6.[PO+11],0)[PO+11], ISNULL(Z6.[PO+12],0)[PO+12] --,Z6.* ---- ,ISNULL(Z7.[SO+00] ,0)[SO+00], ISNULL(Z7.[SO+01],0)[SO+01], ISNULL(Z7.[SO+02],0)[SO+02], ISNULL(Z7.[SO+03],0)[SO+03], ISNULL(Z7.[SO+04],0)[SO+04], ISNULL(Z7.[SO+05],0)[SO+05], ISNULL(Z7.[SO+06],0)[SO+06], ISNULL(Z7.[SO+07],0)[SO+07], ISNULL(Z7.[SO+08],0)[SO+08], ISNULL(Z7.[SO+09],0)[SO+09], ISNULL(Z7.[SO+10],0)[SO+10], ISNULL(Z7.[SO+11],0)[SO+11], ISNULL(Z7.[SO+12],0)[SO+12] FROM OITM T1 INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod LEFT JOIN UFD1 T3 ON T3.TableID = 'oitm' AND T1.[U_Chain] = T3.[FldValue] AND T3.FieldID = 28 LEFT JOIN OMRC T4 on T1.FirmCode = T4.FirmCode CROSS APPLY ( SELECT --sum(ISNULL(A2.Quantity,0)) as 'Total Sales 3 Months' This is not net of CNs so I've changed it to sum Z3 sales sum(ISNULL(A2.Quantity,0))/3 as 'Ave 3 Month Sales' , max(ISNULL(A2.Quantity,0)) as 'MAX' , min(ISNULL(A2.Quantity,0)) as 'MIN' --, SUM(ISNULL(A2.Quantity,0)) as 'TotalALLSales' -- ,ISNULL(COUNT(ISNULL(A1.DocEntry,0)),0) as 'DocAmt' FROM ODLN A1 inner join DLN1 A2 on A1.DocEntry = A2.DocEntry WHERE A2.ItemCode = T1.ItemCode AND A1.DocDate >= DATEADD(month, DATEDIFF(month, 0, DATEADD(month, -3, GETDATE())), 0) AND A1.DocDate < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AND A1.cardcode NOT IN ('ZT01','ZK01') ) Z1 CROSS APPLY ( SELECT COUNT(A1.DocCur) as 'DocAmt' FROM ODLN A1 inner join DLN1 A2 on A1.DocEntry = A2.DocEntry WHERE A2.ItemCode = T1.ItemCode AND A1.DocDate >= DATEADD(month, DATEDIFF(month, 0, DATEADD(month, -3, GETDATE())), 0) AND A1.DocDate < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AND A1.cardcode NOT IN ('ZT01','ZK01') ) ZA CROSS APPLY ( SELECT distinct max(A2.Quantity) as 'SalesPast12Months' FROM ODLN A1 inner join DLN1 A2 on A1.DocEntry = A2.DocEntry Join OFPR on A2.ShipDate between F_RefDate and T_RefDate WHERE OFPR.AbsEntry between @Period-13 and @Period -1 and A2.ItemCode = T1.ItemCode AND A1.cardcode NOT IN ('ZT01','ZK01') ) ZZ CROSS APPLY ( SELECT SUM(A1.OnHand)[onhand],SUM(ISNULL(A1.OnOrder,0))[Open PO's],SUM(ISNULL(A1.iscommited,0))[IsCommited] FROM OITM A1 WHERE A1.ItemCode = T1.ItemCode) Z2 OUTER APPLY (SELECT * FROM ( SELECT --MONTH(A1.DocDate)[MonthInt], case when OFPR.AbsEntry=@Period then 'SALE-' + '00' when OFPR.AbsEntry=@Period-1 then 'SALE-' + '01' when OFPR.AbsEntry=@Period-2 then 'SALE-' + '02' when OFPR.AbsEntry=@Period-3 then 'SALE-' + '03' when OFPR.AbsEntry=@Period-4 then 'SALE-' + '04' when OFPR.AbsEntry=@Period-5 then 'SALE-' + '05' when OFPR.AbsEntry=@Period-6 then 'SALE-' + '06' when OFPR.AbsEntry=@Period-7 then 'SALE-' + '07' when OFPR.AbsEntry=@Period-8 then 'SALE-' + '08' when OFPR.AbsEntry=@Period-9 then 'SALE-' + '09' when OFPR.AbsEntry=@Period-10 then 'SALE-' + '10' when OFPR.AbsEntry=@Period-11 then 'SALE-' + '11' when OFPR.AbsEntry=@Period-12 then 'SALE-' + '12' end as [MonthName] ,(A2.Quantity)[Quantity] -- ,A2.ItemCode FROM ODLN A1 inner join DLN1 A2 on A1.DocEntry = A2.DocEntry Join OFPR on A1.DocDate between F_RefDate and T_RefDate WHERE OFPR.AbsEntry between @Period-12 and @Period AND A2.ItemCode = T1.ItemCode AND A1.cardcode NOT IN ('ZT01','ZK01') Union all SELECT --MONTH(A1.DocDate)[MonthInt], case when OFPR.AbsEntry=@Period then 'SALE-' + '00' when OFPR.AbsEntry=@Period-1 then 'SALE-' + '01' when OFPR.AbsEntry=@Period-2 then 'SALE-' + '02' when OFPR.AbsEntry=@Period-3 then 'SALE-' + '03' when OFPR.AbsEntry=@Period-4 then 'SALE-' + '04' when OFPR.AbsEntry=@Period-5 then 'SALE-' + '05' when OFPR.AbsEntry=@Period-6 then 'SALE-' + '06' when OFPR.AbsEntry=@Period-7 then 'SALE-' + '07' when OFPR.AbsEntry=@Period-8 then 'SALE-' + '08' when OFPR.AbsEntry=@Period-9 then 'SALE-' + '09' when OFPR.AbsEntry=@Period-10 then 'SALE-' + '10' when OFPR.AbsEntry=@Period-11 then 'SALE-' + '11' when OFPR.AbsEntry=@Period-12 then 'SALE-' + '12' end as [MonthName] ,ISNULL(A2.Quantity*-1,0)[Quantity] -- ,A2.ItemCode FROM ORIN A1 inner join RIN1 A2 on A1.DocEntry = A2.DocEntry Join OFPR on A1.DocDate between F_RefDate and T_RefDate WHERE OFPR.AbsEntry between @Period-12 and @Period AND A2.ItemCode = T1.ItemCode AND A1.cardcode NOT IN ('ZT01','ZK01')) as DeliverybyMonth PIVOT ( SUM(Quantity) FOR MonthName IN ([SALE-00],[SALE-01],[SALE-02],[SALE-03],[SALE-04],[SALE-05],[SALE-06],[SALE-07],[SALE-08],[SALE-09],[SALE-10], [SALE-11],[SALE-12]) --[1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]) )AS [SalesperMonth]) Z3 OUTER APPLY (SELECT * FROM ( SELECT --MONTH(A1.DocDate)[MonthInt], -- 'FCST-' + DATENAME(MONTH, (A2.Date))[MonthName] case when OFPR.AbsEntry<=@Period-3 then 'FCST-03<' WHEN OFPR.AbsEntry=@Period-2 THEN 'FCST-02' WHEN OFPR.AbsEntry=@Period-1 THEN 'FCST-01' WHEN OFPR.AbsEntry=@Period THEN 'FCST-00' WHEN OFPR.AbsEntry=@Period+1 THEN 'FCST+01' WHEN OFPR.AbsEntry=@Period+2 THEN 'FCST+02' WHEN OFPR.AbsEntry=@Period+3 THEN 'FCST+03' WHEN OFPR.AbsEntry=@Period+4 THEN 'FCST+04' WHEN OFPR.AbsEntry=@Period+5 THEN 'FCST+05' WHEN OFPR.AbsEntry>=@Period+6 THEN 'FCST+06>' END AS [MonthName] ,(A2.Quantity)[Quantity] -- ,A2.ItemCode FROM OFCT A1 inner join FCT1 A2 on A1.AbsID = A2.AbsID Join OFPR on A2.Date between F_RefDate and T_RefDate WHERE A1.Code = @ForecastCode AND A2.ItemCode = T1.ItemCode AND A2.Date >= DATEADD(month, DATEDIFF(month, 0, DATEADD(month, -12, GETDATE())), 0) AND A2.Date <= DATEADD(month, DATEDIFF(month, 0, DATEADD(month, 12, GETDATE())), 0)) as DeliverybyMonth PIVOT ( SUM(Quantity) FOR MonthName IN ([FCST-03<],[FCST-02],[FCST-01],[FCST-00],[FCST+01],[FCST+02],[FCST+03],[FCST+04],[FCST+05],[FCST+06>]) --[1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]) )AS [ForecastperMonth]) Z4 OUTER APPLY (SELECT MAX(Quantity)[MAXSales],MIN(Quantity)[MINSales] FROM ( SELECT SUM(A2.Quantity)[Quantity] ,MONTH(A2.DocDate)[Month] -- ,A2.ItemCode FROM ODLN A1 inner join DLN1 A2 on A1.DocEntry = A2.DocEntry WHERE A2.DocDate >= DATEADD(month, DATEDIFF(month, 0, DATEADD(month, -12, GETDATE())), 0) AND A2.DocDate < DATEADD(month, DATEDIFF(month, 0, DATEADD(month, +12, GETDATE())), 0) AND A2.ItemCode = T1.ItemCode AND A1.cardcode NOT IN ('ZT01','ZK01') GROUP BY MONTH(A2.DocDate))AS [SalesperMonth] ) Z5 OUTER APPLY (SELECT * FROM ( SELECT --MONTH(A1.DocDate)[MonthInt], case when OFPR.AbsEntry<=@Period then 'PO+' + '00' when OFPR.AbsEntry=@Period+1 then 'PO+' + '01' when OFPR.AbsEntry=@Period+2 then 'PO+' + '02' when OFPR.AbsEntry=@Period+3 then 'PO+' + '03' when OFPR.AbsEntry=@Period+4 then 'PO+' + '04' when OFPR.AbsEntry=@Period+5 then 'PO+' + '05' when OFPR.AbsEntry=@Period+6 then 'PO+' + '06' when OFPR.AbsEntry=@Period+7 then 'PO+' + '07' when OFPR.AbsEntry=@Period+8 then 'PO+' + '08' when OFPR.AbsEntry=@Period+9 then 'PO+' + '09' when OFPR.AbsEntry=@Period+10 then 'PO+' + '10' when OFPR.AbsEntry=@Period+11 then 'PO+' + '11' when OFPR.AbsEntry=@Period+12 then 'PO+' + '12' end as [MonthName] ,ISNULL(A1.OpenQty,0)[Quantity] -- ,A2.ItemCode FROM POR1 A1 inner join OPOR A2 on A1.DocEntry = A2.DocEntry Join OFPR on A1.ShipDate between F_RefDate and T_RefDate WHERE A1.ItemCode = T1.ItemCode AND A2.DocStatus = 'O' AND A2.CANCELED <> 'Y' --AND A1.ShipDate >= DATEADD(month, DATEDIFF(month, 0, DATEADD(month, 0, GETDATE())), 0) --AND A1.ShipDate <= DATEADD(month, DATEDIFF(month, 0, DATEADD(month, 12, GETDATE())), 0) ) as DeliverybyMonth PIVOT ( SUM(Quantity) FOR MonthName IN ([PO+00],[PO+01],[PO+02],[PO+03],[PO+04],[PO+05],[PO+06],[PO+07],[PO+08],[PO+09],[PO+10],[PO+11],[PO+12]) --[1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]) )AS [OnOrderperMonth]) Z6 OUTER APPLY (SELECT * FROM ( SELECT --MONTH(A1.DocDate)[MonthInt], case --This logic will look at sales order created in past including this fin month when OFPR.AbsEntry<=@Period then 'SO+' + '00' --This logic will look at sales order created in month +1 excluding this fin month ??? when OFPR.AbsEntry=@Period+1 then 'SO+' + '01' when OFPR.AbsEntry=@Period+2 then 'SO+' + '02' when OFPR.AbsEntry=@Period+3 then 'SO+' + '03' when OFPR.AbsEntry=@Period+4 then 'SO+' + '04' when OFPR.AbsEntry=@Period+5 then 'SO+' + '05' when OFPR.AbsEntry=@Period+6 then 'SO+' + '06' when OFPR.AbsEntry=@Period+7 then 'SO+' + '07' when OFPR.AbsEntry=@Period+8 then 'SO+' + '08' when OFPR.AbsEntry=@Period+9 then 'SO+' + '09' when OFPR.AbsEntry=@Period+10 then 'SO+' + '10' when OFPR.AbsEntry=@Period+11 then 'SO+' + '11' when OFPR.AbsEntry=@Period+12 then 'SO+' + '12' end as [MonthName] ,(A1.OpenQty)[Quantity] -- ,A2.ItemCode FROM RDR1 A1 inner join ORDR A2 on A1.DocEntry = A2.DocEntry Join OFPR on A1.ShipDate between F_RefDate and T_RefDate WHERE A1.ItemCode = T1.ItemCode AND A2.DocStatus = 'O' AND A2.CANCELED <> 'Y' --AND A1.ShipDate >= DATEADD(month, DATEDIFF(month, 0, DATEADD(month, 0, GETDATE())), 0) --AND A1.ShipDate <= DATEADD(month, DATEDIFF(month, 0, DATEADD(month, 12, GETDATE())), 0) AND A2.cardcode NOT IN ('ZT01','ZK01')) as OrdersByMonth PIVOT ( SUM(Quantity) FOR MonthName IN ([SO+00],[SO+01],[SO+02],[SO+03],[SO+04],[SO+05],[SO+06],[SO+07],[SO+08],[SO+09],[SO+10],[SO+11],[SO+12]) --[1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]) )AS [OpenSalesOrderperMonth]) Z7 WHERE t1.InvntItem='Y' and t2.itmsgrpnam not Like '%%discount%%' and t2.itmsgrpnam not Like '%%transport%%' and t2.itmsgrpnam not Like '%%packaging material%%' and t2.itmsgrpnam not Like '%%other%%' and t2.itmsgrpnam not Like '%%kingfish%%' and t2.itmsgrpnam not Like '%%outside stock%%' and t2.itmsgrpnam not Like '%%pallets%%' and t2.itmsgrpnam not Like '%%fixed assets%%' and t4.firmname not Like '%%zoutside stock%%' SET ARITHABORT ON SET ANSI_WARNINGS ON

10 |10000 characters needed characters left characters exceeded

Hi Ebie,

Several bits of the query have been commented out with double hyphens (--). This type of comment however comments out everything on the same line.

Because you have not formatted the query as code, it is unclear which parts of the query have been commented out.

Would you please edit your question, and format the query as code (you can use the code button)?

Regards,

Johan

0
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Ebie Conrad Jan 11, 2017 at 12:49 PM
0

hi

I use only Query manager or query generator.

i do not know about formatting it as code, what is used to do that?

Share
10 |10000 characters needed characters left characters exceeded
Ebie Conrad Jan 11, 2017 at 01:05 PM
0

sap-query.txt

I have uploaded a text file, hope this can help?


sap-query.txt (12.8 kB)
Show 1 Share
10 |10000 characters needed characters left characters exceeded

This helps, thanks

1
Ebie Conrad Feb 03, 2017 at 12:43 PM
0

Hi

I found the problem, our Posting Periods were not defined in the Modules, Administration, system Initialisation.

Share
10 |10000 characters needed characters left characters exceeded