- Katılım
- 16 Haziran 2007
- Mesajlar
- 56
- Excel Vers. ve Dili
- 2003 türçe
Bu formülü nasıl kısalta bilirim değişken kullanamadığım için her şeyi mükerer yapıyorum ama yapamıyorum artık veri alamaz hale geldi excel bekliyorda bekliyor
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [AA_KAPIKOLU_KOL_RENK_05] AS
SELECT LEFT(CODE,3)+' '+SUBSTRING(CODE,12,2) +' '+ SUBSTRING(CODE,22,1)AS 'KOL+RENK', LEFT(CODE,3)+' '+SUBSTRING(CODE,12,2) +' '+ SUBSTRING(CODE,22,1) +' '+ SUBSTRING(CODE,5,3) AS 'KOL+RENK+AYNA' ,SUBSTRING(CODE,5,3) AS 'AYNA', SUBSTRING(CODE,5,9) AS 'AYNA+MODEL+RENK',CODE AS 'STOK KODU', NAME AS 'STOK ADI',
MONTE=ISNULL ((SELECT (SUM (CASE WHEN UNITSETREF=9 THEN (LG_029_01_STLINE.AMOUNT*2) ELSE LG_029_01_STLINE.AMOUNT END))
FROM LOGO.dbo.LG_029_01_STLINE LG_029_01_STLINE, LOGO.dbo.LG_029_ITEMS LG_029_ITEMS
WHERE ANATABLO.CODE=LG_029_ITEMS.CODE AND LG_029_01_STLINE.STOCKREF = LG_029_ITEMS.LOGICALREF AND (LG_029_ITEMS.CARDTYPE=12)AND (LG_029_ITEMS.STGRPCODE Like '25%') AND LG_029_01_STLINE.TRCODE=13 AND (LG_029_01_STLINE.DATE_>={ts '2009-05-01 00:00:00'})
GROUP BY LG_029_ITEMS.CODE HAVING COUNT(LG_029_01_STLINE.STOCKREF)>0),0.00),
STOK=ISNULL ((SELECT (SUM (CASE WHEN LG_029_01_STLINE.TRCODE IN (6,7,8,51,11,12) THEN (LG_029_01_STLINE.AMOUNT*-1) ELSE LG_029_01_STLINE.AMOUNT END))
FROM LOGO.dbo.LG_029_01_STLINE LG_029_01_STLINE, LOGO.dbo.LG_029_ITEMS LG_029_ITEMS
WHERE ANATABLO.CODE=LG_029_ITEMS.CODE AND LG_029_01_STLINE.STOCKREF = LG_029_ITEMS.LOGICALREF AND (LG_029_ITEMS.CARDTYPE=12)AND (LG_029_ITEMS.STGRPCODE Like '25%')
GROUP BY LG_029_ITEMS.CODE HAVING COUNT(LG_029_01_STLINE.STOCKREF)>0),0.00),
SIPARIS=ISNULL ((SELECT Sum(AMOUNT-SHIPPEDAMOUNT)
FROM LOGO.dbo.LG_029_01_ORFICHE LG_029_01_ORFICHE, LOGO.dbo.LG_029_01_ORFLINE LG_029_01_ORFLINE, LOGO.dbo.LG_029_CLCARD LG_029_CLCARD, LOGO.dbo.LG_029_ITEMS LG_029_ITEMS
WHERE ANATABLO.CODE=LG_029_ITEMS.CODE AND LG_029_01_ORFLINE.ORDFICHEREF = LG_029_01_ORFICHE.LOGICALREF AND LG_029_01_ORFLINE.CLIENTREF = LG_029_CLCARD.LOGICALREF AND LG_029_01_ORFLINE.STOCKREF = LG_029_ITEMS.LOGICALREF AND ((LG_029_01_ORFLINE.STATUS=4) AND (LG_029_01_ORFLINE.CLOSED=0) AND (LG_029_ITEMS.CARDTYPE=12) AND (LG_029_ITEMS.STGRPCODE Like '25%') AND (LG_029_CLCARD.CARDTYPE=1))
GROUP BY LG_029_ITEMS.CODE
HAVING (Sum(AMOUNT-SHIPPEDAMOUNT)>0)AND COUNT(LG_029_01_ORFLINE.ORDFICHEREF)>0),0.00),
KALAN =ISNULL ((SELECT (SUM (CASE WHEN LG_029_01_STLINE.TRCODE IN (6,7,8,51,11,12) THEN (LG_029_01_STLINE.AMOUNT*-1) ELSE LG_029_01_STLINE.AMOUNT END))
FROM LOGO.dbo.LG_029_01_STLINE LG_029_01_STLINE, LOGO.dbo.LG_029_ITEMS LG_029_ITEMS
WHERE ANATABLO.CODE=LG_029_ITEMS.CODE AND LG_029_01_STLINE.STOCKREF = LG_029_ITEMS.LOGICALREF AND (LG_029_ITEMS.CARDTYPE=12)AND (LG_029_ITEMS.STGRPCODE Like '25%')
GROUP BY LG_029_ITEMS.CODE HAVING COUNT(LG_029_01_STLINE.STOCKREF)>0),0.00)-
ISNULL ((SELECT Sum(AMOUNT-SHIPPEDAMOUNT)
FROM LOGO.dbo.LG_029_01_ORFICHE LG_029_01_ORFICHE, LOGO.dbo.LG_029_01_ORFLINE LG_029_01_ORFLINE, LOGO.dbo.LG_029_CLCARD LG_029_CLCARD, LOGO.dbo.LG_029_ITEMS LG_029_ITEMS
WHERE ANATABLO.CODE=LG_029_ITEMS.CODE AND LG_029_01_ORFLINE.ORDFICHEREF = LG_029_01_ORFICHE.LOGICALREF AND LG_029_01_ORFLINE.CLIENTREF = LG_029_CLCARD.LOGICALREF AND LG_029_01_ORFLINE.STOCKREF = LG_029_ITEMS.LOGICALREF AND ((LG_029_01_ORFLINE.STATUS=4) AND (LG_029_01_ORFLINE.CLOSED=0) AND (LG_029_ITEMS.CARDTYPE=12) AND (LG_029_ITEMS.STGRPCODE Like '25%') AND (LG_029_CLCARD.CARDTYPE=1))
GROUP BY LG_029_ITEMS.CODE
HAVING (Sum(AMOUNT-SHIPPEDAMOUNT)>0)AND COUNT(LG_029_01_ORFLINE.ORDFICHEREF)>0),0.00),
SATIS=ISNULL ((SELECT (SUM (CASE WHEN LG_029_01_STLINE.TRCODE =3 THEN (LG_029_01_STLINE.AMOUNT*-1) ELSE LG_029_01_STLINE.AMOUNT END))
FROM LOGO.dbo.LG_029_01_STLINE LG_029_01_STLINE, LOGO.dbo.LG_029_ITEMS LG_029_ITEMS
WHERE ANATABLO.CODE=LG_029_ITEMS.CODE AND LG_029_01_STLINE.STOCKREF = LG_029_ITEMS.LOGICALREF AND (LG_029_ITEMS.CARDTYPE=12)AND (LG_029_ITEMS.STGRPCODE Like '25%') AND LG_029_01_STLINE.TRCODE IN(3,8)
GROUP BY LG_029_ITEMS.CODE HAVING COUNT(LG_029_01_STLINE.STOCKREF)>0),0.00) ,
KALANADET =(ISNULL ((SELECT (SUM (CASE WHEN LG_029_01_STLINE.TRCODE IN (6,7,8,51,11,12) THEN (LG_029_01_STLINE.AMOUNT*-1) ELSE LG_029_01_STLINE.AMOUNT END))
FROM LOGO.dbo.LG_029_01_STLINE LG_029_01_STLINE, LOGO.dbo.LG_029_ITEMS LG_029_ITEMS
WHERE ANATABLO.CODE=LG_029_ITEMS.CODE AND LG_029_01_STLINE.STOCKREF = LG_029_ITEMS.LOGICALREF AND (LG_029_ITEMS.CARDTYPE=12)AND (LG_029_ITEMS.STGRPCODE Like '25%')
GROUP BY LG_029_ITEMS.CODE HAVING COUNT(LG_029_01_STLINE.STOCKREF)>0),0.00)-
ISNULL ((SELECT Sum(AMOUNT-SHIPPEDAMOUNT)
FROM LOGO.dbo.LG_029_01_ORFICHE LG_029_01_ORFICHE, LOGO.dbo.LG_029_01_ORFLINE LG_029_01_ORFLINE, LOGO.dbo.LG_029_CLCARD LG_029_CLCARD, LOGO.dbo.LG_029_ITEMS LG_029_ITEMS
WHERE ANATABLO.CODE=LG_029_ITEMS.CODE AND LG_029_01_ORFLINE.ORDFICHEREF = LG_029_01_ORFICHE.LOGICALREF AND LG_029_01_ORFLINE.CLIENTREF = LG_029_CLCARD.LOGICALREF AND LG_029_01_ORFLINE.STOCKREF = LG_029_ITEMS.LOGICALREF AND ((LG_029_01_ORFLINE.STATUS=4) AND (LG_029_01_ORFLINE.CLOSED=0) AND (LG_029_ITEMS.CARDTYPE=12) AND (LG_029_ITEMS.STGRPCODE Like '25%') AND (LG_029_CLCARD.CARDTYPE=1))
GROUP BY LG_029_ITEMS.CODE
HAVING (Sum(AMOUNT-SHIPPEDAMOUNT)>0)AND COUNT(LG_029_01_ORFLINE.ORDFICHEREF)>0),0.00))* case WHEN UNITSETREF=9 THEN 2 ELSE 1 END
FROM LG_029_ITEMS AS ANATABLO
WHERE (CARDTYPE=12) AND (STGRPCODE Like '25%')
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [AA_KAPIKOLU_KOL_RENK_05] AS
SELECT LEFT(CODE,3)+' '+SUBSTRING(CODE,12,2) +' '+ SUBSTRING(CODE,22,1)AS 'KOL+RENK', LEFT(CODE,3)+' '+SUBSTRING(CODE,12,2) +' '+ SUBSTRING(CODE,22,1) +' '+ SUBSTRING(CODE,5,3) AS 'KOL+RENK+AYNA' ,SUBSTRING(CODE,5,3) AS 'AYNA', SUBSTRING(CODE,5,9) AS 'AYNA+MODEL+RENK',CODE AS 'STOK KODU', NAME AS 'STOK ADI',
MONTE=ISNULL ((SELECT (SUM (CASE WHEN UNITSETREF=9 THEN (LG_029_01_STLINE.AMOUNT*2) ELSE LG_029_01_STLINE.AMOUNT END))
FROM LOGO.dbo.LG_029_01_STLINE LG_029_01_STLINE, LOGO.dbo.LG_029_ITEMS LG_029_ITEMS
WHERE ANATABLO.CODE=LG_029_ITEMS.CODE AND LG_029_01_STLINE.STOCKREF = LG_029_ITEMS.LOGICALREF AND (LG_029_ITEMS.CARDTYPE=12)AND (LG_029_ITEMS.STGRPCODE Like '25%') AND LG_029_01_STLINE.TRCODE=13 AND (LG_029_01_STLINE.DATE_>={ts '2009-05-01 00:00:00'})
GROUP BY LG_029_ITEMS.CODE HAVING COUNT(LG_029_01_STLINE.STOCKREF)>0),0.00),
STOK=ISNULL ((SELECT (SUM (CASE WHEN LG_029_01_STLINE.TRCODE IN (6,7,8,51,11,12) THEN (LG_029_01_STLINE.AMOUNT*-1) ELSE LG_029_01_STLINE.AMOUNT END))
FROM LOGO.dbo.LG_029_01_STLINE LG_029_01_STLINE, LOGO.dbo.LG_029_ITEMS LG_029_ITEMS
WHERE ANATABLO.CODE=LG_029_ITEMS.CODE AND LG_029_01_STLINE.STOCKREF = LG_029_ITEMS.LOGICALREF AND (LG_029_ITEMS.CARDTYPE=12)AND (LG_029_ITEMS.STGRPCODE Like '25%')
GROUP BY LG_029_ITEMS.CODE HAVING COUNT(LG_029_01_STLINE.STOCKREF)>0),0.00),
SIPARIS=ISNULL ((SELECT Sum(AMOUNT-SHIPPEDAMOUNT)
FROM LOGO.dbo.LG_029_01_ORFICHE LG_029_01_ORFICHE, LOGO.dbo.LG_029_01_ORFLINE LG_029_01_ORFLINE, LOGO.dbo.LG_029_CLCARD LG_029_CLCARD, LOGO.dbo.LG_029_ITEMS LG_029_ITEMS
WHERE ANATABLO.CODE=LG_029_ITEMS.CODE AND LG_029_01_ORFLINE.ORDFICHEREF = LG_029_01_ORFICHE.LOGICALREF AND LG_029_01_ORFLINE.CLIENTREF = LG_029_CLCARD.LOGICALREF AND LG_029_01_ORFLINE.STOCKREF = LG_029_ITEMS.LOGICALREF AND ((LG_029_01_ORFLINE.STATUS=4) AND (LG_029_01_ORFLINE.CLOSED=0) AND (LG_029_ITEMS.CARDTYPE=12) AND (LG_029_ITEMS.STGRPCODE Like '25%') AND (LG_029_CLCARD.CARDTYPE=1))
GROUP BY LG_029_ITEMS.CODE
HAVING (Sum(AMOUNT-SHIPPEDAMOUNT)>0)AND COUNT(LG_029_01_ORFLINE.ORDFICHEREF)>0),0.00),
KALAN =ISNULL ((SELECT (SUM (CASE WHEN LG_029_01_STLINE.TRCODE IN (6,7,8,51,11,12) THEN (LG_029_01_STLINE.AMOUNT*-1) ELSE LG_029_01_STLINE.AMOUNT END))
FROM LOGO.dbo.LG_029_01_STLINE LG_029_01_STLINE, LOGO.dbo.LG_029_ITEMS LG_029_ITEMS
WHERE ANATABLO.CODE=LG_029_ITEMS.CODE AND LG_029_01_STLINE.STOCKREF = LG_029_ITEMS.LOGICALREF AND (LG_029_ITEMS.CARDTYPE=12)AND (LG_029_ITEMS.STGRPCODE Like '25%')
GROUP BY LG_029_ITEMS.CODE HAVING COUNT(LG_029_01_STLINE.STOCKREF)>0),0.00)-
ISNULL ((SELECT Sum(AMOUNT-SHIPPEDAMOUNT)
FROM LOGO.dbo.LG_029_01_ORFICHE LG_029_01_ORFICHE, LOGO.dbo.LG_029_01_ORFLINE LG_029_01_ORFLINE, LOGO.dbo.LG_029_CLCARD LG_029_CLCARD, LOGO.dbo.LG_029_ITEMS LG_029_ITEMS
WHERE ANATABLO.CODE=LG_029_ITEMS.CODE AND LG_029_01_ORFLINE.ORDFICHEREF = LG_029_01_ORFICHE.LOGICALREF AND LG_029_01_ORFLINE.CLIENTREF = LG_029_CLCARD.LOGICALREF AND LG_029_01_ORFLINE.STOCKREF = LG_029_ITEMS.LOGICALREF AND ((LG_029_01_ORFLINE.STATUS=4) AND (LG_029_01_ORFLINE.CLOSED=0) AND (LG_029_ITEMS.CARDTYPE=12) AND (LG_029_ITEMS.STGRPCODE Like '25%') AND (LG_029_CLCARD.CARDTYPE=1))
GROUP BY LG_029_ITEMS.CODE
HAVING (Sum(AMOUNT-SHIPPEDAMOUNT)>0)AND COUNT(LG_029_01_ORFLINE.ORDFICHEREF)>0),0.00),
SATIS=ISNULL ((SELECT (SUM (CASE WHEN LG_029_01_STLINE.TRCODE =3 THEN (LG_029_01_STLINE.AMOUNT*-1) ELSE LG_029_01_STLINE.AMOUNT END))
FROM LOGO.dbo.LG_029_01_STLINE LG_029_01_STLINE, LOGO.dbo.LG_029_ITEMS LG_029_ITEMS
WHERE ANATABLO.CODE=LG_029_ITEMS.CODE AND LG_029_01_STLINE.STOCKREF = LG_029_ITEMS.LOGICALREF AND (LG_029_ITEMS.CARDTYPE=12)AND (LG_029_ITEMS.STGRPCODE Like '25%') AND LG_029_01_STLINE.TRCODE IN(3,8)
GROUP BY LG_029_ITEMS.CODE HAVING COUNT(LG_029_01_STLINE.STOCKREF)>0),0.00) ,
KALANADET =(ISNULL ((SELECT (SUM (CASE WHEN LG_029_01_STLINE.TRCODE IN (6,7,8,51,11,12) THEN (LG_029_01_STLINE.AMOUNT*-1) ELSE LG_029_01_STLINE.AMOUNT END))
FROM LOGO.dbo.LG_029_01_STLINE LG_029_01_STLINE, LOGO.dbo.LG_029_ITEMS LG_029_ITEMS
WHERE ANATABLO.CODE=LG_029_ITEMS.CODE AND LG_029_01_STLINE.STOCKREF = LG_029_ITEMS.LOGICALREF AND (LG_029_ITEMS.CARDTYPE=12)AND (LG_029_ITEMS.STGRPCODE Like '25%')
GROUP BY LG_029_ITEMS.CODE HAVING COUNT(LG_029_01_STLINE.STOCKREF)>0),0.00)-
ISNULL ((SELECT Sum(AMOUNT-SHIPPEDAMOUNT)
FROM LOGO.dbo.LG_029_01_ORFICHE LG_029_01_ORFICHE, LOGO.dbo.LG_029_01_ORFLINE LG_029_01_ORFLINE, LOGO.dbo.LG_029_CLCARD LG_029_CLCARD, LOGO.dbo.LG_029_ITEMS LG_029_ITEMS
WHERE ANATABLO.CODE=LG_029_ITEMS.CODE AND LG_029_01_ORFLINE.ORDFICHEREF = LG_029_01_ORFICHE.LOGICALREF AND LG_029_01_ORFLINE.CLIENTREF = LG_029_CLCARD.LOGICALREF AND LG_029_01_ORFLINE.STOCKREF = LG_029_ITEMS.LOGICALREF AND ((LG_029_01_ORFLINE.STATUS=4) AND (LG_029_01_ORFLINE.CLOSED=0) AND (LG_029_ITEMS.CARDTYPE=12) AND (LG_029_ITEMS.STGRPCODE Like '25%') AND (LG_029_CLCARD.CARDTYPE=1))
GROUP BY LG_029_ITEMS.CODE
HAVING (Sum(AMOUNT-SHIPPEDAMOUNT)>0)AND COUNT(LG_029_01_ORFLINE.ORDFICHEREF)>0),0.00))* case WHEN UNITSETREF=9 THEN 2 ELSE 1 END
FROM LG_029_ITEMS AS ANATABLO
WHERE (CARDTYPE=12) AND (STGRPCODE Like '25%')
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO