******************Kalenderwochen in richtigen Rehenfolge(12Monate+15Monate)

Select 

 XWEEK,

Cast(datepart( YEAR, XDATE)  As varchar(5)) As YEAR,

Cast(datepart( YEAR, XDATE)  As varchar(5)) + ' / KW' + Cast(XWEEK As varchar(5)) As KW1

From IS_Cal

Where 

XDATE >= DATEADD(month, DATEDIFF(month, 0, DATEADD(month, -12, GetDate() )), 0) /* letzten 12 Monate */

and  XDATE <= DATEADD(month, DATEDIFF(month, 0, DATEADD(month, 15, GetDate() )), 0) /* nächsten 15 Monate */

Group  By Cast(datepart( YEAR, XDATE)  As varchar(5)) + ' / KW' + Cast(XWEEK As varchar(5)), XWEEK ,Cast(datepart( YEAR, XDATE)  As varchar(5)) 

Order By Cast(datepart( YEAR, XDATE)  As varchar(5)) ,XWEEK 

******************Z2************************

select

distinct ord.NAME,

ord.PPARTS,

pos.EPREIS,

(pos.EPREIS * ord.PPARTS) as Verkaufsvolumen,

cal.CPPFULL Herstellkosten, 

CASE WHEN cal.CPPFULL = 0 THEN 0

   ELSE (pos.EPREIS * ord.PPARTS) / cal.CPPFULL

END   as Zwischenrechnung

from or_order ord inner join pa_posit pos on  ( ord.name = pos.POSTNAME )

inner join OR_CALC cal on  ( ord.name = cal.NAME )

******************************************

select

--ord.NAME As OrderName,

--op.name As OpName,

--op.etime,

--ord.PPARTS,

--pos.EPREIS,

(pos.EPREIS * ord.PPARTS) as Verkaufsvolumen,

--cal.CPPFULL Herstellkosten, 

--CASE WHEN cal.CPPFULL = 0 THEN 0

  -- ELSE (pos.EPREIS * ord.PPARTS) / cal.CPPFULL

--END   as Zwischenrechnung,

c.XWEEK,

Cast(datepart(YEAR, c.XDATE)  Asvarchar(5))AsYEAR,

Cast(datepart( YEAR, c.XDATE)  As varchar(5)) + ' / KW' + Cast(c.XWEEK As varchar(5)) As KW1

from or_order ord inner join pa_posit pos on  ( ord.name = pos.POSTNAME )

inner join OR_CALC cal on  ( ord.name = cal.NAME )

inner join OR_OP op on  ( ord.no = op.orno )

inner join IS_CAL c on c.XDATE= op.ETIME

where op.SEQNUM =(select max(SEQNUM) from or_op where or_op.orno = ord.no and coalesce(or_op.ETIME,0)<>0) and

c.XDATE >=DATEADD(month,DATEDIFF(month, 0,DATEADD(month,-12,GetDate())), 0)/* letzten 12 Monate */

and  c.XDATE <=DATEADD(month,DATEDIFF(month, 0,DATEADD(month, 15,GetDate())), 0)/* nächsten 15 Monate */

Group  By Cast(datepart( YEAR, c.XDATE)  As varchar(5)) + ' / KW' + Cast(c.XWEEK As varchar(5)), c.XWEEK ,Cast(datepart( YEAR, c.XDATE)  As varchar(5)),(pos.EPREIS * ord.PPARTS)

--Group  By  Cast(datepart( YEAR, c.XDATE)  As varchar(5)) + ' / KW' + Cast(c.XWEEK As varchar(5)),c.XDATE, c.XWEEK ,pos.EPREIS,ord.PPARTS

Order By Cast(datepart( YEAR, c.XDATE)  As varchar(5)) ,c.XWEEK 

**************************

Select

a.Jahr + ' / KW' + a.kw As Jahr_KW,

SUM(a.z1) As Z1,

SUM(a.z2) As Z2,

SUM(a.z3) As Z3,

SUM(a.z4) As Z4,

SUM(a.z5) As Z5,

SUM(a.z6) As Z6,

SUM(a.z7) As Z7,

SUM(a.z8) As Z8,

SUM(a.z9) As Z9,

SUM(a.z10) As Z10,

SUM(a.z11) As Z11,

SUM(a.z12) As Z12,

