Bu kodlar daha önce başkası tarafından yazılmış kodlardır. Benden sadece birim eklememi istediler. Birimi ekledim fakat veri çektiği tablolar için sorguya ' ' koydum çünkü kolon sayısını eşitlemem gerekiyordu. Daha sonra birimleri getirdim fakat bu koyduğun boşluklardan dolayı ürünün bir birimli hali birde boşluklu hali geliyor bunları nasıl birleştirebilirm???
IF OBJECT_ID('RAP_URPROG') IS NOT NULL DROP TABLE RAP_URPROG
CREATE TABLE RAP_URPROG (OK2 VARCHAR(10),CINS VARCHAR(1),BIRIM VARCHAR(20),EBAT VARCHAR(20),PIC FLOAT,PDIS FLOAT,P FLOAT,PSPF FLOAT,PYM FLOAT,URETIM FLOAT,K6 FLOAT,K1 FLOAT,K2 FLOAT, K4 FLOAT,KY FLOAT,KSPF FLOAT,KALITE VARCHAR(1),FIRMA VARCHAR(10),URHAT VARCHAR(19),URUNSERI VARCHAR(5))
INSERT INTO RAP_URPROG
SELECT OZELKOD2, CINSS,' ', EBAT,ICPROG,DISPROG,PROGRAM,SPF,YM,0,0,0,0,0,0,0,'','',FABRIKA AS URHAT,KODU FROM ENG_PROGRAM_GIRIS WHERE YIL=2012 AND AY=10
INSERT INTO RAP_URPROG
SELECT IT.SPECODE2 AS OK2, LEFT(IT.CODE,1) AS CINS , UN.NAME AS BIRIM, (SELECT DEGER FROM ENG_TANIMLAMA WHERE GRUP=1 AND C+KOD=LEFT(IT.CODE,3)) AS EBAT,
0,0,0,0,0,SLT.AMOUNT AS URETIM,0,0,0,0,0,0,RIGHT(IT.CODE,1) AS KALITE,LEFT(SL.NAME,6) AS FIRMA,XT8.UHATTI AS URHAT,SUBSTRING(IT.CODE,12,5)
FROM LG_042_ITEMS IT WITH (NOLOCK),LG_042_UNITSETF UN WITH (NOLOCK), LG_042_01_SLTRANS SLT WITH (NOLOCK), LG_042_01_SERILOTN SL WITH (NOLOCK), LG_XT008001_042 XT8 WITH (NOLOCK)
WHERE IT.LOGICALREF=SLT.ITEMREF AND IT.UNITSETREF=UN.LOGICALREF AND SL.LOGICALREF=SLT.SLREF AND SL.CODE=XT8.PALETNO AND FICHETYPE=13 AND IT.CODE NOT LIKE '%.0' AND IT.SPECODE2 NOT LIKE '00.%' AND MONTH(SLT.DATE_)=10
AND XT8.UHATTI IN ('T','3','3.2')
yasak RAP_URPROG SET KSPF=URETIM/*, K6=0,K1=0,K2=0,K4=0,KY=0*/ WHERE FIRMA IN ('IF.001')
yasak RAP_URPROG SET FIRMA=(SELECT SPECODE2 FROM LG_042_CLCARD WHERE CODE=RAP_URPROG.FIRMA)
yasak RAP_URPROG SET K6=URETIM WHERE KALITE IN ('1','9','6','8') AND FIRMA='SAT.DIS'
yasak RAP_URPROG SET K1=URETIM WHERE KALITE IN ('1','9','6','8') AND FIRMA='SAT.ICP'
yasak RAP_URPROG SET K2=URETIM WHERE KALITE='2'
yasak RAP_URPROG SET K4=URETIM WHERE KALITE='4'
yasak RAP_URPROG SET KY=URETIM WHERE KALITE IN ('Y','7')
yasak RAP_URPROG SET URHAT=1 WHERE URHAT BETWEEN 'A' AND 'D'
yasak RAP_URPROG SET URHAT=2 WHERE URHAT BETWEEN 'E' AND 'H'
yasak RAP_URPROG SET URHAT=3 WHERE URHAT ='30' OR URHAT ='3.2'
yasak RAP_URPROG SET URHAT=4 WHERE URHAT ='40' OR URHAT = 'P'
DELETE FROM RAP_URPROG WHERE OK2 NOT LIKE '01.%' AND OK2 NOT LIKE '03.%'
SELECT OK2,CINS,BIRIM,EBAT,URHAT,SUM(PIC) AS PIC,SUM(PDIS) AS PDIS, SUM(P) AS P, SUM(PSPF) AS PSPF, SUM(PYM) AS PYM, SUM(URETIM) AS URETIM, SUM(K6) AS K6, SUM(K1) AS K1, SUM(K2) AS K2, SUM(K4) AS K4, SUM(KY) AS KY, SUM(KSPF) AS KSPF,COUNT(URUNSERI) AS URUNCESIT ,(SELECT COUNT(DISTINCT(URUNSERI)) FROM RAP_URPROG WHERE OK2=R.OK2 AND CINS=R.CINS AND EBAT=R.EBAT AND URHAT=R.URHAT AND P>0) AS PROGRAMCESIT ,(SELECT COUNT(DISTINCT(URUNSERI)) FROM RAP_URPROG WHERE OK2=R.OK2 AND CINS=R.CINS AND EBAT=R.EBAT AND URHAT=R.URHAT AND URETIM>0) AS URETIMCESIT FROM RAP_URPROG R GROUP BY OK2,CINS,EBAT,URHAT,BIRIM ORDER BY EBAT
