Skip to main content

Timeout beim Aufruf eines Designer Berichtes

Permanently deleted user

Rolf Mettler

Hallo zusammen

Habe bei einem Kunden ein bisschen einen aufwendigen Budgetbericht im Einsatz. Der lief jedoch bis letzte Woche einwandfrei. Wenn ich jetzt das Layout via dem Layouter oder über den Button im Menü starten will, kommt eine Timeout Fehlermeldung.

Führe ich die Query im SAP oder im Query Analyzer durch, dauert diese zwar fast 50 Sekunden aber Sie läuft.

Gibt es Einstellungen im SAP, im Designer oder auf dem SQL Server 2005 welcher dieses Problem löst? DANKE und Gruss Rolf

Hier die QUERY:
/* Query SCSBudgetDaten */

SELECT * FROM [dbo].[@BUDGETSCS] T0

/* Query SCSProfitCenterDatenproMonat */

SELECT


/* Line Data*/

T0.DocEntry,

T0.LineNum,

T0.ItemCode,

T0.Dscription,

T0.Quantity,

T0.ShipDate,

T0.OpenQty,

T0.Price,

T0.WhsCode,

T0.SlpCode As LineSlpCode,

T4.SlpName As LineSlpName,

T0.GrssProfit,

T0.LineTotal,

T0.LineTotal * ((100.0-ISNULL(T1.DiscPrcnt,0))/100.0) As LineTotalWithDocDiscPrcnt,

T0.VatSum,

T2.ItmsGrpCod,

T3.ItmsGrpNam As ItemGroupName,

T0.AcctCode As LineAcctCode,

T6.AcctName As LineAcctName,

T0.Project,

T0.OcrCode As LinePCC,

T5.OcrName As LineProfitCenter,

T5.Direct As Direktbuchung,

T6.OverCode As AcctProfitCenterCode,



/* Document Data*/

T1.DocNum,

T1.ObjType,

T1.CardCode,

T1.CardName,

T8.City,

T8.Country,

T8.ZipCode,

T8.GroupCode AS BusinessPartnerGroupCode,

T9.GroupName As BusinessPartnerGroupName,

T1.DocDate,

T1.DocDueDate,

T1.DocStatus,

T1.DocTotal,

T1.DocCur,


/* Business Parter Data*/

T10.SlpCode As BusinessPartnerSlp,

T10.SlpName As BusinessPartnerSlpName


INTO #PCBerichtPoolInfo



/*Select * from #PCBerichtPoolInfo */


FROM PCH1 T0

INNER JOIN OPCH T1 ON T0.DocEntry=T1.DocEntry

LEFT OUTER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode

LEFT OUTER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod

LEFT OUTER JOIN OSLP T4 ON T0.SlpCode = T4.SlpCode

LEFT OUTER JOIN OOCR T5 ON T0.OcrCode = T5.OcrCode

LEFT OUTER JOIN OACT T6 ON T0.AcctCode = T6.AcctCode

LEFT OUTER JOIN OOCR T7 ON T6.OverCode = T7.OcrCode

INNER JOIN OCRD T8 ON T1.CardCode = T8.CardCode

INNER JOIN OCRG T9 ON T8.GroupCode = T9.GroupCode

LEFT OUTER JOIN OSLP T10 ON T8.SlpCode = T10.SlpCode


UNION ALL




SELECT


/* Line Data*/

T20.DocEntry,

T20.LineNum,

T20.ItemCode,

T20.Dscription,

T20.Quantity,

T20.ShipDate,

T20.OpenQty,

T20.Price *-1 As Price,

T20.WhsCode,

T20.SlpCode As LineSlpCode,

T24.SlpName As LineSlpName,

T20.LineTotal *-1As GrssProfit,

T20.LineTotal *-1,

T20.LineTotal *-1* ((100.0-ISNULL(T21.DiscPrcnt,0))/100.0) As LineTotalWithDocDiscPrcnt,

T20.VatSum As VatSum,

T22.ItmsGrpCod,

T23.ItmsGrpNam As ItemGroupName,

T20.AcctCode As LineAcctCode,

T26.AcctName As LineAcctName,

T20.Project,

T20.OcrCode As LinePCC,

T25.OcrName As LineProfitCenter,

T25.Direct As Direktbuchung,

T26.OverCode As AcctProfitCenterCode,



/* Document Data*/

T21.DocNum,

T21.ObjType,

T21.CardCode,

T21.CardName,

T28.City,

T28.ZipCode,

T28.Country,

T28.GroupCode AS BusinessPartnerGroupCode,

T29.GroupName As BusinessPartnerGroupName,