SUM(a.z13) As Z13,

SUM(a.z14) As Z14,

a.jahr, --nur zum sortieren

a.kw --nur zum sortieren

From BAN_Auswertung a

Group By Jahr + ' / KW' + kw, a.jahr, a.kw

Order By Cast(a.jahr As integer), Cast(a.kw As integer

***********************************

select

Cast(datepart( YEAR, c.XDATE)  As varchar(5)) As JAHR,

Cast(c.XWEEK As varchar(5)) As KW,

Case WHEN cal.CPPFULL = 0 Then 0 Else ((ord.PPARTS * pos.EPREIS) / cal.CPPFULL) *((op.PTR * wp.SCOST0) +(op.PPARTS * wp.GCOST)) END As Z1

from or_order ord inner join pa_posit pos on  (ord.name = pos.POSTNAME)

inner join OR_CALC cal on  (ord.name = cal.NAME)

inner join OR_OP op on  (ord.no = op.orno)

inner join IS_CAL c on (c.XDATE = op.ETIME)

inner join WP_MA1 wp on (wp.no = op.PWPLACE)

where op.SEQNUM =(select max(SEQNUM) from or_op where or_op.orno = ord.no and coalesce(or_op.ETIME,0)<>0) and

c.XDATE >=DATEADD(month,DATEDIFF(month, 0,DATEADD(month,-12,GetDate())), 0)/* letzten 12 Monate */

and  c.XDATE <=DATEADD(month,DATEDIFF(month, 0,DATEADD(month, 15,GetDate())), 0)/* nächsten 15 Monate */

Group  By Cast(c.XWEEK As varchar(5)), c.XWEEK ,Cast(datepart( YEAR, c.XDATE)  As varchar(5)),cal.CPPFULL, ord.PPARTS, pos.EPREIS,op.PTR,wp.SCOST0,wp.GCOST,op.PPARTS

Order By Cast(datepart( YEAR, c.XDATE)  As varchar(5)) ,c.XWEEK 

*********************************

select 

ord.NAME As Auftrag,

ord.PPARTS,

pos.EPREIS,

(pos.EPREIS * ord.PPARTS) as VerkaufswertFA,

pap.TXTIDENT,

pap.TXTNUMMER,

pap.PAPPRT2,

ord.STATUS,

pos.POSNO,

pap.PAPERTYP,

pos.postno,

pos.postyp,

ord.no,

Cast(datepart( YEAR, c.XDATE)  As varchar(5)) As JAHR,

Cast(c.XWEEK As varchar(5)) As KW,

Case WHEN cal.CPPFULL = 0 Then 0 Else ((ord.PPARTS * pos.EPREIS) / cal.CPPFULL) *((op.PTR * wp.SCOST0) +(op.PPARTS * wp.GCOST)) END As Z1

from or_order ord

inner join pa_posit pos

inner join pa_paper pap on (pap.PANO = pos.PANO and pap.IDENT in(1,101) ) on (( ord.name = pos.POSTNAME or ord.name like pos.POSTNAME + '#%' ) and pos.postname not like '%#%' )

inner join OR_CALC cal on  (ord.name = cal.NAME)

inner join OR_OP op on  (ord.no = op.orno)

inner join IS_CAL c on (c.XDATE = op.ETIME)

inner join WP_MA1 wp on (wp.no = op.PWPLACE)

where ord.EINLAST in(0,1) and ord.status <> 2 

and pap.txtident <>'Abruf-Bestätigung'

and pap.txtident <> 'Rahmenauftrag'

and pap.txtident <>'Abrufbestätigung'

and op.SEQNUM =(select max(SEQNUM) from or_op where or_op.orno = ord.no and coalesce(or_op.ETIME,0)<>0) and

c.XDATE >=DATEADD(month,DATEDIFF(month, 0,DATEADD(month,-12,GetDate())), 0)/* letzten 12 Monate */

and  c.XDATE <=DATEADD(month,DATEDIFF(month, 0,DATEADD(month, 15,GetDate())), 0)/* nächsten 15 Monate */

Order By Cast(datepart( YEAR, c.XDATE)  As varchar(5)) ,c.XWEEK 

**************************************

select

ord.NAME As Auftrag,

op.NAME,

op.descr,

wp.cell,

wp.NAME,

pos.postname,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'FB' and op2.no =op.no) as z2FB,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'MT' and op2.no =op.no) as z2MT,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'IP' and op2.no =op.no) as z2IP,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'UK' and op2.no =op.no) as z2uk,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'allgemein' and op2.no =op.no) as z2allgemein

