Ayrıntılı Satış Raporu

[kekeceme]

merhabalar elimde ayrıntılı satış raporu ile ilgil bir sorgu var. bu sorguya TOTALADDTAX ek vergi ve TOTALDISCOUNTS genel toplam , indirimler TOTALDISCOUNTED ve GROSSTOTAL indirimler dahil genel toplam alanını eklemek istiyorum ama bir türlü yapamadım yardımcı olabilir misiniz lütfen ?

Kod: Tümünü seç

SELECT
CONVERT(nvarchar(20), INVOICE.DATE_,104) as Tarih,
--INVOICE.DATE_ AS [Tarih],
CLCARD.CODE as [Cari Kod],
CLCARD.DEFINITION_ as [Cari Ad],
INVOICE.SERIALCODE [Seri No],
INVOICE.FICHENO [Fiş No],
clcard.SPECODE2  [BAYİ],
COALESCE(
CASE STLINE.UINFO1
WHEN 0 THEN STLINE.AMOUNT
ELSE STLINE.AMOUNT*STLINE.UINFO2/STLINE.UINFO1
END, STLINE.AMOUNT/1000) [Miktar],
UNT.CODE [Birim],
CASE WHEN STLINE.LINETYPE=4 THEN SRV.CODE ELSE INV.CODE END AS [Malzeme Kod],
CASE WHEN STLINE.LINETYPE=4 THEN SRV.DEFINITION_ ELSE INV.NAME END AS [Malzeme Ad],
CASE WHEN STLINE.LINETYPE=4 THEN '' ELSE INV.STGRPCODE END AS [Malzeme Grup],
CASE WHEN STLINE.LINETYPE=4 THEN SRV.SPECODE ELSE INV.SPECODE END AS [Malzeme Özel Kod],
STLINE.PRICE [Birim Fiyat],
STLINE.VATMATRAH [KDV Matrah],
STLINE.VATAMNT [KDV Tutar],
STLINE.VAT [KDV %],
(CASE
WHEN INVOICE.TRCODE=1 THEN 'Satınalma Faturası'
WHEN INVOICE.TRCODE=2 THEN 'Perakende Satış İade Faturası'
WHEN INVOICE.TRCODE=3 THEN 'Toptan Satış İade Faturası'
WHEN INVOICE.TRCODE=4 THEN 'Alınan Hizmet Faturası'
WHEN INVOICE.TRCODE=5 THEN 'Alınan Proforma Faturası'
WHEN INVOICE.TRCODE=6 THEN 'Satınalma İade Faturası'
WHEN INVOICE.TRCODE=7 THEN 'Perakende Satış Faturası'
WHEN INVOICE.TRCODE=8 THEN 'Toptan Satış Faturası'
WHEN INVOICE.TRCODE=9 THEN 'Verilen Hizmet Faturası'
WHEN INVOICE.TRCODE=10 THEN 'Verilen Proforma Faturası'
WHEN INVOICE.TRCODE=12 THEN 'Alınan Vade Farkı Faturası'
WHEN INVOICE.TRCODE=13 THEN 'Satınalma Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=14 THEN 'Satış Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=26 THEN 'Müstahsil Makbuzu' END
) as [Fatura Tipi],
CASE WHEN CLCARD.ISPERSCOMP=1 THEN CLCARD.TCKNO ELSE CLCARD.TAXNR END AS [Cari VNO],
CLCARD.TAXOFFICE as [Cari VDaire],
EMUH.CODE as [Muhasebe Hesap Planı Kod],
EMUH.DEFINITION_ as [Muhasebe Hesap Planı Ad],
RTRIM(INVOICE.GENEXP1+' '+INVOICE.GENEXP2+' '+INVOICE.GENEXP3+' '+INVOICE.GENEXP4) AS [Açıklama]
FROM LG_022_12_INVOICE AS INVOICE WITH (NOLOCK)
RIGHT OUTER JOIN LG_022_12_STLINE AS STLINE ON INVOICE.LOGICALREF = STLINE.INVOICEREF
RIGHT OUTER JOIN LG_022_ITEMS INV ON INV.LOGICALREF=STLINE.STOCKREF
LEFT JOIN LG_022_SRVCARD SRV ON SRV.LOGICALREF=STLINE.STOCKREF
RIGHT OUTER JOIN LG_022_CLCARD CLCARD ON CLCARD.LOGICALREF=INVOICE.CLIENTREF
LEFT JOIN LG_022_EMUHACC EMUH ON EMUH.LOGICALREF=STLINE.ACCOUNTREF
RIGHT OUTER JOIN LG_022_UNITSETL UNT ON UNT.UNITSETREF=INV.UNITSETREF AND LINENR=1
WHERE(INVOICE.TRCODE IN(2,3,7,8,9,10,14))
--AND INVOICE.DATE_ >= '2023-01-01 00:00:00.000'
--AND INVOICE.DATE_ <= '2023-12-31 23:59:59.999'
ORDER BY INVOICE.DATE_,
INVOICE.SOURCEINDEX,
INVOICE.FICHENO