T21.DocDate,

T21.DocDueDate,

T21.DocStatus,

T21.DocTotal *-1 As DocTotal,

T21.DocCur,


/* Business Parter Data*/

T30.SlpCode As BusinessPartnerSlp,

T30.SlpName As BusinessPartnerSlpName






FROM RPC1 T20

INNER JOIN ORPC T21 ON T20.DocEntry=T21.DocEntry

LEFT OUTER JOIN OITM T22 ON T20.ItemCode = T22.ItemCode

LEFT OUTER JOIN OITB T23 ON T22.ItmsGrpCod = T23.ItmsGrpCod

LEFT OUTER JOIN OSLP T24 ON T20.SlpCode = T24.SlpCode

LEFT OUTER JOIN OOCR T25 ON T20.OcrCode = T25.OcrCode

LEFT OUTER JOIN OACT T26 ON T20.AcctCode = T26.AcctCode

LEFT OUTER JOIN OOCR T27 ON T26.OverCode = T27.OcrCode

INNER JOIN OCRD T28 ON T21.CardCode = T28.CardCode

INNER JOIN OCRG T29 ON T28.GroupCode = T29.GroupCode

LEFT OUTER JOIN OSLP T30 ON T28.SlpCode = T30.SlpCode


UNION ALL


SELECT

/* Line Data*/

T40.TransId as DocEntry,

T40.Line_ID as LineNum,

T40.Account as ItemCode,

'Debit' as Dscription,

'0' as Quantity,

T40.DueDate as ShipDate,

'0' as OpenQty,

T40.Debit As Price,

'0' as WhsCode,

'0' As LineSlpCode,

'SlpName' As LineSlpName,

T40.Debit As GrssProfit,

T40.Debit As LineTotal,

'0' As LineTotalWithDocDiscPrcnt,

'0' As VarSum,

'0' as ItmsGrpCod,

'0' As ItemGroupName,

T40.Account As LineAcctCode,

T46.AcctName As LineAcctName,

T40.Project,

T40.ProfitCode As LinePCC,

T45.OcrName As LineProfitCenter,

T45.Direct As Direktbuchung,

T46.OverCode As AcctProfitCenterCode,



/* Document Data*/

T41.TransId as DocNum,

T41.ObjType,

'0' as CardCode,

'0' asCardName,

'0' as City,

'0' as ZipCode,

'0' as Country,

'0' AS BusinessPartnerGroupCode,

'0' As BusinessPartnerGroupName,

T41.RefDate as DocDate,

T41.DueDate as DocDueDate,

'0' as DocStatus,

'0' as DocTotal,

'0' as DocCur,


/* Business Parter Data*/

'0' As BusinessPartnerSlp,

'SlpName' As BusinessPartnerSlpName



FROM JDT1 T40

INNER JOIN OJDT T41 ON T40.TransId=T41.TransId

LEFT OUTER JOIN OOCR T45 ON T40.ProfitCode = T45.OcrCode

LEFT OUTER JOIN OACT T46 ON T40.Account = T46.AcctCode

LEFT OUTER JOIN OOCR T47 ON T46.OverCode = T47.OcrCode


Where T41.TransType='30'AND T40.Debit<>0


UNION ALL


SELECT

/* Line Data*/

T40.TransId as DocEntry,

T40.Line_ID as LineNum,

T40.Account as ItemCode,

'Credit' as Dscription,

'0' as Quantity,

T40.DueDate as ShipDate,

'0' as OpenQty,

T40.Credit As Price,

'0' as WhsCode,

'0' As LineSlpCode,

'SlpName' As LineSlpName,

T40.Credit * -1 As GrssProfit,

T40.Credit * -1 As LineTotal,

'0' As LineTotalWithDocDiscPrcnt,

'0' As VarSum,

'0' as ItmsGrpCod,

'0' As ItemGroupName,

T40.Account As LineAcctCode,

T46.AcctName As LineAcctName,

T40.Project,

T40.ProfitCode As LinePCC,

T45.OcrName As LineProfitCenter,

T45.Direct As Direktbuchung,

T46.OverCode As AcctProfitCenterCode,



/* Document Data*/

T41.TransId as DocNum,

T41.ObjType,

'0' as CardCode,

'0' as CardName,

'0' as City,

'0' as ZipCode,

'0' as Country,

'0' AS BusinessPartnerGroupCode,

'0' As BusinessPartnerGroupName,

T41.RefDate as DocDate,

T41.DueDate as DocDueDate,

'0' as DocStatus,

'0' as DocTotal,

'0' as DocCur,


/* Business Parter Data*/

'0' As BusinessPartnerSlp,

