SQL Sorgu son alış ve son satış tarihleri de eklenebilir mi

cenkboncuk

Yeni Üye
Katılım
20 Ocak 2021
Mesajlar
1
En iyi yanıt
0
Puanları
1
Yaş
41
Konum
istanbul
Ad Soyad
cenk boncuk
Office Versiyon
office 365 tr
merhaba aşağıdaki sorguya son alış ve son satış tarihleri eklenebilir mi, bir de stok miktarı da gelse tadından yenmez..


SELECT

I.CODE 'MALZEME KODU',
I.NAME 'MALZEME AÇIKLAMA',
U.CODE 'BİRİM',

ISNULL(ALIS.AMOUNT,0) [Son Alış Miktar],
ISNULL(ALIS.[Kdv Hariç Adet Fiyatı] ,0) [Son Alış Kdv Hariç],
ISNULL(ALIS.[Kdv Dahil Adet Fiyatı] ,0) [Son Alış Kdv Dahil],
ISNULL(SATIS.AMOUNT,0) [Son Satış Miktar],
ISNULL(SATIS.[Kdv Hariç Adet Fiyatı] ,0) [Son Satış Kdv Hariç],
ISNULL(SATIS.[Kdv Dahil Adet Fiyatı] ,0) [Son Satış Kdv Dahil]


FROM
LG_221_ITEMS I LEFT OUTER JOIN
LG_221_UNITSETL U ON U.UNITSETREF=I.UNITSETREF
AND U.MAINUNIT=1

OUTER APPLY

(SELECT TOP 1 S.STOCKREF, AMOUNT*
CASE WHEN
ISNULL(UINFO2,0)=0 THEN 1 ELSE UINFO2 END / CASE WHEN
ISNULL(UINFO1,0)=0 THEN 1 ELSE UINFO1 END AMOUNT,
ISNULL(S.VATMATRAH/NULLIF(AMOUNT,0),0)* CASE WHEN
ISNULL(UINFO2,0)=0 THEN 1 ELSE UINFO2 END / CASE WHEN
ISNULL(UINFO1,0)=0 THEN 1 ELSE UINFO1 END AS 'Kdv Hariç Adet Fiyatı',

ISNULL((S.VATMATRAH+S.VATAMNT)/NULLIF(AMOUNT,0),0) * CASE WHEN
ISNULL(UINFO1,0)=0 THEN 1 ELSE UINFO1 END/CASE WHEN
ISNULL(UINFO2,0)=0 THEN 1 ELSE UINFO2 END AS 'Kdv Dahil Adet Fiyatı'

FROM
LG_221_01_STLINE S

WHERE S.STOCKREF = I.LOGICALREF

AND S.LINETYPE=0
AND S.TRCODE IN (1,14)
AND S.STFICHEREF<>0
AND S.CANCELLED=0
AND S.VATMATRAH>0

ORDER BY S.DATE_ DESC,S.FTIME DESC) AS ALIS

OUTER APPLY

(SELECT TOP 1
S.STOCKREF, AMOUNT*
CASE WHEN
ISNULL(UINFO2,0)=0 THEN 1 ELSE UINFO2 END/CASE WHEN
ISNULL(UINFO1,0)=0 THEN 1 ELSE UINFO1 END AMOUNT,
ISNULL(S.VATMATRAH/NULLIF(AMOUNT,0),0)* CASE WHEN
ISNULL(UINFO2,0)=0 THEN 1 ELSE UINFO2 END/CASE WHEN
ISNULL(UINFO1,0)=0 THEN 1 ELSE UINFO1 END AS 'Kdv Hariç Adet Fiyatı',

ISNULL((S.VATMATRAH+S.VATAMNT)/NULLIF(AMOUNT,0),0) * CASE WHEN
ISNULL(UINFO1,0)=0 THEN 1 ELSE UINFO1 END/CASE WHEN
ISNULL(UINFO2,0)=0 THEN 1 ELSE UINFO2 END AS 'Kdv Dahil Adet Fiyatı'

FROM
LG_221_01_STLINE S WHERE S.STOCKREF = I.LOGICALREF

AND S.LINETYPE=0
AND S.TRCODE IN (7,8)
AND S.STFICHEREF<>0
AND S.CANCELLED=0
AND S.VATMATRAH>0

ORDER BY S.DATE_ DESC,S.FTIME DESC) AS SATIS
 

tasad