[akcanadana]

STLINE içinden satır bazında veri alıyorsun. Bahsettiğin alanlar INVOICE içerisinde toplam alanlar. Buraya eklense bile satır bazında olacak. Yani bir faturada 50 satır her satırda toplam rakamlar olur. Bu da anlamsız bir rapor olur. Talebinden bunu anladım

[kekeceme]

STLINE içinden satır bazında veri alıyorsun. Bahsettiğin alanlar INVOICE içerisinde toplam alanlar. Buraya eklense bile satır bazında olacak. Yani bir faturada 50 satır her satırda toplam rakamlar olur. Bu da anlamsız bir rapor olur. Talebinden bunu anladım

merhaba, belirttiğim sorgu satır bazlı çalışıyor zaten, TOTALADDTAX ek vergi kdv gibi her satırda gösterilsin istiyorum.TOTALDISCOUNTS genel toplam , indirimler TOTALDISCOUNTED ve GROSSTOTAL indirimler dahil genel toplam alanını da fatura bazında gösterilsin istiyorum. bunlar olmuyorsa TOTALADDTAX ek vergiyi ekleyebilirsekte işimi görür.

[cousen]

Bu sorguyu bir inceleyin işinizi görebilir.

Kod: Tümünü seç

SELECT        TOP (100) PERCENT dbo.LG_023_CLCARD.CODE AS CariHesapKodu, dbo.LG_023_CLCARD.DEFINITION_ AS CariHesapAciklamasi, dbo.LG_023_CLCARD.SPECODE AS CariHesapOzelKodu, 
                         LG_023_01_STLINE.TRCODE AS [Fat Tu 8s.1a], dbo.LG_023_ITEMS.CODE AS MalzemeKodu, dbo.LG_023_ITEMS.NAME AS MalzemeAciklamasi, dbo.LG_023_ITEMS.SPECODE AS MalzemeOzelKodu, 
                         CONVERT(varchar(10), dbo.LG_023_01_STFICHE.DATE_, 103) AS [Fat Tarih], dbo.LG_023_01_STFICHE.INVNO AS [Fat no], LG_023_01_STLINE.AMOUNT AS miktar, LG_023_01_STLINE.PRICE AS BirimFiyat, 
                         LG_023_01_STLINE.TOTAL AS [Brüt Toplam], LG_023_01_STLINE.DISTDISC AS indirim, LG_023_01_STLINE.VATAMNT AS kdv, LG_023_01_STLINE.VATMATRAH AS NetTutar, 
                         LG_023_01_STLINE.VATAMNT + LG_023_01_STLINE.VATMATRAH AS [KDV DAHİL],
                             (SELECT        DISCPER AS Expr1
                               FROM            dbo.LG_023_01_STLINE AS LG_023_01_STLINE_2
                               WHERE        (PARENTLNREF = LG_023_01_STLINE.LOGICALREF) AND (LOGICALREF =
                                                             (SELECT        MIN(LOGICALREF) AS Expr2
                                                               FROM            dbo.LG_023_01_STLINE AS LG_023_01_STLINE_1
                                                               WHERE        (PARENTLNREF = LG_023_01_STLINE.LOGICALREF) AND (LINETYPE = 2))) AND (LINETYPE = 2)) AS İSK1,
                             (SELECT        DISCPER AS Expr1
                               FROM            dbo.LG_023_01_STLINE AS LG_023_01_STLINE_2
                               WHERE        (PARENTLNREF = LG_023_01_STLINE.LOGICALREF) AND (LOGICALREF =
                                                             (SELECT        MIN(LOGICALREF) + 1 AS Expr2
                                                               FROM            dbo.LG_023_01_STLINE AS LG_023_01_STLINE_1
                                                               WHERE        (PARENTLNREF = LG_023_01_STLINE.LOGICALREF) AND (LINETYPE = 2))) AND (LINETYPE = 2)) AS İSK2,
                             (SELECT        DISCPER AS Expr1
                               FROM            dbo.LG_023_01_STLINE AS LG_023_01_STLINE_2
                               WHERE        (PARENTLNREF = LG_023_01_STLINE.LOGICALREF) AND (LOGICALREF =
                                                             (SELECT        MIN(LOGICALREF) + 2 AS Expr2
                                                               FROM            dbo.LG_023_01_STLINE AS LG_023_01_STLINE_1
                                                               WHERE        (PARENTLNREF = LG_023_01_STLINE.LOGICALREF) AND (LINETYPE = 2))) AND (LINETYPE = 2)) AS İSK3,
                             (SELECT        DISCPER AS Expr1
                               FROM            dbo.LG_023_01_STLINE AS LG_023_01_STLINE_2
                               WHERE        (PARENTLNREF = LG_023_01_STLINE.LOGICALREF) AND (LOGICALREF =
                                                             (SELECT        MIN(LOGICALREF) + 3 AS Expr2
                                                               FROM            dbo.LG_023_01_STLINE AS LG_023_01_STLINE_1
                                                               WHERE        (PARENTLNREF = LG_023_01_STLINE.LOGICALREF) AND (LINETYPE = 2))) AND (LINETYPE = 2)) AS İSK4,
                             (SELECT        DISCPER AS Expr1
                               FROM            dbo.LG_023_01_STLINE AS LG_023_01_STLINE_2
                               WHERE        (PARENTLNREF = LG_023_01_STLINE.LOGICALREF) AND (LOGICALREF =
                                                             (SELECT        MIN(LOGICALREF) + 4 AS Expr2
                                                               FROM            dbo.LG_023_01_STLINE AS LG_023_01_STLINE_1
                                                               WHERE        (PARENTLNREF = LG_023_01_STLINE.LOGICALREF) AND (LINETYPE = 2))) AND (LINETYPE = 2)) AS İSK5, LG_023_01_STLINE.AMOUNT * dbo.LG_023_ITMUNITA.VOLUME_ AS LİTRE, 
                         LG_023_01_STLINE.AMOUNT * dbo.LG_023_ITMUNITA.WEIGHT AS AĞIRLIK, dbo.LG_023_CAMPAIGN.CODE AS KAMPANYA, CONVERT(varchar(2), dbo.LG_023_01_STFICHE.DATE_, 101) AS AY, 
                         dbo.LG_023_01_STFICHE.DATE_, CAST(YEAR(dbo.LG_023_01_STFICHE.DATE_) AS varchar) AS yıl, LG_023_01_STLINE.CANCELLED, dbo.LG_023_01_ORFICHE.FICHENO AS [sip no]