'0' As BusinessPartnerSlpName



FROM JDT1 T40

INNER JOIN OJDT T41 ON T40.TransId=T41.TransId

LEFT OUTER JOIN OOCR T45 ON T40.ProfitCode = T45.OcrCode

LEFT OUTER JOIN OACT T46 ON T40.Account = T46.AcctCode

LEFT OUTER JOIN OOCR T47 ON T46.OverCode = T47.OcrCode


Where T41.TransType='30'AND T40.Credit<>0


ORDER BY 1


/*Zusammenstellung PC*/

Select T0.LinePCC,T0.Direktbuchung, sum(T0.LineTotal) as Total

Into #PCBerichtAllePC

from #PCBerichtPoolInfo T0

group by T0.LinePCC,T0.Direktbuchung


/*Zusammenstellung Januar*/

Select T0.LinePCC, DATEPART(MM,T0.DocDate)as Monat1 , sum(T0.LineTotal) as Total1

Into #PCBerichtM1

from #PCBerichtPoolInfo T0

where DATEPART(MM,T0.DocDate)=1

group by T0.LinePCC, DATEPART(MM,T0.DocDate)


/*Zusammenstellung Februar*/

Select T0.LinePCC, DATEPART(MM,T0.DocDate)as Monat2 , sum(T0.LineTotal) as Total2

Into #PCBerichtM2

from #PCBerichtPoolInfo T0

where DATEPART(MM,T0.DocDate)=2

group by T0.LinePCC, DATEPART(MM,T0.DocDate)


/*Zusammenstellung März*/

Select T0.LinePCC, DATEPART(MM,T0.DocDate)as Monat3 , sum(T0.LineTotal) as Total3

Into #PCBerichtM3

from #PCBerichtPoolInfo T0

where DATEPART(MM,T0.DocDate)=3

group by T0.LinePCC, DATEPART(MM,T0.DocDate)


/*Zusammenstellung April*/

Select T0.LinePCC, DATEPART(MM,T0.DocDate)as Monat4 , sum(T0.LineTotal) as Total4

Into #PCBerichtM4

from #PCBerichtPoolInfo T0

where DATEPART(MM,T0.DocDate)=4

group by T0.LinePCC, DATEPART(MM,T0.DocDate)


/*Zusammenstellung Mai*/

Select T0.LinePCC, DATEPART(MM,T0.DocDate)as Monat5 , sum(T0.LineTotal) as Total5

Into #PCBerichtM5

from #PCBerichtPoolInfo T0

where DATEPART(MM,T0.DocDate)=5

group by T0.LinePCC, DATEPART(MM,T0.DocDate)


/*Zusammenstellung Juni*/

Select T0.LinePCC, DATEPART(MM,T0.DocDate)as Monat6 , sum(T0.LineTotal) as Total6

Into #PCBerichtM6

from #PCBerichtPoolInfo T0

where DATEPART(MM,T0.DocDate)=6

group by T0.LinePCC, DATEPART(MM,T0.DocDate)


/*Zusammenstellung Juli*/

Select T0.LinePCC, DATEPART(MM,T0.DocDate)as Monat7 , sum(T0.LineTotal) as Total7

Into #PCBerichtM7

from #PCBerichtPoolInfo T0

where DATEPART(MM,T0.DocDate)=7

group by T0.LinePCC, DATEPART(MM,T0.DocDate)


/*Zusammenstellung August*/

Select T0.LinePCC, DATEPART(MM,T0.DocDate)as Monat8 , sum(T0.LineTotal) as Total8

Into #PCBerichtM8

from #PCBerichtPoolInfo T0

where DATEPART(MM,T0.DocDate)=8

group by T0.LinePCC, DATEPART(MM,T0.DocDate)


/*Zusammenstellung September*/

Select T0.LinePCC, DATEPART(MM,T0.DocDate)as Monat9 , sum(T0.LineTotal) as Total9

Into #PCBerichtM9

from #PCBerichtPoolInfo T0

where DATEPART(MM,T0.DocDate)=9

group by T0.LinePCC, DATEPART(MM,T0.DocDate)


/*Zusammenstellung Oktober*/

Select T0.LinePCC, DATEPART(MM,T0.DocDate)as Monat10 , sum(T0.LineTotal) as Total10

Into #PCBerichtM10

from #PCBerichtPoolInfo T0

where DATEPART(MM,T0.DocDate)=10

group by T0.LinePCC, DATEPART(MM,T0.DocDate)


/*Zusammenstellung November*/

Select T0.LinePCC, DATEPART(MM,T0.DocDate)as Monat11 , sum(T0.LineTotal) as Total11