from or_order ord

inner join pa_posit pos on (( ord.name = pos.POSTNAME or ord.name like pos.POSTNAME + '#%' ) and pos.postname not like '%#%' )

inner join pa_paper pap on (pap.PANO = pos.PANO and pap.IDENT in(1,101) ) 

inner join OR_OP op on  (ord.no = op.orno)

inner join WP_MA1 wp on (wp.no = op.PWPLACE)

where ord.EINLAST in(0,1) and ord.status <> 2 

and pap.txtident <>'Abruf-Bestätigung'

and pap.txtident <> 'Rahmenauftrag'

and pap.txtident <>'Abrufbestätigung'

*********************************

select 

Prod.Auftrag,

sum(z2FB) as z2FB,

sum(z2MT) as z2MT,

sum(z2IP) as z2IP,

sum(z2UK) as z2UK, 

sum(z2Allgemein) as z2Allgemein,

Prod.PPARTS,

Prod.EPREIS,

Prod.VerkaufswertFA,

Prod.TXTIDENT,

Prod.TXTNUMMER,

Prod.PAPPRT2,

Prod.STATUS,

Prod.POSNO,

Prod.PAPERTYP,

Prod.postno,

Prod.postyp,

Prod.no,

Prod.delivery,

Prod.dat04,

Prod.etime,

Prod.JAHR,

Prod.KW

From(

select     —Subselect damit man im Hauptselect summieren kann

ord.name as Auftrag,

ord.PPARTS as PPARTS,

pos.EPREIS as EPREIS,

(pos.EPREIS * ord.PPARTS) as VerkaufswertFA,

pap.TXTIDENT as TXTIDENT,

pap.TXTNUMMER as TXTNUMMER,

pap.PAPPRT2 as PAPPRT2,

ord.STATUSasSTATUS,

pos.POSNO as POSNO,

pap.PAPERTYP as PAPERTYP,

pos.postno as postno,

pos.postyp as postyp,

ord.no as no,

ord.delivery as delivery,

fag.dat04 as dat04,

op.etime as etime,

Cast(datepart( YEAR, c.XDATE)  As varchar(5)) As JAHR,

Cast(c.XWEEK As varchar(5)) As KW,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'FB' and op2.no =op.no) as z2FB,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'MT' and op2.no =op.no) as z2MT,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'IP' and op2.no =op.no) as z2IP,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'UK' and op2.no =op.no) as z2UK,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'allgemein' and op2.no =op.no) as z2Allgemein

from or_order ord

inner join pa_posit pos on (( ord.name = pos.POSTNAME or ord.name like pos.POSTNAME + '#%' ) and pos.postname not like '%#%' )

inner join pa_paper pap on (pap.PANO = pos.PANO and pap.IDENT in(1,101) ) 

inner join OR_OP op on  (ord.no = op.orno)

inner join WP_MA1 wp on (wp.no = op.PWPLACE)

left join FAG_DETAIL fag on (fag.FKNO = ord.no)

left join OR_CALC cal on  (ord.name = cal.NAME)

inner join IS_CAL c on (c.XDATE = fag.dat04 or c.XDATE = op.ETIME or  c.xdate = GETDATE())

where ord.EINLAST in(0,1) and ord.status <> 2 

and pap.txtident <>'Abruf-Bestätigung'

and pap.txtident <> 'Rahmenauftrag'

and pap.txtident <>'Abrufbestätigung'

and c.XDATE >=DATEADD(month,DATEDIFF(month, 0,DATEADD(month,-12,GetDate())), 0)/* letzten 12 Monate */

and  c.XDATE <=DATEADD(month,DATEDIFF(month, 0,DATEADD(month, 15,GetDate())), 0)/* nächsten 15 Monate */

) as Prod

Group by Prod.Auftrag, Prod.PPARTS, Prod.EPREIS, Prod.VerkaufswertFA, Prod.TXTIDENT, Prod.TXTNUMMER,