Yeni Üye
Katılım
8 Eki 2020
Mesajlar
2
En iyi yanıt
0
Puanları
1
Yaş
46
Konum
ISPARTA
Ad Soyad
TARIK SADIK
Firma ve dönem numaralarını kendinize göre düzenleyin.
Kod:
SELECT
I.CODE 'MALZEME KODU',
I.NAME 'MALZEME AÇIKLAMA',
U.CODE 'BİRİM',
EldekiStok.toplam 'Eldeki Miktar',
ALIS.DATE_'Son Alış Tarihi',
ISNULL(ALIS.AMOUNT,0) [Son Alış Miktar],
ISNULL(ALIS.[Kdv Hariç Adet Fiyatı] ,0) [Son Alış Kdv Hariç],
ISNULL(ALIS.[Kdv Dahil Adet Fiyatı] ,0) [Son Alış Kdv Dahil],
SATIS.DATE_'Son Satış Tarihi',
ISNULL(SATIS.AMOUNT,0) [Son Satış Miktar],
ISNULL(SATIS.[Kdv Hariç Adet Fiyatı] ,0) [Son Satış Kdv Hariç],
ISNULL(SATIS.[Kdv Dahil Adet Fiyatı] ,0) [Son Satış Kdv Dahil]
FROM
LG_001_ITEMS I LEFT OUTER JOIN
LG_001_UNITSETL U ON U.UNITSETREF=I.UNITSETREF AND U.MAINUNIT=1

OUTER APPLY

(SELECT TOP 1 S.STOCKREF,
S.DATE_ ,
AMOUNT*
CASE WHEN
ISNULL(UINFO2,0)=0 THEN 1 ELSE UINFO2 END / CASE WHEN
ISNULL(UINFO1,0)=0 THEN 1 ELSE UINFO1 END AMOUNT,
ISNULL(S.VATMATRAH/NULLIF(AMOUNT,0),0)* CASE WHEN
ISNULL(UINFO2,0)=0 THEN 1 ELSE UINFO2 END / CASE WHEN
ISNULL(UINFO1,0)=0 THEN 1 ELSE UINFO1 END AS 'Kdv Hariç Adet Fiyatı',

ISNULL((S.VATMATRAH+S.VATAMNT)/NULLIF(AMOUNT,0),0) * CASE WHEN
ISNULL(UINFO1,0)=0 THEN 1 ELSE UINFO1 END/CASE WHEN
ISNULL(UINFO2,0)=0 THEN 1 ELSE UINFO2 END AS 'Kdv Dahil Adet Fiyatı'

FROM
LG_001_02_STLINE S

WHERE S.STOCKREF = I.LOGICALREF

AND S.LINETYPE=0
AND S.TRCODE IN (1,14)
AND S.STFICHEREF<>0
AND S.CANCELLED=0
AND S.VATMATRAH>0

ORDER BY S.DATE_ DESC,S.FTIME DESC) AS ALIS

OUTER APPLY

(SELECT TOP 1
S.STOCKREF,
S.DATE_,
AMOUNT*
CASE WHEN
ISNULL(UINFO2,0)=0 THEN 1 ELSE UINFO2 END/CASE WHEN
ISNULL(UINFO1,0)=0 THEN 1 ELSE UINFO1 END AMOUNT,
ISNULL(S.VATMATRAH/NULLIF(AMOUNT,0),0)* CASE WHEN
ISNULL(UINFO2,0)=0 THEN 1 ELSE UINFO2 END/CASE WHEN
ISNULL(UINFO1,0)=0 THEN 1 ELSE UINFO1 END AS 'Kdv Hariç Adet Fiyatı',

ISNULL((S.VATMATRAH+S.VATAMNT)/NULLIF(AMOUNT,0),0) * CASE WHEN
ISNULL(UINFO1,0)=0 THEN 1 ELSE UINFO1 END/CASE WHEN
ISNULL(UINFO2,0)=0 THEN 1 ELSE UINFO2 END AS 'Kdv Dahil Adet Fiyatı'

FROM
LG_001_02_STLINE S WHERE S.STOCKREF = I.LOGICALREF

AND S.LINETYPE=0
AND S.TRCODE IN (7,8)
AND S.STFICHEREF<>0
AND S.CANCELLED=0
AND S.VATMATRAH>0

ORDER BY S.DATE_ DESC,S.FTIME DESC) AS SATIS

OUTER APPLY
(
select STOCKREF,sum(ONHAND) as toplam from LG_001_02_STINVTOT stot where stot.STOCKREF=I.LOGICALREF
and stot.INVENNO=0
group by STOCKREF

) as EldekiStok
 
Üst Alt