Into #PCBerichtM11

from #PCBerichtPoolInfo T0

where DATEPART(MM,T0.DocDate)=11

group by T0.LinePCC, DATEPART(MM,T0.DocDate)


/*Zusammenstellung Dezember*/

Select T0.LinePCC, DATEPART(MM,T0.DocDate)as Monat12 , sum(T0.LineTotal) as Total12

Into #PCBerichtM12

from #PCBerichtPoolInfo T0

where DATEPART(MM,T0.DocDate)=12

group by T0.LinePCC, DATEPART(MM,T0.DocDate)


Select T0.LinePCC, T0.Direktbuchung, T1.Total1, T2.Total2, T3.Total3, T4.Total4, T5.Total5, T6.Total6, T7.Total7, T8.Total8, T9.Total9, T10.Total10, T11.Total11, T12.Total12

from #PCBerichtAllePC T0

left outer Join #PCBerichtM1 T1 on T0.LinePCC=T1.LinePCC

left outer Join #PCBerichtM2 T2 on T0.LinePCC=T2.LinePCC

left outer Join #PCBerichtM3 T3 on T0.LinePCC=T3.LinePCC

left outer Join #PCBerichtM4 T4 on T0.LinePCC=T4.LinePCC

left outer Join #PCBerichtM5 T5 on T0.LinePCC=T5.LinePCC

left outer Join #PCBerichtM6 T6 on T0.LinePCC=T6.LinePCC

left outer Join #PCBerichtM7 T7 on T0.LinePCC=T7.LinePCC

left outer Join #PCBerichtM8 T8 on T0.LinePCC=T8.LinePCC

left outer Join #PCBerichtM9 T9 on T0.LinePCC=T9.LinePCC

left outer Join #PCBerichtM10 T10 on T0.LinePCC=T10.LinePCC

left outer Join #PCBerichtM11 T11 on T0.LinePCC=T11.LinePCC

left outer Join #PCBerichtM12 T12 on T0.LinePCC=T12.LinePCC

/* Query SCSProfitCenterDatenproMonatATR */

IF EXISTS


(SELECT * FROM [tempdb].[dbo].[sysobjects] WHERE name='##PCBerichtPoolInfo')


DROP Table ##PCBerichtPoolInfo


IF EXISTS


(SELECT * FROM [tempdb].[dbo].[sysobjects] WHERE name='##PCBerichtPCATInfo')


DROP Table ##PCBerichtPCATInfo


IF EXISTS


(SELECT * FROM [tempdb].[dbo].[sysobjects] WHERE name='##PCBerichtAllePC')


DROP Table ##PCBerichtAllePC


IF EXISTS


(SELECT * FROM [tempdb].[dbo].[sysobjects] WHERE name='##PCBerichtM1')


DROP Table ##PCBerichtM1


IF EXISTS


(SELECT * FROM [tempdb].[dbo].[sysobjects] WHERE name='##PCBerichtM2')


DROP Table ##PCBerichtM2


IF EXISTS


(SELECT * FROM [tempdb].[dbo].[sysobjects] WHERE name='##PCBerichtM3')


DROP Table ##PCBerichtM3


IF EXISTS


(SELECT * FROM [tempdb].[dbo].[sysobjects] WHERE name='##PCBerichtM4')


DROP Table ##PCBerichtM4


IF EXISTS


(SELECT * FROM [tempdb].[dbo].[sysobjects] WHERE name='##PCBerichtM5')


DROP Table ##PCBerichtM5


IF EXISTS


(SELECT * FROM [tempdb].[dbo].[sysobjects] WHERE name='##PCBerichtM6')


DROP Table ##PCBerichtM6



IF EXISTS


(SELECT * FROM [tempdb].[dbo].[sysobjects] WHERE name='##PCBerichtM7')


DROP Table ##PCBerichtM7


IF EXISTS


(SELECT * FROM [tempdb].[dbo].[sysobjects] WHERE name='##PCBerichtM8')


DROP Table ##PCBerichtM8



IF EXISTS


(SELECT * FROM [tempdb].[dbo].[sysobjects] WHERE name='##PCBerichtM9')


DROP Table ##PCBerichtM9



IF EXISTS


(SELECT * FROM [tempdb].[dbo].[sysobjects] WHERE name='##PCBerichtM10')


DROP Table ##PCBerichtM10



IF EXISTS


(SELECT * FROM [tempdb].[dbo].[sysobjects] WHERE name='##PCBerichtM11')


DROP Table ##PCBerichtM11



IF EXISTS


(SELECT * FROM [tempdb].[dbo].[sysobjects] WHERE name='##PCBerichtM12')