Prod.PAPPRT2, Prod.STATUS, Prod.POSNO, Prod.PAPERTYP, Prod.postno, Prod.postyp, Prod.no, Prod.delivery, Prod.JAHR, Prod.KW, Prod.dat04, Prod.etime

Order by Cast(datepart( YEAR, Prod.JAHR)  As varchar(5)) ,Prod.KW

*********************

select 

DatePart(ISO_WEEK, Prod.xDate) AS KW,

DatePart(year, prod.xdate)as Jahr,

Prod.Auftrag,

Prod.PPARTS,

Prod.EPREIS,

Prod.VerkaufswertFA,

Prod.TXTIDENT,

Prod.TXTNUMMER,

sum(z2FB) as z2FB,

sum(z2MT) as z2MT,

sum(z2IP) as z2IP,

sum(z2UK) as z2UK, 

sum(z2Allgemein) as z2Allgemein,

Prod.PAPPRT2,

Prod.STATUS,

Prod.POSNO,

Prod.PAPERTYP,

Prod.postno,

Prod.postyp,

Prod.no,

Prod.delivery,

Prod.xDate

From(

select

ord.name as Auftrag,

ord.PPARTS as PPARTS,

pos.EPREIS as EPREIS,

(pos.EPREIS * ord.PPARTS) as VerkaufswertFA,

pap.TXTIDENT as TXTIDENT,

pap.TXTNUMMER as TXTNUMMER,

pap.PAPPRT2 as PAPPRT2,

ord.STATUSasSTATUS,

pos.POSNO as POSNO,

pap.PAPERTYP as PAPERTYP,

pos.postno as postno,

pos.postyp as postyp,

ord.no as no,

ord.delivery as delivery,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'FB' and op2.no =op.no) as z2FB,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'MT' and op2.no =op.no) as z2MT,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'IP' and op2.no =op.no) as z2IP,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'UK' and op2.no =op.no) as z2UK,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'allgemein' and op2.no =op.no) as z2Allgemein,

COALESCE(COALESCE(fag.dat04,(Select etime from or_op Where or_op.no =(Select  max(or_op.no) from or_op where or_op.orno = ord.no))), GetDate()) as xDate

from or_order ord

inner join pa_posit pos on (( ord.name = pos.POSTNAME or ord.name like pos.POSTNAME + '#%' ) and pos.postname not like '%#%' )

inner join pa_paper pap on (pap.PANO = pos.PANO and pap.IDENT in(1,101) ) 

inner join OR_OP op on  (ord.no = op.orno)

inner join WP_MA1 wp on (wp.no = op.PWPLACE)

left join FAG_DETAIL fag on (fag.FKNO = ord.no)

left join OR_CALC cal on  (ord.name = cal.NAME)

where ord.EINLAST in(0,1) and ord.status <> 2 

and pap.txtident <>'Abruf-Bestätigung'

and pap.txtident <> 'Rahmenauftrag'

and pap.txtident <>'Abrufbestätigung'

    and COALESCE(COALESCE(fag.dat04,(Select etime from or_op Where or_op.no =(Select  max(or_op.no) from or_op where or_op.orno = ord.no))), GetDate()) >= DATEADD(month, DATEDIFF(month, 0, DATEADD(month, -12, GetDate() )), 0) /* letzten 12 Monate */

and COALESCE(COALESCE(fag.dat04,(Select etime from or_op Where or_op.no =(Select  max(or_op.no) from or_op where or_op.orno = ord.no))), GetDate()) <= DATEADD(month, DATEDIFF(month, 0, DATEADD(month, 15, GetDate() )), 0) /* nächsten 15 Monate */

-- ord.name = 'FA 17-31515'

) as Prod

Group by Prod.Auftrag, Prod.PPARTS, Prod.EPREIS, Prod.VerkaufswertFA, Prod.TXTIDENT, Prod.TXTNUMMER,

Prod.PAPPRT2, Prod.STATUS, Prod.POSNO, Prod.PAPERTYP, Prod.postno, Prod.postyp, Prod.no, Prod.delivery,Prod.xDate

****************************

select 

DatePart(year, prod.xdate)as Jahr,

DatePart(ISO_WEEK, Prod.xDate) AS KW,

Prod.Auftrag,

Prod.PPARTS as Fertigungsmenge,

Prod.EPREIS as "Stueckpreis aus Faktura",

Prod.VerkaufswertFA,

