sipariş raporu mu dedi biri?
tam benim uzmanlık alanım.
buyrun:
SELECT DISTINCT
S.DATE_ AS 'SİP.TAR.',
SD.DUEDATE AS 'TES.TAR.',
(CASE WHEN (DATEDIFF(DAY, SD.DUEDATE, GETDATE()) >0) THEN (DATEDIFF(DAY, SD.DUEDATE, GETDATE())) ELSE NULL END) AS 'GECİKME',
C.DEFINITION2 AS 'ÜNV.', C.TOWN AS 'RUT',
(CASE WHEN C.CITY!='ANKARA' THEN C.CITY END) AS İLİ
,SUM((SD.AMOUNT-SD.SHIPPEDAMOUNT)*(SD.LINENET/SD.AMOUNT)) AS 'TL.TOP.'
,SUM((SD.AMOUNT-SD.SHIPPEDAMOUNT)*SD.GROSSUINFO2) AS 'KG.TOP.'
,S.GENEXP1 AS 'NOT'
,SUM(CASE
WHEN (SD.STOCKREF=1) AND (SD.SHIPPEDAMOUNT=0) THEN SD.AMOUNT
WHEN (SD.STOCKREF=1) AND (SD.AMOUNT<>SD.SHIPPEDAMOUNT) THEN SD.AMOUNT-SD.SHIPPEDAMOUNT
ELSE NULL
END) AS 'ABC'
,SUM(CASE
WHEN (SD.STOCKREF=2) AND (SD.SHIPPEDAMOUNT=0) THEN SD.AMOUNT
WHEN (SD.STOCKREF=2) AND (SD.AMOUNT<>SD.SHIPPEDAMOUNT)THEN SD.AMOUNT-SD.SHIPPEDAMOUNT
ELSE NULL
END) AS 'XYZ'
FROM
LG_001_01_ORFICHE S
LEFT JOIN LG_001_CLCARD C ON C.LOGICALREF=S.CLIENTREF
LEFT JOIN LG_001_01_ORFLINE SD ON S.LOGICALREF=SD.ORDFICHEREF
WHERE
SD.AMOUNT>SD.SHIPPEDAMOUNT AND -- SİPARİŞTEN FAZLA SEVKEDİLENLERİ ELEMEK İÇİN
(SD.LINETYPE <> '2')AND -- SATIR İNDİRİMİ OLAN SATIRLARI ATLAMAK İÇİN
(SD.LINETYPE <> '3') -- SATIR MASRAFI OLAN SATIRLARI ATLAMAK İÇİN
GROUP BY C.CODE, C.DEFINITION2, C.CITY, C.TOWN, S.FICHENO, S.DATE_, SD.DUEDATE, S.NETTOTAL, S.GENEXP1
ORDER BY DUEDATE, RUT , İLİ