DROP Table ##PCBerichtM12



IF EXISTS


(SELECT * FROM [tempdb].[dbo].[sysobjects] WHERE name='##PCBerichtAufgeteilt')


DROP Table ##PCBerichtAufgeteilt


IF EXISTS


(SELECT * FROM [tempdb].[dbo].[sysobjects] WHERE name='##PCBerichtPoolInfo')


DROP Table ##PCBerichtPoolInfo






SELECT


/* Line Data*/

T0.DocEntry,

T0.LineNum,

T0.ItemCode,

T0.Dscription,

T0.Quantity,

T0.ShipDate,

T0.OpenQty,

T0.Price,

T0.WhsCode,

T0.SlpCode As LineSlpCode,

T4.SlpName As LineSlpName,

T0.GrssProfit,

T0.LineTotal,

T0.LineTotal * ((100.0-ISNULL(T1.DiscPrcnt,0))/100.0) As LineTotalWithDocDiscPrcnt,

T0.VatSum,

T2.ItmsGrpCod,

T3.ItmsGrpNam As ItemGroupName,

T0.AcctCode As LineAcctCode,

T6.AcctName As LineAcctName,

T0.Project,

T0.OcrCode As LinePCC,

T5.OcrName As LineProfitCenter,

T5.Direct As Direktbuchung,

T6.OverCode As AcctProfitCenterCode,



/* Document Data*/

T1.DocNum,

T1.ObjType,

T1.CardCode,

T1.CardName,

T8.City,

T8.Country,

T8.ZipCode,

T8.GroupCode AS BusinessPartnerGroupCode,

T9.GroupName As BusinessPartnerGroupName,

T1.DocDate,

T1.DocDueDate,

T1.DocStatus,

T1.DocTotal,

T1.DocCur,


/* Business Parter Data*/

T10.SlpCode As BusinessPartnerSlp,

T10.SlpName As BusinessPartnerSlpName


INTO ##PCBerichtPoolInfo



/*Select * from #PCBerichtPoolInfo */


FROM PCH1 T0

INNER JOIN OPCH T1 ON T0.DocEntry=T1.DocEntry

LEFT OUTER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode

LEFT OUTER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod

LEFT OUTER JOIN OSLP T4 ON T0.SlpCode = T4.SlpCode

LEFT OUTER JOIN OOCR T5 ON T0.OcrCode = T5.OcrCode

LEFT OUTER JOIN OACT T6 ON T0.AcctCode = T6.AcctCode

LEFT OUTER JOIN OOCR T7 ON T6.OverCode = T7.OcrCode

INNER JOIN OCRD T8 ON T1.CardCode = T8.CardCode

INNER JOIN OCRG T9 ON T8.GroupCode = T9.GroupCode

LEFT OUTER JOIN OSLP T10 ON T8.SlpCode = T10.SlpCode



UNION ALL




SELECT


/* Line Data*/

T20.DocEntry,

T20.LineNum,

T20.ItemCode,

T20.Dscription,

T20.Quantity,

T20.ShipDate,

T20.OpenQty,

T20.Price *-1 As Price,

T20.WhsCode,

T20.SlpCode As LineSlpCode,

T24.SlpName As LineSlpName,

T20.LineTotal *-1As GrssProfit,

T20.LineTotal *-1,

T20.LineTotal *-1* ((100.0-ISNULL(T21.DiscPrcnt,0))/100.0) As LineTotalWithDocDiscPrcnt,

T20.VatSum As VatSum,

T22.ItmsGrpCod,

T23.ItmsGrpNam As ItemGroupName,

T20.AcctCode As LineAcctCode,

T26.AcctName As LineAcctName,

T20.Project,

T20.OcrCode As LinePCC,

T25.OcrName As LineProfitCenter,

T25.Direct As Direktbuchung,

T26.OverCode As AcctProfitCenterCode,



/* Document Data*/

T21.DocNum,

T21.ObjType,

T21.CardCode,

T21.CardName,

T28.City,

T28.ZipCode,

T28.Country,

T28.GroupCode AS BusinessPartnerGroupCode,

T29.GroupName As BusinessPartnerGroupName,

T21.DocDate,

T21.DocDueDate,

T21.DocStatus,

T21.DocTotal *-1 As DocTotal,

T21.DocCur,


/* Business Parter Data*/

T30.SlpCode As BusinessPartnerSlp,

T30.SlpName As BusinessPartnerSlpName






FROM RPC1 T20

INNER JOIN ORPC T21 ON T20.DocEntry=T21.DocEntry

LEFT OUTER JOIN OITM T22 ON T20.ItemCode = T22.ItemCode