Prod.TXTIDENT as "Belegtyp",

Prod.TXTNUMMER as "Faktura-Nummer",

Prod.POSNO,

Prod.Posart,

Prod.xDate as Auswertungszeitpunkt,

Prod.CPPFULL As "Interne Fertigungskosten" ,

sum(z2FB) as "Kosten Z2 Fachbereich FB",

CASE WHEN Prod.CPPFULL = 0 THEN 0 ELSE (Prod.VerkaufswertFA / Prod.CPPFULL) * sum(z2FB) END  as "Z2 Fachbereich FB", 

sum(z2MT) as "Kosten  Z2 Fachbereich MT",

CASE WHEN Prod.CPPFULL = 0 THEN 0 ELSE (Prod.VerkaufswertFA / Prod.CPPFULL) * sum(z2MT) END  as "Z2 Fachbereich MT", 

sum(z2IP) as "Kosten  Z2 Fachbereich IP",

CASE WHEN Prod.CPPFULL = 0 THEN 0 ELSE (Prod.VerkaufswertFA / Prod.CPPFULL) * sum(z2IP) END  as "Z2 Fachbereich IP", 

sum(z2UK) as "Kosten  Z2 Fachbereich UK", 

CASE WHEN Prod.CPPFULL = 0 THEN 0 ELSE (Prod.VerkaufswertFA / Prod.CPPFULL) * sum(z2UK) END  as "Z2 Fachbereich UK", 

sum(z2Allgemein) as "Kosten  Z2 Allgemein",

CASE WHEN Prod.CPPFULL = 0 THEN 0 ELSE (Prod.VerkaufswertFA / Prod.CPPFULL) * sum(z2Allgemein) END  as "Z2 Fachbereich Allgemein", 

Prod.PAPPRT2 as "Belegwert Statistik",

Prod.delivery as "Liefertermin FA",

Prod.STATUS,

Prod.POSNO,

Prod.PAPERTYP,

Prod.postno,

Prod.postyp,

Prod.Orno 

From(

select

ord.name as Auftrag,

ord.PPARTS as PPARTS,

pos.EPREIS as EPREIS,

(pos.EPREIS * ord.PPARTS) as VerkaufswertFA,

pap.TXTIDENT as TXTIDENT,

pap.TXTNUMMER as TXTNUMMER,

pap.PAPPRT2 as PAPPRT2,

ord.STATUSasSTATUS,

pos.POSNO as POSNO,

pap.PAPERTYP as PAPERTYP,

pos.postno as postno,

pos.postyp as postyp,

ord.no as Orno,

ord.delivery as delivery,

cal.CPPFULL ,

pos.POSART,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'FB' and op2.no =op.no) as z2FB,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'MT' and op2.no =op.no) as z2MT,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'IP' and op2.no =op.no) as z2IP,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'UK' and op2.no =op.no) as z2UK,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'allgemein' and op2.no =op.no) as z2Allgemein,

COALESCE(COALESCE(fag.dat04,(Select etime from or_op Where or_op.no =(Select  max(or_op.no) from or_op where or_op.orno = ord.no))), GetDate()) as xDate

from or_order ord

inner join pa_posit pos on (( ord.name = pos.POSTNAME or ord.name like pos.POSTNAME + '#%' ) and pos.postname not like '%#%' )

inner join pa_paper pap on (pap.PANO = pos.PANO and pap.IDENT in(1,101) ) 

inner join OR_OP op on  (ord.no = op.orno)

inner join WP_MA1 wp on (wp.no = op.PWPLACE)

left join FAG_DETAIL fag on (fag.FKNO = ord.no)

left join OR_CALC cal on  (ord.name = cal.NAME)

where ord.EINLAST in(0,1) and ord.status <> 2 

and pap.txtident <>'Abruf-Bestätigung'

and pap.txtident <> 'Rahmenauftrag'

and pap.txtident <>'Abrufbestätigung'

    and COALESCE(COALESCE(fag.dat04,(Select etime from or_op Where or_op.no =(Select  max(or_op.no) from or_op where or_op.orno = ord.no))), GetDate()) >= DATEADD(month, DATEDIFF(month, 0, DATEADD(month, -12, GetDate() )), 0) /* letzten 12 Monate */

and COALESCE(COALESCE(fag.dat04,(Select etime from or_op Where or_op.no =(Select  max(or_op.no) from or_op where or_op.orno = ord.no))), GetDate()) <= DATEADD(month, DATEDIFF(month, 0, DATEADD(month, 15, GetDate() )), 0) /* nächsten 15 Monate */

) as Prod

Group by Prod.Auftrag, Prod.PPARTS, Prod.EPRE

************************

select 

DatePart(year, prod.xdate)as Jahr,

DatePart(ISO_WEEK, Prod.xDate) AS KW,

Prod.Auftrag,

Prod.PPARTS as Fertigungsmenge,

Prod.EPREIS as "Stueckpreis aus Faktura",

Prod.VerkaufswertFA,

Prod.TXTIDENT as "Belegtyp",

Prod.TXTNUMMER as "Faktura-Nummer",

Prod.POSNO,

Prod.Posart,

Prod.xDate as Auswertungszeitpunkt,

Prod.CPPFULL As "Interne Fertigungskosten" ,

Prod.STATUS,

Prod.PAPERTYP,

Prod.postno,

Prod.postyp,

Prod.Orno ,

sum(z2FB) as "Kosten Z2 Fachbereich FB",

CASE WHEN Prod.CPPFULL = 0 THEN 0 ELSE (Prod.VerkaufswertFA / Prod.CPPFULL) * sum(z2FB) END  as "Z2 Fachbereich FB", 

sum(z2MT) as "Kosten  Z2 Fachbereich MT",

CASE WHEN Prod.CPPFULL = 0 THEN 0 ELSE (Prod.VerkaufswertFA / Prod.CPPFULL) * sum(z2MT) END  as "Z2 Fachbereich MT", 

sum(z2IP) as "Kosten  Z2 Fachbereich IP",

CASE WHEN Prod.CPPFULL = 0 THEN 0 ELSE (Prod.VerkaufswertFA / Prod.CPPFULL) * sum(z2IP) END  as "Z2 Fachbereich IP", 

sum(z2UK) as "Kosten  Z2 Fachbereich UK", 

CASE WHEN Prod.CPPFULL = 0 THEN 0 ELSE (Prod.VerkaufswertFA / Prod.CPPFULL) * sum(z2UK) END  as "Z2 Fachbereich UK", 

sum(z2Allgemein) as "Kosten  Z2 Allgemein",

CASE WHEN Prod.CPPFULL = 0 THEN 0 ELSE (Prod.VerkaufswertFA / Prod.CPPFULL) * sum(z2Allgemein) END  as "Z2 Fachbereich Allgemein", 

Prod.PAPPRT2 as "Belegwert Statistik",

Prod.delivery as "Liefertermin FA",

    PROD.CDATE,--Kontrolle des letzeten Laufs

    PROD.CHDATE --Kontrolle des letzeten Laufs

,( 

  (CASE WHEN Prod.CPPFULL = 0 THEN 0 ELSE (Prod.VerkaufswertFA / Prod.CPPFULL) * sum(z2FB) END) +(CASE WHEN Prod.CPPFULL = 0 THEN 0 ELSE (Prod.VerkaufswertFA / Prod.CPPFULL) * sum(z2MT) END )

  +(CASE WHEN Prod.CPPFULL = 0 THEN 0 ELSE (Prod.VerkaufswertFA / Prod.CPPFULL) * sum(z2IP) END ) +(CASE WHEN Prod.CPPFULL = 0 THEN 0 ELSE (Prod.VerkaufswertFA / Prod.CPPFULL) * sum(z2UK) END)

  + CASE WHEN Prod.CPPFULL = 0 THEN 0 ELSE (Prod.VerkaufswertFA / Prod.CPPFULL) * sum(z2Allgemein) END

) as KontrollSumme,

----START Z3----

Prod.CPFULLPROD,

Prod.VerkaufswertFA as "Z3 #1Verkaufsvolumen des Auftrages",

(CASE WHEN Prod.CRMAT = 0 and Prod.CPMAT = 0 THEN 0

  WHEN Prod.CRMAT = 0 and Prod.CPMAT <> 0 THEN Prod.CPMAT

ELSE Prod.CRMAT END)as "Z3#4geplanteMaterialkosten" , 

PROD.CPOUT as "Z3 #5Fremdvergabe Sollkosten",