FROM            dbo.LG_023_01_STLINE AS LG_023_01_STLINE INNER JOIN
                         dbo.LG_023_CLCARD ON LG_023_01_STLINE.CLIENTREF = dbo.LG_023_CLCARD.LOGICALREF INNER JOIN
                         dbo.LG_023_ITEMS ON LG_023_01_STLINE.STOCKREF = dbo.LG_023_ITEMS.LOGICALREF INNER JOIN
                         dbo.LG_023_01_STFICHE ON LG_023_01_STLINE.STFICHEREF = dbo.LG_023_01_STFICHE.LOGICALREF INNER JOIN
                         dbo.LG_023_ITMUNITA ON dbo.LG_023_ITEMS.LOGICALREF = dbo.LG_023_ITMUNITA.ITEMREF INNER JOIN
                         dbo.LG_023_01_ORFICHE ON LG_023_01_STLINE.ORDFICHEREF = dbo.LG_023_01_ORFICHE.LOGICALREF LEFT OUTER JOIN
                         dbo.LG_SLSMAN ON LG_023_01_STLINE.SALESMANREF = dbo.LG_SLSMAN.LOGICALREF LEFT OUTER JOIN
                         dbo.LG_023_CAMPAIGN ON LG_023_01_STLINE.CAMPAIGNREFS1 = dbo.LG_023_CAMPAIGN.LOGICALREF
WHERE        (dbo.LG_023_ITMUNITA.UNITLINEREF = 24) AND (LG_023_01_STLINE.LINETYPE = 0) AND (LG_023_01_STLINE.CANCELLED = 0)
ORDER BY CariHesapKodu

[kekeceme]

Bu sorguyu bir inceleyin işinizi görebilir.

Kod: Tümünü seç