LEFT OUTER JOIN OITB T23 ON T22.ItmsGrpCod = T23.ItmsGrpCod

LEFT OUTER JOIN OSLP T24 ON T20.SlpCode = T24.SlpCode

LEFT OUTER JOIN OOCR T25 ON T20.OcrCode = T25.OcrCode

LEFT OUTER JOIN OACT T26 ON T20.AcctCode = T26.AcctCode

LEFT OUTER JOIN OOCR T27 ON T26.OverCode = T27.OcrCode

INNER JOIN OCRD T28 ON T21.CardCode = T28.CardCode

INNER JOIN OCRG T29 ON T28.GroupCode = T29.GroupCode

LEFT OUTER JOIN OSLP T30 ON T28.SlpCode = T30.SlpCode


UNION ALL


SELECT

/* Line Data*/

T40.TransId as DocEntry,

T40.Line_ID as LineNum,

T40.Account as ItemCode,

'Debit' as Dscription,

'0' as Quantity,

T40.DueDate as ShipDate,

'0' as OpenQty,

T40.Debit As Price,

'0' as WhsCode,

'0' As LineSlpCode,

'SlpName' As LineSlpName,

T40.Debit As GrssProfit,

T40.Debit As LineTotal,

'0' As LineTotalWithDocDiscPrcnt,

'0' As VarSum,

'0' as ItmsGrpCod,

'0' As ItemGroupName,

T40.Account As LineAcctCode,

T46.AcctName As LineAcctName,

T40.Project,

T40.ProfitCode As LinePCC,

T45.OcrName As LineProfitCenter,

T45.Direct As Direktbuchung,

T46.OverCode As AcctProfitCenterCode,



/* Document Data*/

T41.TransId as DocNum,

T41.ObjType,

'0' as CardCode,

'0' asCardName,

'0' as City,

'0' as ZipCode,

'0' as Country,

'0' AS BusinessPartnerGroupCode,

'0' As BusinessPartnerGroupName,

T41.RefDate as DocDate,

T41.DueDate as DocDueDate,

'0' as DocStatus,

'0' as DocTotal,

'0' as DocCur,


/* Business Parter Data*/

'0' As BusinessPartnerSlp,

'SlpName' As BusinessPartnerSlpName



FROM JDT1 T40

INNER JOIN OJDT T41 ON T40.TransId=T41.TransId

LEFT OUTER JOIN OOCR T45 ON T40.ProfitCode = T45.OcrCode

LEFT OUTER JOIN OACT T46 ON T40.Account = T46.AcctCode

LEFT OUTER JOIN OOCR T47 ON T46.OverCode = T47.OcrCode


Where T41.TransType='30'AND T40.Debit<>0 /*AND T46.ActType<>'N'*/


UNION ALL


SELECT

/* Line Data*/

T40.TransId as DocEntry,

T40.Line_ID as LineNum,

T40.Account as ItemCode,

'Credit' as Dscription,

'0' as Quantity,

T40.DueDate as ShipDate,

'0' as OpenQty,

T40.Credit As Price,

'0' as WhsCode,

'0' As LineSlpCode,

'SlpName' As LineSlpName,

T40.Credit * -1 As GrssProfit,

T40.Credit * -1 As LineTotal,

'0' As LineTotalWithDocDiscPrcnt,

'0' As VarSum,

'0' as ItmsGrpCod,

'0' As ItemGroupName,

T40.Account As LineAcctCode,

T46.AcctName As LineAcctName,

T40.Project,

T40.ProfitCode As LinePCC,

T45.OcrName As LineProfitCenter,

T45.Direct As Direktbuchung,

T46.OverCode As AcctProfitCenterCode,



/* Document Data*/

T41.TransId as DocNum,

T41.ObjType,

'0' as CardCode,

'0' as CardName,

'0' as City,

'0' as ZipCode,

'0' as Country,

'0' AS BusinessPartnerGroupCode,

'0' As BusinessPartnerGroupName,

T41.RefDate as DocDate,

T41.DueDate as DocDueDate,

'0' as DocStatus,

'0' as DocTotal,

'0' as DocCur,


/* Business Parter Data*/

'0' As BusinessPartnerSlp,

'0' As BusinessPartnerSlpName



FROM JDT1 T40

INNER JOIN OJDT T41 ON T40.TransId=T41.TransId

LEFT OUTER JOIN OOCR T45 ON T40.ProfitCode = T45.OcrCode

LEFT OUTER JOIN OACT T46 ON T40.Account = T46.AcctCode

LEFT OUTER JOIN OOCR T47 ON T46.OverCode = T47.OcrCode