sum(z2FB) as "Kosten Z3 Fachbereich FB",  --gleicher Wert wie Z2

sum(z2MT) as "Kosten  Z3 Fachbereich MT", --gleicher Wert wie Z2

sum(z2IP) as "Kosten  Z3 Fachbereich IP", --gleicher Wert wie Z2

sum(z2UK) as "Kosten  Z3 Fachbereich UK", --gleicher Wert wie Z2

sum(z2Allgemein) as "Kosten  Z3 Allgemein" --gleicher Wert wie Z2

--Ermittlung: "Z3 Geplante Wertschöpfung Fachbereich FB" usw --> Berechnung per Plugin

----END Z3----

From(

select

ord.name as Auftrag,

ord.PPARTS as PPARTS,

pos.EPREIS as EPREIS,

(pos.EPREIS * ord.PPARTS) as VerkaufswertFA,

pap.TXTIDENT as TXTIDENT,

pap.TXTNUMMER as TXTNUMMER,

pap.PAPPRT2 as PAPPRT2,

ord.STATUSasSTATUS,

pos.POSNO as POSNO,

pap.PAPERTYP as PAPERTYP,

pos.postno as postno,

pos.postyp as postyp,

ord.no as Orno,

ord.delivery as delivery,

   cal.CPPFULL ,

   cal.CDATE,

   cal.CHDATE,

   pos.POSART,

   ---Z3----

cal.CPOUT,

cal.CRMAT,

cal.CPMAT,

cal.CPFULLPROD,

   ---Z3----

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'FB' and op2.no =op.no) as z2FB,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'MT' and op2.no =op.no) as z2MT,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'IP' and op2.no =op.no) as z2IP,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'UK' and op2.no =op.no) as z2UK,

(select  ((op2.PTR/3600 * wp2.SCOST0) +(op2.PPARTS *(op2.pte/3600) * wp2.GCOST)) from or_op op2 inner join wp_ma1 wp2 on (op2.PWPLACE = wp2.no) where wp2.cell = 'allgemein' and op2.no =op.no) as z2Allgemein,

COALESCE(COALESCE(fag.dat04,(Select etime from or_op Where or_op.no =(Select  max(or_op.no) from or_op where or_op.orno = ord.no))), GetDate()) as xDate

from or_order ord

inner join pa_posit pos on (( ord.name = pos.POSTNAME or ord.name like pos.POSTNAME + '#%' ) and pos.postname not like '%#%' )

inner join pa_paper pap on (pap.PANO = pos.PANO and pap.IDENT in(1,101) ) 

inner join OR_OP op on  (ord.no = op.orno)

inner join WP_MA1 wp on (wp.no = op.PWPLACE)

left join FAG_DETAIL fag on (fag.FKNO = ord.no)

left join OR_CALC cal on  (ord.name = cal.NAME)

where ord.EINLAST in(0,1) and ord.status <> 2 

and pap.txtident <>'Abruf-Bestätigung'

and pap.txtident <> 'Rahmenauftrag'

and pap.txtident <>'Abrufbestätigung'

    and COALESCE(COALESCE(fag.dat04,(Select etime from or_op Where or_op.no =(Select  max(or_op.no) from or_op where or_op.orno = ord.no))), GetDate()) >= DATEADD(month, DATEDIFF(month, 0, DATEADD(month, -12, GetDate() )), 0) /* letzten 12 Monate */

and COALESCE(COALESCE(fag.dat04,(Select etime from or_op Where or_op.no =(Select  max(or_op.no) from or_op where or_op.orno = ord.no))), GetDate()) <= DATEADD(month, DATEDIFF(month, 0, DATEADD(month, 15, GetDate() )), 0) /* nächsten 15 Monate */

--and ord.name = 'FA 16-29749'

) as Prod

Group by Prod.Auftrag, Prod.PPARTS, Prod.EPREIS, Prod.VerkaufswertFA, Prod.TXTIDENT, Prod.TXTNUMMER,

Prod.PAPPRT2, Prod.STATUS, Prod.POSNO, Prod.PAPERTYP, Prod.postno, Prod.postyp, Prod.ORNO, Prod.delivery,Prod.xDate,Prod.CPPFULL,prod.POSART

,PROD.CDATE,PROD.CHDATE

,PROD.CPOUT,P

*****************************************