SELECT        TOP (100) PERCENT dbo.LG_023_CLCARD.CODE AS CariHesapKodu, dbo.LG_023_CLCARD.DEFINITION_ AS CariHesapAciklamasi, dbo.LG_023_CLCARD.SPECODE AS CariHesapOzelKodu, 
                         LG_023_01_STLINE.TRCODE AS [Fat Tu 8s.1a], dbo.LG_023_ITEMS.CODE AS MalzemeKodu, dbo.LG_023_ITEMS.NAME AS MalzemeAciklamasi, dbo.LG_023_ITEMS.SPECODE AS MalzemeOzelKodu, 
                         CONVERT(varchar(10), dbo.LG_023_01_STFICHE.DATE_, 103) AS [Fat Tarih], dbo.LG_023_01_STFICHE.INVNO AS [Fat no], LG_023_01_STLINE.AMOUNT AS miktar, LG_023_01_STLINE.PRICE AS BirimFiyat, 
                         LG_023_01_STLINE.TOTAL AS [Brüt Toplam], LG_023_01_STLINE.DISTDISC AS indirim, LG_023_01_STLINE.VATAMNT AS kdv, LG_023_01_STLINE.VATMATRAH AS NetTutar, 
                         LG_023_01_STLINE.VATAMNT + LG_023_01_STLINE.VATMATRAH AS [KDV DAHİL],
                             (SELECT        DISCPER AS Expr1
                               FROM            dbo.LG_023_01_STLINE AS LG_023_01_STLINE_2
                               WHERE        (PARENTLNREF = LG_023_01_STLINE.LOGICALREF) AND (LOGICALREF =
                                                             (SELECT        MIN(LOGICALREF) AS Expr2
                                                               FROM            dbo.LG_023_01_STLINE AS LG_023_01_STLINE_1
                                                               WHERE        (PARENTLNREF = LG_023_01_STLINE.LOGICALREF) AND (LINETYPE = 2))) AND (LINETYPE = 2)) AS İSK1,
                             (SELECT        DISCPER AS Expr1
                               FROM            dbo.LG_023_01_STLINE AS LG_023_01_STLINE_2
                               WHERE        (PARENTLNREF = LG_023_01_STLINE.LOGICALREF) AND (LOGICALREF =
                                                             (SELECT        MIN(LOGICALREF) + 1 AS Expr2
                                                               FROM            dbo.LG_023_01_STLINE AS LG_023_01_STLINE_1
                                                               WHERE        (PARENTLNREF = LG_023_01_STLINE.LOGICALREF) AND (LINETYPE = 2))) AND (LINETYPE = 2)) AS İSK2,
                             (SELECT        DISCPER AS Expr1
                               FROM            dbo.LG_023_01_STLINE AS LG_023_01_STLINE_2
                               WHERE        (PARENTLNREF = LG_023_01_STLINE.LOGICALREF) AND (LOGICALREF =
                                                             (SELECT        MIN(LOGICALREF) + 2 AS Expr2
                                                               FROM            dbo.LG_023_01_STLINE AS LG_023_01_STLINE_1
                                                               WHERE        (PARENTLNREF = LG_023_01_STLINE.LOGICALREF) AND (LINETYPE = 2))) AND (LINETYPE = 2)) AS İSK3,
                             (SELECT        DISCPER AS Expr1
                               FROM            dbo.LG_023_01_STLINE AS LG_023_01_STLINE_2
                               WHERE        (PARENTLNREF = LG_023_01_STLINE.LOGICALREF) AND (LOGICALREF =
                                                             (SELECT        MIN(LOGICALREF) + 3 AS Expr2
                                                               FROM            dbo.LG_023_01_STLINE AS LG_023_01_STLINE_1
                                                               WHERE        (PARENTLNREF = LG_023_01_STLINE.LOGICALREF) AND (LINETYPE = 2))) AND (LINETYPE = 2)) AS İSK4,
                             (SELECT        DISCPER AS Expr1
                               FROM            dbo.LG_023_01_STLINE AS LG_023_01_STLINE_2
                               WHERE        (PARENTLNREF = LG_023_01_STLINE.LOGICALREF) AND (LOGICALREF =
                                                             (SELECT        MIN(LOGICALREF) + 4 AS Expr2
                                                               FROM            dbo.LG_023_01_STLINE AS LG_023_01_STLINE_1
                                                               WHERE        (PARENTLNREF = LG_023_01_STLINE.LOGICALREF) AND (LINETYPE = 2))) AND (LINETYPE = 2)) AS İSK5, LG_023_01_STLINE.AMOUNT * dbo.LG_023_ITMUNITA.VOLUME_ AS LİTRE, 
                         LG_023_01_STLINE.AMOUNT * dbo.LG_023_ITMUNITA.WEIGHT AS AĞIRLIK, dbo.LG_023_CAMPAIGN.CODE AS KAMPANYA, CONVERT(varchar(2), dbo.LG_023_01_STFICHE.DATE_, 101) AS AY, 
                         dbo.LG_023_01_STFICHE.DATE_, CAST(YEAR(dbo.LG_023_01_STFICHE.DATE_) AS varchar) AS yıl, LG_023_01_STLINE.CANCELLED, dbo.LG_023_01_ORFICHE.FICHENO AS [sip no]