Where T41.TransType='30'AND T40.Credit<>0 /*AND T46.ActType<>'N'*/


ORDER BY 1

/*Zusammenstellung alle AT mit %Verteilung*/

SELECT T1.OcrCode, T1.PrcCode, T1.PrcAmount

INTO ##PCBerichtPCATInfo

FROM OOCR T0

INNER JOIN OCR1 T1 ON T0.OcrCode = T1.OcrCode

/*WHERE T1.Direct ='n'*/


/*select * from ##PCBerichtPCATInfo T0*/





/*Zusammenstellung PC*/

Select T0.LinePCC,T1.PrcCode,T1.PrcAmount,T0.Direktbuchung, sum(T0.LineTotal) as Total

Into ##PCBerichtAllePC

from ##PCBerichtPoolInfo T0 inner join ocr1 t1 on T0.LinePCC=T1.OcrCode

group by T0.LinePCC, T1.PrcCode,T1.PrcAmount, T0.Direktbuchung

/*select * from ##PCBerichtAllePC T0*/


/*Zusammenstellung Januar*/

Select T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)as Monat1 , sum(T0.LineTotal) as Total1

Into ##PCBerichtM1

from ##PCBerichtPoolInfo T0 INNER JOIN OCR1 T1 ON T0.LinePCC=T1.OcrCode

where DATEPART(MM,T0.DocDate)=1

group by T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)


/*Zusammenstellung Februar*/

Select T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)as Monat2 , sum(T0.LineTotal) as Total2

Into ##PCBerichtM2

from ##PCBerichtPoolInfo T0 INNER JOIN OCR1 T1 ON T0.LinePCC=T1.OcrCode

where DATEPART(MM,T0.DocDate)=2

group by T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)


/*Zusammenstellung März*/

Select T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)as Monat3 , sum(T0.LineTotal) as Total3

Into ##PCBerichtM3

from ##PCBerichtPoolInfo T0 INNER JOIN OCR1 T1 ON T0.LinePCC=T1.OcrCode

where DATEPART(MM,T0.DocDate)=3

group by T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)


/*Zusammenstellung April*/

Select T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)as Monat4 , sum(T0.LineTotal) as Total4

Into ##PCBerichtM4

from ##PCBerichtPoolInfo T0 INNER JOIN OCR1 T1 ON T0.LinePCC=T1.OcrCode

where DATEPART(MM,T0.DocDate)=4

group by T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)


/*Zusammenstellung Mai*/

Select T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)as Monat5 , sum(T0.LineTotal) as Total5

Into ##PCBerichtM5

from ##PCBerichtPoolInfo T0 INNER JOIN OCR1 T1 ON T0.LinePCC=T1.OcrCode

where DATEPART(MM,T0.DocDate)=5

group by T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)


/*Zusammenstellung Juni*/

Select T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)as Monat6 , sum(T0.LineTotal) as Total6

Into ##PCBerichtM6

from ##PCBerichtPoolInfo T0 INNER JOIN OCR1 T1 ON T0.LinePCC=T1.OcrCode

where DATEPART(MM,T0.DocDate)=6

group by T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)


/*Zusammenstellung Juli*/

Select T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)as Monat7 , sum(T0.LineTotal) as Total7

Into ##PCBerichtM7

from ##PCBerichtPoolInfo T0 INNER JOIN OCR1 T1 ON T0.LinePCC=T1.OcrCode

where DATEPART(MM,T0.DocDate)=7

group by T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)


/*Zusammenstellung August*/

Select T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)as Monat8 , sum(T0.LineTotal) as Total8

Into ##PCBerichtM8

from ##PCBerichtPoolInfo T0 INNER JOIN OCR1 T1 ON T0.LinePCC=T1.OcrCode

where DATEPART(MM,T0.DocDate)=8

group by T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)


/*Zusammenstellung September*/

Select T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)as Monat9 , sum(T0.LineTotal) as Total9

Into ##PCBerichtM9

from ##PCBerichtPoolInfo T0 INNER JOIN OCR1 T1 ON T0.LinePCC=T1.OcrCode

where DATEPART(MM,T0.DocDate)=9

group by T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)


/*Zusammenstellung Oktober*/

Select T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)as Monat10 , sum(T0.LineTotal) as Total10

Into ##PCBerichtM10

from ##PCBerichtPoolInfo T0 INNER JOIN OCR1 T1 ON T0.LinePCC=T1.OcrCode

where DATEPART(MM,T0.DocDate)=10

group by T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)


/*Zusammenstellung November*/

Select T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)as Monat11 , sum(T0.LineTotal) as Total11

