Ben hepten unuttum Tutkun082 sizi Bu aralar yoğunum. Size Bir örnek Gönderiyorum Bunu management te kopyalayıp F5 yaparsananız NET DEGER de sizin istediğiniz Brm miktarı gözükür. Artık siz uygularsınız
| SET ANSI_NULLS ON |
| GO |
| SET QUOTED_IDENTIFIER ON |
| GO |
| ALTER FUNCTION [dbo].[fn_NelerAldikSattik]( @caritip tinyint, |
| @carikod nvarchar(25), |
| @Satis_fl bit, |
| @ilktar datetime, |
| @sontar datetime ) |
| RETURNS @RetTBL table |
| ( |
| [msg_S_0077] nvarchar(25), /* TİPİ */ |
| [msg_S_0078] nvarchar(25), /* KODU */ |
| [msg_S_0070] nvarchar(50), /* İSMİ */ |
| [msg_S_0470] float, /* HAREKET miktarı */ |
| [msg_S_1207] float, /* İADE MİKTARI */ |
| [msg_S_1535] float, /* NET MİKTAR */ |
| [msg_S_1536] float, /* HAREKET DEĞERİ */ |
| [msg_S_1537] float, /* İADE DEĞERİ */ |
| [msg_S_1538] float /* NET DEĞERİ */ |
| ) |
| AS |
| BEGIN |
| Declare @Cmp_Tip tinyint |
| Declare @Cmp_KarsiTip tinyint |
| If @Satis_fl = 1 |
| Begin |
| SET @Cmp_Tip = 1 |
| SET @Cmp_KarsiTip = 0 |
| End |
| Else |
| Begin |
| SET @Cmp_Tip = 0 |
| SET @Cmp_KarsiTip = 1 |
| End |
| INSERT INTO @RetTBL |
| SELECT TOP 100 PERCENT |
| 'Stok', |
| sth_stok_kod, |
| dbo.fn_StokIsmi(sth_stok_kod), |
|
| SUM ( CASE |
| WHEN (sth_normal_iade=0) AND (dbo.fn_DegerFarki_mi(sth_cins)=0) THEN sth_miktar |
| ELSE 0 |
| END ) as [hareket miktarı], |
| SUM ( CASE |
| WHEN (sth_normal_iade=1) AND (dbo.fn_DegerFarki_mi(sth_cins)=0) THEN sth_miktar |
| ELSE 0 |
| END )as [İade Miktarı], |
| SUM ( CASE |
| WHEN (sth_normal_iade=0) AND (dbo.fn_DegerFarki_mi(sth_cins)=0) THEN sth_miktar |
| WHEN (sth_normal_iade=1) AND (dbo.fn_DegerFarki_mi(sth_cins)=0) THEN -1 * sth_miktar |
| ELSE 0 |
| END )As [Net Miktar], |
| SUM ( CASE |
| WHEN sth_normal_iade=0 THEN dbo.fn_StokHareketNetDeger ( sth_tutar, |
| sth_iskonto1, |
| sth_iskonto2, |
| sth_iskonto3, |
| sth_iskonto4, |
| sth_iskonto5, |
| sth_iskonto6, |
| sth_masraf1, |
| sth_masraf2, |
| sth_masraf3, |
| sth_masraf4, |
| sth_otvtutari, |
| sth_oivtutari, |
| sth_tip, |
| 0 |
| sth_har_doviz_kuru, |
| sth_alt_doviz_kuru, |
| sth_stok_doviz_kuru) |
| ELSE 0 |
| END )As [Hareket Değer], |
| SUM ( CASE |
| WHEN sth_normal_iade=1 THEN dbo.fn_StokHareketNetDeger ( sth_tutar, |
| sth_iskonto1, |
| sth_iskonto2, |
| sth_iskonto3, |
| sth_iskonto4, |
| sth_iskonto5, |
| sth_iskonto6, |
| sth_masraf1, |
| sth_masraf2, |
| sth_masraf3, |
| sth_masraf4, |
| sth_otvtutari, |
| sth_oivtutari, |
| sth_tip, |
| 0 |
| sth_har_doviz_kuru, |
| sth_alt_doviz_kuru, |
| sth_stok_doviz_kuru) |
| ELSE 0 |
| END )As [İade Değeri], |
| SUM ( CASE |
| WHEN sth_normal_iade=0 THEN dbo.fn_StokHareketNetDeger ( sth_tutar, |
| sth_iskonto1, |
| sth_iskonto2, |
| sth_iskonto3, |
| sth_iskonto4, |
| sth_iskonto5, |
| sth_iskonto6, |
| sth_masraf1, |
| sth_masraf2, |
| sth_masraf3, |
| sth_masraf4, |
| sth_otvtutari, |
| sth_oivtutari, |
| sth_tip, |
| 0 |
| sth_har_doviz_kuru, |
| sth_alt_doviz_kuru, |
| sth_stok_doviz_kuru) |
| ELSE -1 * dbo.fn_StokHareketNetDeger ( sth_tutar, |
| sth_iskonto1, |
| sth_iskonto2, |
| sth_iskonto3, |
| sth_iskonto4, |
| sth_iskonto5, |
| sth_iskonto6, |
| sth_masraf1, |
| sth_masraf2, |
| sth_masraf3, |
| sth_masraf4, |
| sth_otvtutari, |
| sth_oivtutari, |
| sth_tip, |
| 0 |
| sth_har_doviz_kuru, |
| sth_alt_doviz_kuru, |
| sth_stok_doviz_kuru) |
| END)/ |
| SUM ( CASE |
| WHEN (sth_normal_iade=0) AND (dbo.fn_DegerFarki_mi(sth_cins)=0) THEN sth_miktar |
| WHEN (sth_normal_iade=1) AND (dbo.fn_DegerFarki_mi(sth_cins)=0) THEN -1 * sth_miktar |
| ELSE 0 |
| END ) |
|
| as [Net değer] |
|
| FROM dbo.STOK_HAREKETLERI WITH (NOLOCK, INDEX = NDX_STOK_HAREKETLERI_03) |
| WHERE (sth_cari_kodu= @carikod) AND |
| (sth_cari_cinsi = @caritip) AND |
| ((sth_tarih>=@ilktar) or (@ilktar is NULL)) AND |
| ((sth_tarih<=@sontar) or (@sontar is NULL)) AND |
| (((sth_tip=@Cmp_KarsiTip) AND (sth_normal_iade=1)) OR |
| ((sth_tip=@Cmp_Tip) AND (sth_normal_iade=0))) AND |
| (sth_evraktip IN (1,3,4,13,16)) |
| Group by sth_stok_kod |
|
| UNION ALL |
| SELECT TOP 100 PERCENT |
| dbo.fn_CariCins ( cha_kasa_hizmet ), |
| cha_kasa_hizkod, |
| dbo.fn_CarininIsminiBul(cha_kasa_hizmet,cha_kasa_hizkod), |
| SUM ( CASE |
| WHEN cha_normal_Iade=0 THEN 1 |
| ELSE 0 |
| END ), |
| SUM ( CASE |
| WHEN cha_normal_Iade=1 THEN 1 |
| ELSE 0 |
| END ), |
| SUM ( CASE |
| WHEN cha_normal_Iade=0 THEN 1 |
| ELSE -1 |
| END ), |
| SUM ( CASE |
| WHEN cha_normal_Iade=0 THEN dbo.fn_CariHesapMeblag ( cha_meblag , |
| cha_aratoplam, |
| cha_vergi1 , |
| cha_vergi2 , |
| cha_vergi3 , |
| cha_vergi4 , |
| cha_vergi5 , |
| cha_vergi6 , |
| cha_vergi7 , |
| cha_vergi8 , |
| cha_vergi9 , |
| cha_vergi10 , |
| cha_d_kur, |
| cha_altd_kur , |
| cha_karsid_kur , |
| cha_cinsi, |
| cha_evrak_tip, |
| 0,@caritip, |
| cha_meblag_ana_doviz_icin_gecersiz_fl, |
| cha_meblag_alt_doviz_icin_gecersiz_fl, |
| cha_meblag_orj_doviz_icin_gecersiz_fl, |
| cha_ft_masraf1,cha_ft_masraf2,cha_ft_masraf3,cha_ft_masraf4, |
| cha_odeme_arr1,cha_odeme_arr2,cha_odeme_arr3,cha_odeme_arr4, |
| cha_odeme_arr5,cha_odeme_arr6,cha_odeme_arr7,cha_odeme_arr8, |
| cha_otvtutari, |
| cha_oivtutari, |
| cha_yuvarlama, |
| dbo.fn_ToplamTevkifat(cha_RECno), |
| cha_stopaj,cha_savsandesfonu) |
| ELSE 0 |
| END ), |
| SUM ( CASE |
| WHEN cha_normal_Iade=1 THEN dbo.fn_CariHesapMeblag ( cha_meblag , |
| cha_aratoplam, |
| cha_vergi1 , |
| cha_vergi2 , |
| cha_vergi3 , |
| cha_vergi4 , |
| cha_vergi5 , |
| cha_vergi6 , |
| cha_vergi7 , |
| cha_vergi8 , |
| cha_vergi9 , |
| cha_vergi10 , |
| cha_d_kur, |
| cha_altd_kur , |
| cha_karsid_kur , |
| cha_cinsi, |
| cha_evrak_tip, |
| 0,@caritip, |
| cha_meblag_ana_doviz_icin_gecersiz_fl, |
| cha_meblag_alt_doviz_icin_gecersiz_fl, |
| cha_meblag_orj_doviz_icin_gecersiz_fl, |
| cha_ft_masraf1,cha_ft_masraf2,cha_ft_masraf3,cha_ft_masraf4, |
| cha_odeme_arr1,cha_odeme_arr2,cha_odeme_arr3,cha_odeme_arr4, |
| cha_odeme_arr5,cha_odeme_arr6,cha_odeme_arr7,cha_odeme_arr8, |
| cha_otvtutari, |
| cha_oivtutari, |
| cha_yuvarlama, |
| dbo.fn_ToplamTevkifat(cha_RECno), |
| cha_stopaj,cha_savsandesfonu) |
| ELSE 0 |
| END ), |
| SUM ( CASE |
| WHEN cha_normal_Iade=0 THEN dbo.fn_CariHesapMeblag ( cha_meblag , |
| cha_aratoplam, |
| cha_vergi1 , |
| cha_vergi2 , |
| cha_vergi3 , |
| cha_vergi4 , |
| cha_vergi5 , |
| cha_vergi6 , |
| cha_vergi7 , |
| cha_vergi8 , |
| cha_vergi9 , |
| cha_vergi10 , |
| cha_d_kur, |
| cha_altd_kur , |
| cha_karsid_kur , |
| cha_cinsi, |
| cha_evrak_tip, |
| 0,@caritip, |
| cha_meblag_ana_doviz_icin_gecersiz_fl, |
| cha_meblag_alt_doviz_icin_gecersiz_fl, |
| cha_meblag_orj_doviz_icin_gecersiz_fl, |
| cha_ft_masraf1,cha_ft_masraf2,cha_ft_masraf3,cha_ft_masraf4, |
| cha_odeme_arr1,cha_odeme_arr2,cha_odeme_arr3,cha_odeme_arr4, |
| cha_odeme_arr5,cha_odeme_arr6,cha_odeme_arr7,cha_odeme_arr8, |
| cha_otvtutari, |
| cha_oivtutari, |
| cha_yuvarlama, |
| dbo.fn_ToplamTevkifat(cha_RECno), |
| cha_stopaj,cha_savsandesfonu) |
| ELSE -1 * dbo.fn_CariHesapMeblag ( cha_meblag , |
| cha_aratoplam, |
| cha_vergi1 , |
| cha_vergi2 , |
| cha_vergi3 , |
| cha_vergi4 , |
| cha_vergi5 , |
| cha_vergi6 , |
| cha_vergi7 , |
| cha_vergi8 , |
| cha_vergi9 , |
| cha_vergi10 , |
| cha_d_kur, |
| cha_altd_kur , |
| cha_karsid_kur , |
| cha_cinsi, |
| cha_evrak_tip, |
| 0,@caritip, |
| cha_meblag_ana_doviz_icin_gecersiz_fl, |
| cha_meblag_alt_doviz_icin_gecersiz_fl, |
| cha_meblag_orj_doviz_icin_gecersiz_fl, |
| cha_ft_masraf1,cha_ft_masraf2,cha_ft_masraf3,cha_ft_masraf4, |
| cha_odeme_arr1,cha_odeme_arr2,cha_odeme_arr3,cha_odeme_arr4, |
| cha_odeme_arr5,cha_odeme_arr6,cha_odeme_arr7,cha_odeme_arr8, |
| cha_otvtutari, |
| cha_oivtutari, |
| cha_yuvarlama, |
| dbo.fn_ToplamTevkifat(cha_RECno), |
| cha_stopaj,cha_savsandesfonu) |
| END ) |
|
| FROM dbo.CARI_HESAP_HAREKETLERI WITH (NOLOCK, INDEX = NDX_CARI_HESAP_HAREKETLERI_03) |
| WHERE (cha_kod= @carikod) AND |
| (cha_cari_cins= @caritip) AND |
| (cha_kasa_hizkod<>'') AND |
| (cha_kasa_hizmet in (3,5,8,9)) AND |
| ((cha_tarihi>=@ilktar) or (@ilktar is NULL)) AND |
| ((cha_tarihi<=@sontar) or (@sontar is NULL)) AND |
| (((cha_tip=@Cmp_Tip) AND (cha_normal_Iade=1)) OR |
| ((cha_tip=@Cmp_KarsiTip) AND (cha_normal_Iade=0))) |
| GROUP BY cha_kasa_hizmet,cha_kasa_hizkod |
| RETURN |
| END |
|
Buda Size Benden olsun Hatamı telafi edeyim. Siz sadece STOK Kodunu ben kırmızı renkle yaptım. Bu sorguda siz istediğiniz. stok kodunu girdiğiniz zaman hangi cari bu stoktan ne kadar almış. Sizin istediğiniz gibi bir sorgu, Bunun amacıda Cariden değilde stoktan bulma, DBANALİZDE denersiniz.
|
SELECT TOP 100 PERCENT |
| dbo.fn_CariCins(sth_cari_cinsi) as [cınsı], |
| sth_cari_kodu as [kodu], |
| dbo.fn_CarininIsminiBul(sth_cari_cinsi,sth_cari_kodu) as [ismı], |
| SUM ( CASE |
| WHEN sth_normal_iade=0 THEN sth_miktar |
| ELSE 0 |
| END ) as [mıktar], |
| SUM ( CASE |
| WHEN sth_normal_iade=1 THEN sth_miktar |
| ELSE 0 |
| END ) as [ıade mıktar], |
| SUM ( CASE |
| WHEN sth_normal_iade=0 THEN sth_miktar |
| ELSE -1 * sth_miktar |
| END ) as [kalan ], |
| SUM ( CASE |
| WHEN sth_normal_iade=0 THEN dbo.fn_StokHareketNetDeger ( sth_tutar, |
| sth_iskonto1, |
| sth_iskonto2, |
| sth_iskonto3, |
| sth_iskonto4, |
| sth_iskonto5, |
| sth_iskonto6, |
| sth_masraf1, |
| sth_masraf2, |
| sth_masraf3, |
| sth_masraf4, |
| sth_otvtutari, |
| sth_oivtutari, |
| sth_tip, |
| 0 |
| sth_har_doviz_kuru, |
| sth_alt_doviz_kuru, |
| sth_stok_doviz_kuru) |
| ELSE 0 |
| END ) as [stok hareket], |
| SUM ( CASE |
| WHEN sth_normal_iade=1 THEN dbo.fn_StokHareketNetDeger ( sth_tutar, |
| sth_iskonto1, |
| sth_iskonto2, |
| sth_iskonto3, |
| sth_iskonto4, |
| sth_iskonto5, |
| sth_iskonto6, |
| sth_masraf1, |
| sth_masraf2, |
| sth_masraf3, |
| sth_masraf4, |
| sth_otvtutari, |
| sth_oivtutari, |
| sth_tip, |
| 0 |
| sth_har_doviz_kuru, |
| sth_alt_doviz_kuru, |
| sth_stok_doviz_kuru) |
| ELSE 0 |
| END )as [ıade deger], |
| SUM ( CASE |
| WHEN sth_normal_iade=0 THEN dbo.fn_StokHareketNetDeger ( sth_tutar, |
| sth_iskonto1, |
| sth_iskonto2, |
| sth_iskonto3, |
| sth_iskonto4, |
| sth_iskonto5, |
| sth_iskonto6, |
| sth_masraf1, |
| sth_masraf2, |
| sth_masraf3, |
| sth_masraf4, |
| sth_otvtutari, |
| sth_oivtutari, |
| sth_tip, |
| 0 |
| sth_har_doviz_kuru, |
| sth_alt_doviz_kuru, |
| sth_stok_doviz_kuru) |
| ELSE -1 * dbo.fn_StokHareketNetDeger ( sth_tutar, |
| sth_iskonto1, |
| sth_iskonto2, |
| sth_iskonto3, |
| sth_iskonto4, |
| sth_iskonto5, |
| sth_iskonto6, |
| sth_masraf1, |
| sth_masraf2, |
| sth_masraf3, |
| sth_masraf4, |
| sth_otvtutari, |
| sth_oivtutari, |
| sth_tip, |
| 0 |
| sth_har_doviz_kuru, |
| sth_alt_doviz_kuru, |
| sth_stok_doviz_kuru) |
| END ) as [netde] , |
|
| SUM ( CASE |
| WHEN sth_normal_iade=0 THEN dbo.fn_StokHareketNetDeger ( sth_tutar, |
| sth_iskonto1, |
| sth_iskonto2, |
| sth_iskonto3, |
| sth_iskonto4, |
| sth_iskonto5, |
| sth_iskonto6, |
| sth_masraf1, |
| sth_masraf2, |
| sth_masraf3, |
| sth_masraf4, |
| sth_otvtutari, |
| sth_oivtutari, |
| sth_tip, |
| 0 |
| sth_har_doviz_kuru, |
| sth_alt_doviz_kuru, |
| sth_stok_doviz_kuru) |
| ELSE -1 * dbo.fn_StokHareketNetDeger ( sth_tutar, |
| sth_iskonto1, |
| sth_iskonto2, |
| sth_iskonto3, |
| sth_iskonto4, |
| sth_iskonto5, |
| sth_iskonto6, |
| sth_masraf1, |
| sth_masraf2, |
| sth_masraf3, |
| sth_masraf4, |
| sth_otvtutari, |
| sth_oivtutari, |
| sth_tip, |
| 0 |
| sth_har_doviz_kuru, |
| sth_alt_doviz_kuru, |
| sth_stok_doviz_kuru) |
| END ) /SUM ( CASE |
| WHEN sth_normal_iade=0 THEN sth_miktar |
| ELSE -1 * sth_miktar |
| END ) as [vrm] |
|
|
| FROM dbo.STOK_HAREKETLERI WITH (NOLOCK , INDEX=NDX_STOK_HAREKETLERI_11) |
| WHERE (sth_stok_kod = '1901') AND |
| (((sth_tip=0) AND (sth_normal_iade=1)) OR |
| ((sth_tip=0) AND (sth_normal_iade=0))) AND |
| (sth_evraktip IN (1,3,4,13)) |
| GROUP BY sth_cari_cinsi,sth_cari_kodu |
| ORDER BY sth_cari_cinsi,sth_cari_kodu |
|
Post Options
Thanks(0)
Quote 
Topic: Cari f10 penceresi (neler aldık-neler sattık)
Topic Options