FROM            dbo.LG_023_01_STLINE AS LG_023_01_STLINE INNER JOIN
                         dbo.LG_023_CLCARD ON LG_023_01_STLINE.CLIENTREF = dbo.LG_023_CLCARD.LOGICALREF INNER JOIN
                         dbo.LG_023_ITEMS ON LG_023_01_STLINE.STOCKREF = dbo.LG_023_ITEMS.LOGICALREF INNER JOIN
                         dbo.LG_023_01_STFICHE ON LG_023_01_STLINE.STFICHEREF = dbo.LG_023_01_STFICHE.LOGICALREF INNER JOIN
                         dbo.LG_023_ITMUNITA ON dbo.LG_023_ITEMS.LOGICALREF = dbo.LG_023_ITMUNITA.ITEMREF INNER JOIN
                         dbo.LG_023_01_ORFICHE ON LG_023_01_STLINE.ORDFICHEREF = dbo.LG_023_01_ORFICHE.LOGICALREF LEFT OUTER JOIN
                         dbo.LG_SLSMAN ON LG_023_01_STLINE.SALESMANREF = dbo.LG_SLSMAN.LOGICALREF LEFT OUTER JOIN
                         dbo.LG_023_CAMPAIGN ON LG_023_01_STLINE.CAMPAIGNREFS1 = dbo.LG_023_CAMPAIGN.LOGICALREF
WHERE        (dbo.LG_023_ITMUNITA.UNITLINEREF = 24) AND (LG_023_01_STLINE.LINETYPE = 0) AND (LG_023_01_STLINE.CANCELLED = 0)
ORDER BY CariHesapKodu

merhabalar, sorguyu çalıştırdığımda boş geliyor kontrol edebilir misiniz rica etsem ?

[cousen]

merhabalar, sorguyu çalıştırdığımda boş geliyor kontrol edebilir misiniz rica etsem ?

Bende sorunsuz çalışıyor dönem yılı kontrol ettiniz mi ?

[kekeceme]

Bende sorunsuz çalışıyor dönem yılı kontrol ettiniz mi ?

sorgudaki LG_023_01 firma kodu ve dönemlerini değiştirdim başka biryerini değişitrecek miyim ?

[cousen]

sorgudaki LG_023_01 firma kodu ve dönemlerini değiştirdim başka biryerini değişitrecek miyim ?

Başka değişikliğe gerek yok

[kekeceme]

Başka değişikliğe gerek yok

bu şekilde bende maalesef sorgu boş geliyor

[akcanadana]

(dbo.LG_023_ITMUNITA.UNITLINEREF = 24) Bunu anlamadım silersen sanırım düzelir

[kekeceme]

(dbo.LG_023_ITMUNITA.UNITLINEREF = 24) Bunu anlamadım silersen sanırım düzelir

sildim onu da ama yine de çalışmadı maalesef

[Tarkan VURAL]

Merhaba,
Atila beyin dediği gibi her satır için fatura bazında tekrarlı toplamları göreceksiniz. Zaten amacınız bu ise kolay.

Kod: Tümünü seç

FROM LG_001_01_INVOICE AS INVOICE WITH (NOLOCK)

satırından bir önceye

Kod: Tümünü seç

,INVOICE.TOTALADDTAX ToplamEkVergi, INVOICE.TOTALDISCOUNTS ToplamIndirim, 
INVOICE.TOTALDISCOUNTED ToplamIndirimMatrahi, INVOICE.GROSSTOTAL ToplamKdvsizFaturaTutari,
INVOICE.NETTOTAL ToplamKdvliFaturaTutari

ekleyebilirsiniz.