Into ##PCBerichtM11

from ##PCBerichtPoolInfo T0 INNER JOIN OCR1 T1 ON T0.LinePCC=T1.OcrCode

where DATEPART(MM,T0.DocDate)=11

group by T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)


/*Zusammenstellung Dezember*/

Select T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)as Monat12 , sum(T0.LineTotal) as Total12

Into ##PCBerichtM12

from ##PCBerichtPoolInfo T0 INNER JOIN OCR1 T1 ON T0.LinePCC=T1.OcrCode

where DATEPART(MM,T0.DocDate)=12

group by T0.LinePCC,T1.PrcCode,T1.PrcAmount, DATEPART(MM,T0.DocDate)


Select distinct T0.LinePCC,T0.PrcCode,T0.PrcAmount, T0.Direktbuchung,T1.Total1,(T1.Total1*T0.PrcAmount/100) as 'Total1aufg', T2.Total2,(T2.Total2*T0.PrcAmount/100) as 'Total2aufg', T3.Total3,(T3.Total3*T0.PrcAmount/100) as 'Total3aufg', T4.Total4,(T4.Total4*T0.PrcAmount/100) as 'Total4aufg', T5.Total5,(T5.Total5*T0.PrcAmount/100) as 'Total5aufg', T6.Total6,(T6.Total6*T0.PrcAmount/100) as 'Total6aufg', T7.Total7,(T7.Total7*T0.PrcAmount/100) as 'Total7aufg', T8.Total8,(T8.Total8*T0.PrcAmount/100) as 'Total8aufg', T9.Total9,(T9.Total9*T0.PrcAmount/100) as 'Total9aufg', T10.Total10,(T10.Total10*T0.PrcAmount/100) as 'Total10aufg', T11.Total11,(T11.Total11*T0.PrcAmount/100) as 'Total11aufg', T12.Total12,(T12.Total12*T0.PrcAmount/100) as 'Total12aufg'

into ##PCBerichtAufgeteilt

from ##PCBerichtAllePC T0

left outer Join ##PCBerichtM1 T1 on T0.LinePCC=T1.LinePCC

left outer Join ##PCBerichtM2 T2 on T0.LinePCC=T2.LinePCC

left outer Join ##PCBerichtM3 T3 on T0.LinePCC=T3.LinePCC

left outer Join ##PCBerichtM4 T4 on T0.LinePCC=T4.LinePCC

left outer Join ##PCBerichtM5 T5 on T0.LinePCC=T5.LinePCC

left outer Join ##PCBerichtM6 T6 on T0.LinePCC=T6.LinePCC

left outer Join ##PCBerichtM7 T7 on T0.LinePCC=T7.LinePCC

left outer Join ##PCBerichtM8 T8 on T0.LinePCC=T8.LinePCC

left outer Join ##PCBerichtM9 T9 on T0.LinePCC=T9.LinePCC

left outer Join ##PCBerichtM10 T10 on T0.LinePCC=T10.LinePCC

left outer Join ##PCBerichtM11 T11 on T0.LinePCC=T11.LinePCC

left outer Join ##PCBerichtM12 T12 on T0.LinePCC=T12.LinePCC


Select T0.PrcCode,sum(T0.Total1aufg)as '1', sum(T0.Total2aufg)as '2',sum(T0.Total3aufg)as '3',sum(T0.Total4aufg) as '4',sum(T0.Total5aufg) as '5',sum(T0.Total6aufg) as '6',sum(T0.Total7aufg) as '7',sum(T0.Total8aufg) as '8',sum(T0.Total9aufg) as '9',sum(T0.Total10aufg) as '10',sum(T0.Total11aufg) as '11',sum(T0.Total12aufg) as '12'

from ##PCBerichtAufgeteilt T0

where T0.Direktbuchung='n'

group by T0.PrcCode

Philipp Knecht

Hi Rolf

Oi oi oi...Habe jede Zeile genaustens analysiert ;)

Du kannst irgendwo im Kommentarteil folgendes eintragen:

/* @@TIMEOUT=[1000] */

hth

Rolf Mettler

Hi Phops

Sorry, dein langes Studium hat nichts gebracht. Siehe angehängte Fehlermeldung.


Gruss DANKE

Rolf

Attachment
[url=http://www.coresystems.ch/wp-content/../wp-content/forum-file-uploads/rolf-mettlermtf-ch/QueryError.doc]QueryError.doc[/url]

Philipp Knecht

Hoi Rolf

Werde ich mir bei nächster Gelegnheit genauer anschauen.

Gruss
0

Comments

0 comments

Please sign in to leave a comment.

Didn't find what you were looking for?

New post