--BORÇ TAKİPTE OLUP DİĞER MODULLERDE OLMAYAN
SELECT *
FROM LG_001_01_PAYTRANS
WHERE
(
(MODULENR=4 AND TRCODE IN (6,7,8,9,41) AND SIGN=1) OR
(MODULENR=5 AND TRCODE IN (2,3) AND SIGN=1) OR
(MODULENR=6 AND TRCODE IN (3,4) AND SIGN=1)OR
(MODULENR=7 AND TRCODE IN (4) AND SIGN=1)OR
(MODULENR=10 AND TRCODE IN (2) AND SIGN=1)OR
(MODULENR=4 AND TRCODE IN (1,2,3,4,26,42) AND SIGN=0)OR
(MODULENR=5 AND TRCODE IN (1,4) AND SIGN=0)OR
(MODULENR=6 AND TRCODE IN (1,2) AND SIGN=0)OR
(MODULENR=7 AND TRCODE IN (3) AND SIGN=0)OR
(MODULENR=10 AND TRCODE IN (1) AND SIGN=0)
)
OR
(
((MODULENR = 0) AND (TRCODE=0) AND (FICHEREF >= 0))OR
((MODULENR>=0) AND (TRCODE=0)AND(FICHEREF=0)) OR
((MODULENR=0) AND (TRCODE>=0)AND(FICHEREF=0))
)
OR
(
(MODULENR=4 AND FICHEREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_INVOICE)) OR
(MODULENR=5 AND FICHEREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_CLFLINE)) OR
(MODULENR=6 AND FICHEREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_CSROLL)) OR
(MODULENR=7 AND FICHEREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_BNFLINE)) OR
(MODULENR=10 AND FICHEREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_KSLINES))
)
--FİŞİ OLMAYAN CARİ HESAP FİŞLERİ
SELECT * FROM LG_001_01_CLFLINE
WHERE MODULENR=5 AND SOURCEFREF NOT IN(SELECT LOGICALREF FROM LG_001_01_CLFICHE)
--CARI KARTI OLMAYAN HAREKETLER
SELECT CLIENTREF FROM LG_001_01_CLFLINE
WHERE CLIENTREF NOT IN(SELECT LOGICALREF FROM LG_001_CLCARD;
--CARİ HESAP HAREKET HATALI SATIRLAR
SELECT * FROM LG_001_01_CLFLINE WHERE MODULENR=4 AND TRCODE=38 AND
SOURCEFREF NOT IN (SELECT LOGICALREF FROM LG_001_01_INVOICE)
Merhaba,
Yedek aldıktan sonra işlemleri yapınız.
---------FİŞİ OLMAYAN CARİ HESAP HAREKETLERİ
SELECT * FROM LG_001_01_CLFLINE
WHERE MODULENR=5 AND SOURCEFREF NOT IN (SELECT LOGICALREF FROM LG_001_01_CLFICHE)
---------CARI KARTI OLMAYAN HAREKETLER
SELECT CLIENTREF FROM LG_001_01_CLFLINE
WHERE CLIENTREF NOT IN (SELECT LOGICALREF FROM LG_001_CLCARD)
---------CARİ HESAP HAREKET HATALI SATIRLAR
SELECT * FROM LG_001_01_CLFLINE WHERE MODULENR=4 AND TRCODE=38 AND
SOURCEFREF NOT IN (SELECT LOGICALREF FROM LG_001_01_INVOICE)
***********************
BORÇ TAKİP İLE CARİ HESAP HAREKETLERİNİ KARŞILAŞTIRIYOR
SELECT * FROM LG_208_01_PAYTRANS P WITH(NOLOCK) LEFT OUTER JOIN LG_208_01_CLFLINE L WITH(NOLOCK) ON P.FICHEREF=L.SOURCEFREF AND P.MODULENR=L.MODULENR WHERE P.MODULENR NOT IN (5,61,62,63,64) AND L.LOGICALREF IS NULL UNION
SELECT * FROM LG_208_01_PAYTRANS P WITH(NOLOCK) LEFT OUTER JOIN LG_208_01_CLFLINE L WITH(NOLOCK) ON P.FICHEREF=L.LOGICALREF AND P.MODULENR=L.MODULENR WHERE P.MODULENR IN (5,61,62,63,64) AND L.LOGICALREF IS NULL
*****************
CARİ HESAP HAREKETLERİ İLE BORÇ TAKİP KARŞILAŞTIRIYOR
SELECT * FROM LG_208_01_CLFLINE L WITH(NOLOCK) LEFT OUTER JOIN LG_208_01_PAYTRANS P WITH(NOLOCK) ON L.SOURCEFREF=P.FICHEREF AND L.MODULENR=P.MODULENR WHERE L.MODULENR NOT IN (5,61,62,63,64) AND L.AMOUNT>0
-- AND L.CLIENTREF=140
AND P.LOGICALREF IS NULL
UNION
SELECT
*
FROM
LG_208_01_CLFLINE L WITH(NOLOCK)
LEFT OUTER JOIN LG_208_01_PAYTRANS P WITH(NOLOCK) ON L.LOGICALREF=P.FICHEREF AND L.MODULENR=P.MODULENR
WHERE
L.MODULENR IN (5,61,62,63,64)
AND L.AMOUNT>0
-- AND L.CLIENTREF=140
AND P.LOGICALREF IS NULL
SELECT * FROM LG_208_01_PAYTRANS WHERE MODULENR=10 AND FICHEREF=19372
SELECT * FROM LG_208_01_CLFLINE WHERE MODULENR=10 AND SOURCEFREF=19372
------CARI KARTI OLMAYAN HAREKETLER
SELECT CLIENTREF FROM LG_001_01_CLFLINE
WHERE CLIENTREF NOT IN (SELECT LOGICALREF FROM LG_001_CLCARD)
------CARİ HESAP HAREKET HATALI SATIRLAR
SELECT * FROM LG_001_01_CLFLINE WHERE MODULENR=4 AND TRCODE=38 AND
SOURCEFREF NOT IN (SELECT LOGICALREF FROM LG_001_01_INVOICE)
-------FATURASI OLUP CARI HESAP HAREKETİ OLMAYANLAR
SELECT * FROM LG_001_01_INVOICE WHERE TRCODE=8 AND LOGICALREF NOT IN (
SELECT SOURCEFREF FROM LG_001_01_CLFLINE WHERE MODULENR=4 AND TRCODE=38 )
------FİŞİ OLMAYAN CARİ HESAP HAREKETLERİ
SELECT * FROM LG_001_01_CLFLINE
WHERE MODULENR=5 AND SOURCEFREF NOT IN (SELECT LOGICALREF FROM LG_001_01_CLFICHE)
-----CARI KARTI OLMAYAN HAREKETLER
SELECT CLIENTREF FROM LG_001_01_CLFLINE
WHERE CLIENTREF NOT IN (SELECT LOGICALREF FROM LG_001_CLCARD)
----CARİ HESAP HAREKETLERİNDE OLUP CARİ HESAP FİŞİ BULUNMAYAN KAYITLAR İÇİN
SELECT * FROM LG_001_01_CLFLINE WHERE MODULENR=5 AND SOURCEFREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_CLFICHE)
----CARİ HESAP FİŞİ OLAN ANCAK CARİ HAREKETLERDE GÖZÜKMEYEN KAYITLAR İÇİN
SELECT * FROM LG_001_01_CLFICHE WHERE LOGICALREF NOT IN
(SELECT SOURCEFREF FROM LG_001_01_CLFLINE WHERE MODULENR=5)
Tag: sorgu
LOGO TIGER 3 SQL AY , AMBAR VE MALZEME BAZLI STOK DURUM RAPORU
CREATE view [dbo].[BM_211_STOKAYLIK] as
SELECT TOP 100 PERCENT tarih, ambar,'0' [isyeri],
CASE ITEMS2.CARDTYPE
WHEN 1 THEN
'TICARI MAL'
WHEN 2 THEN
'KARMA KOLI'
WHEN 3 THEN
'DEPOZITOLU MAL'
WHEN 4 THEN
'SABIT KIYMET'
WHEN 10 THEN
'HAMMADDE'
WHEN 11 THEN
'YARI MAMUL'
WHEN 12 THEN
'MAMUL'
WHEN 13 THEN
'TUKETIM MALI'
WHEN 20 THEN
'GENEL MALZEME SINIFI'
WHEN 21 THEN
'TABLOLU MALZEME SINIFI'
ELSE
CONVERT(VARCHAR(20), CARDTYPE)
END AS mal_tur,
ITEMS2.CODE mal_kod, ITEMS2.NAME mal_ad ,BIRIM.CODE birim ,
ISNULL(
(
SELECT SUM(ONHAND)
FROM LV_211_01_STINVTOT STINVTOT
WHERE STINVTOT.STOCKREF = ITEMS2.LOGICALREF
AND STINVTOT.INVENNO = ambarnr
-- AND STINVTOT.DATE_ <= DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(m, 0, tarih)), 0)
AND YEAR(STINVTOT.DATE_) * 100 + MONTH(STINVTOT.DATE_)<=tarih
),
0
) stok
FROM (
SELECT DISTINCT tarih ,whouse.NR ambarnr, WHOUSE.NAME ambar,MALZEMELER.STOCKREF FROM (
select DISTINCT YEAR(STLINE.DATE_) * 100 + MONTH(STLINE.DATE_) tarih from LG_211_01_STLINE STLINE
WHERE YEAR(STLINE.DATE_) * 100 + MONTH(STLINE.DATE_) <=YEAR(GETDATE())*100+MONTH(GETDATE())
) AS T
CROSS JOIN L_CAPIWHOUSE WHOUSE
CROSS JOIN LG_211_ITEMS ITEMS
LEFT JOIN
(
SELECT STLINE.STOCKREF , STLINE.SOURCEINDEX
FROM LG_211_01_STLINE STLINE WITH (NOLOCK)
LEFT JOIN LG_211_01_STFICHE STFIC
ON STLINE.STFICHEREF = STFIC.LOGICALREF
WHERE (
(STFIC.TRCODE IN ( 2, 3, 4, 7, 8, 9, 35, 36, 37, 38, 39 ))
OR (STFIC.CLIENTREF = 0)
OR (STFIC.TRCODE IN ( 1, 5, 6, 10, 26, 30, 31, 32, 33, 34 ))
OR (STFIC.CLIENTREF = 0)
OR (STFIC.TRCODE IN ( 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 50, 51 ))
)
AND (STFIC.CANCELLED = 0)
AND STLINE.LINETYPE IN ( 0, 1, 5, 6, 8, 9, 10 )
AND
(
(STFIC.STATUS IN ( 0, 1 ))
OR (STFIC.TRCODE IN ( 11, 12, 13, 14, 25, 26, 50, 51 ))
)
AND
(
(STFIC.STATUS IN ( 0, 1 ))
OR (STFIC.TRCODE = 25)
)
AND
(
(STFIC.PRODSTAT = 0)
OR (STFIC.TRCODE IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 ))
)
AND (STFIC.DEVIR IN ( 0, 1 ))
) as MALZEMELER ON MALZEMELER.STOCKREF=ITEMS.LOGICALREF AND MALZEMELER.SOURCEINDEX=NR
WHERE WHOUSE.FIRMNR=211
--ORDER BY tarih
) AS T2 LEFT JOIN LG_211_ITEMS ITEMS2 ON STOCKREF=ITEMS2.LOGICALREF
LEFT OUTER JOIN LG_211_UNITSETL BIRIM WITH (NOLOCK)
ON BIRIM.UNITSETREF = ITEMS2.UNITSETREF
AND BIRIM.MAINUNIT = 1
order by tarih
GO
LOGO SQL AYRINTILI MALIYET ANALIZ RAPORU
SELECT distinct
(SELECT
case ITEMS.CARDTYPE when 1 then 'Ticari Mal'
when 2 then 'Karma Koli'
when 3 then 'Depozitolu Mal'
when 4 then 'Sabit Kıymet'
when 10 then 'Hammadde'
when 11 then 'Yarı Mamul'
when 12 then 'Mamul'
when 13 then 'Tükletim Malı' else CONVERT(VARCHAR,ITEMS.CARDTYPE) END
FROM LG_211_ITEMS AS ITEMS WITH (NOLOCK) WHERE ITEMS.LOGICALREF=STLINE.STOCKREF) AS [Malzeme Turu],
(SELECT ITEMS.CODE FROM LG_211_ITEMS AS ITEMS WITH (NOLOCK) WHERE ITEMS.LOGICALREF=STLINE.STOCKREF) AS [Malzeme Kodu],
(SELECT ITEMS.NAME FROM LG_211_ITEMS AS ITEMS WITH (NOLOCK) WHERE ITEMS.LOGICALREF=STLINE.STOCKREF) AS [Malzeme Adı],
(SELECT ITEMS.SPECODE FROM LG_211_ITEMS AS ITEMS WITH (NOLOCK) WHERE ITEMS.LOGICALREF=STLINE.STOCKREF) AS [Malzeme Özelkodu],
STLINE.DATE_ AS [Tarih],
100*(YEAR(STLINE.DATE_))+MONTH(STLINE.DATE_) AS [Ay],
YEAR(STLINE.DATE_) AS [Yıl],
CASE
WHEN STLINE.TRCODE IN(11,12,13,14,25,50,51) THEN (SELECT STFICHE.FICHENO FROM LG_211_01_STFICHE AS STFICHE WITH (NOLOCK) WHERE STFICHE.LOGICALREF=STLINE.STFICHEREF)
WHEN STLINE.TRCODE IN(1,2,3,6,7,8) THEN (SELECT INVOICE.FICHENO FROM LG_211_01_INVOICE AS INVOICE WITH (NOLOCK) WHERE INVOICE.LOGICALREF=STLINE.INVOICEREF)
END AS [Fiş No],
CASE
WHEN STLINE.TRCODE IN(11,12,13,14,25,50,51) THEN (SELECT STFICHE.DOCODE FROM LG_211_01_STFICHE AS STFICHE WITH (NOLOCK) WHERE STFICHE.LOGICALREF=STLINE.STFICHEREF)
WHEN STLINE.TRCODE IN(1,2,3,6,7,8) THEN (SELECT INVOICE.DOCODE FROM LG_211_01_INVOICE AS INVOICE WITH (NOLOCK) WHERE INVOICE.LOGICALREF=STLINE.INVOICEREF)
END AS [Fiş Belge No],
CASE
STLINE.TRCODE
WHEN 14 THEN 'Devir Fişi'
WHEN 11 THEN 'Fire Fişi'
WHEN 12 THEN 'Sarf Fişi'
WHEN 13 THEN 'Üretimden Giriş Fişi'
WHEN 25 THEN 'Ambar Fişi'
WHEN 50 THEN 'Sayım Fazlası'
WHEN 51 THEN 'Sayım Eksiği'
WHEN 1 THEN 'Satınalma Faturası'
WHEN 6 THEN 'Satınalma İade Faturası'
WHEN 2 THEN 'Perkende Satış İade Faturası'
WHEN 3 THEN 'Toptan Satış İade Faturası'
WHEN 7 THEN 'Perakende Satış Faturası'
WHEN 8 THEN 'Toptan Satış Faturası'
END AS [Fiş Türü],
CASE
WHEN STLINE.IOCODE IN (0,1,2) THEN 'G'
WHEN STLINE.IOCODE IN (3,4) THEN 'Ç'
ELSE '?'
END AS [Hareket Yönü],
CASE
WHEN TRCODE IN (1,2,3,13,14,25,50) AND IOCODE IN (0,1,2) THEN
(SELECT L_CAPIDIV.NAME FROM L_CAPIDIV WITH (NOLOCK) WHERE L_CAPIDIV.FIRMNR=211 AND L_CAPIDIV.NR=(SELECT TOP 1 L_CAPIWHOUSE.DIVISNR FROM L_CAPIWHOUSE WITH (NOLOCK) WHERE L_CAPIWHOUSE.FIRMNR=211 AND L_CAPIWHOUSE.NR=STLINE.SOURCEINDEX))
ELSE
''
END AS [Giriş İşyeri],
CASE
WHEN TRCODE IN (1,2,3,13,14,25,50) AND IOCODE IN (0,1,2) THEN
(SELECT L_CAPIWHOUSE.NAME FROM L_CAPIWHOUSE WITH (NOLOCK) WHERE L_CAPIWHOUSE.FIRMNR=211 AND L_CAPIWHOUSE.NR=STLINE.SOURCEINDEX)
ELSE
''
END AS [Giriş Ambarı],
CASE
WHEN TRCODE IN (25) AND IOCODE IN (2) THEN
ISNULL((SELECT EMCENTER.CODE FROM LG_211_EMCENTER AS EMCENTER WITH (NOLOCK) WHERE STLINE.VATCENTERREF=EMCENTER.LOGICALREF),'')
WHEN TRCODE IN (1,2,3,13,14,50) THEN --AND IOCODE IN (0,1,2) THEN
ISNULL((SELECT EMCENTER.CODE FROM LG_211_EMCENTER AS EMCENTER WITH (NOLOCK) WHERE STLINE.CENTERREF=EMCENTER.LOGICALREF),'')
ELSE ''
END AS [Giriş Masraf Merkezi],
CASE
WHEN TRCODE IN (1,2,3,13,14,25,50) AND IOCODE IN (0,1,2) THEN
STLINE.AMOUNT
ELSE
0
END AS [Giren Miktar],
(SELECT CODE FROM LG_211_UNITSETF WITH (NOLOCK) WHERE LOGICALREF=(SELECT LG_211_ITEMS.UNITSETREF FROM LG_211_ITEMS WITH (NOLOCK) WHERE LG_211_ITEMS.LOGICALREF=STLINE.STOCKREF)) AS [Birim],
CASE
WHEN TRCODE IN (1,2,3,13,14,25,50) AND IOCODE IN (0,1,2) THEN
ROUND(STLINE.VATMATRAH/STLINE.AMOUNT,4)
ELSE
0
END AS [Birim Fiyat (G)],
CASE
WHEN TRCODE IN (1,2,3,13,14,25,50) AND IOCODE IN (0,1,2) THEN
ROUND(STLINE.VATMATRAH/STLINE.AMOUNT,4)*STLINE.AMOUNT
ELSE
0
END AS [Giren Mal Tutarı],
CASE
WHEN TRCODE IN (1,2,3,13,14,25,50) AND IOCODE IN (0,1,2) THEN
ROUND(STLINE.VATMATRAH/STLINE.AMOUNT,4)*STLINE.AMOUNT
ELSE
0
END AS [Giriş Maliyeti],
CASE
WHEN TRCODE IN (6,7,8,11,12,25,51) AND IOCODE IN (3,4) THEN
(SELECT L_CAPIDIV.NAME FROM L_CAPIDIV WITH (NOLOCK) WHERE L_CAPIDIV.FIRMNR=211 AND L_CAPIDIV.NR=(SELECT TOP 1 L_CAPIWHOUSE.DIVISNR FROM L_CAPIWHOUSE WITH (NOLOCK) WHERE L_CAPIWHOUSE.FIRMNR=211 AND L_CAPIWHOUSE.NR=STLINE.SOURCEINDEX))
ELSE
''
END AS [Çıkış İşyeri],
CASE
WHEN TRCODE IN (6,7,8,11,12,25,51) AND IOCODE IN (3,4) THEN
(SELECT L_CAPIWHOUSE.NAME FROM L_CAPIWHOUSE WITH (NOLOCK) WHERE L_CAPIWHOUSE.FIRMNR=211 AND L_CAPIWHOUSE.NR=STLINE.SOURCEINDEX)
ELSE
''
END AS [Çıkış Ambarı],
CASE
WHEN TRCODE IN (25) AND IOCODE IN (3) THEN
ISNULL((SELECT EMCENTER.CODE FROM LG_211_EMCENTER AS EMCENTER WITH (NOLOCK) WHERE STLINE.CENTERREF=EMCENTER.LOGICALREF),'')
WHEN TRCODE IN (7,8,6,11,12) THEN--AND IOCODE IN (0,1,2) THEN
ISNULL((SELECT EMCENTER.CODE FROM LG_211_EMCENTER AS EMCENTER WITH (NOLOCK) WHERE STLINE.CENTERREF=EMCENTER.LOGICALREF),'')
ELSE ''
END AS [Çıkış Masraf Merkezi],
CASE
WHEN TRCODE IN (6,7,8,11,12,25,51) AND IOCODE IN (3,4) THEN
STLINE.AMOUNT
ELSE
0
END AS [Çıkış Miktar],
CASE
WHEN TRCODE IN (6,7,8,11,12,25,51) AND IOCODE IN (3,4) THEN
ROUND(STLINE.VATMATRAH/STLINE.AMOUNT,4)
ELSE
0
END AS [Birim Fiyat (Ç)],
CASE
WHEN TRCODE IN (6,7,8,11,12,25,51) AND IOCODE IN (3,4) THEN
--ROUND(STLINE.VATMATRAH/STLINE.AMOUNT,4)*STLINE.AMOUNT
(STLINE.VATMATRAH/STLINE.AMOUNT)*STLINE.AMOUNT
ELSE
0
END AS [Çıkış Mal Tutarı],
CASE
WHEN TRCODE IN (6,7,8,11,12,25,51) AND IOCODE IN (3,4) THEN
--STLINE.AMOUNT*ROUND(STLINE.OUTCOST,4)
STLINE.AMOUNT*STLINE.OUTCOST
ELSE
0
END AS [Çıkış Maliyet Tutarı],
--ROUND(STLINE.OUTCOST,4) AS [Birim Maliyeti COST],
STLINE.OUTCOST AS [Birim Maliyeti COST],
ROUND((SELECT SUM(LV_211_01_STINVTOT.ONHAND) FROM LV_211_01_STINVTOT WITH (NOLOCK) WHERE LV_211_01_STINVTOT.INVENNO=-1 AND LV_211_01_STINVTOT.STOCKREF=STLINE.STOCKREF),4) AS [Eldeki Miktar Genel],
ROUND((SELECT SUM(LV_211_01_STINVTOT.ONHAND) FROM LV_211_01_STINVTOT WITH (NOLOCK) WHERE LV_211_01_STINVTOT.INVENNO=-1 AND LV_211_01_STINVTOT.STOCKREF=STLINE.STOCKREF),4)
*
ROUND((SELECT TOP 1 LG_211_01_STLINE.OUTCOST FROM LG_211_01_STLINE WITH (NOLOCK) WHERE LG_211_01_STLINE.OUTCOST 0 AND LG_211_01_STLINE.STOCKREF=STLINE.STOCKREF AND LG_211_01_STLINE.TRCODE IN (6,7,8,11,12,25,51) AND LG_211_01_STLINE.IOCODE IN (3,4) ORDER BY LG_211_01_STLINE.DATE_ DESC),4) AS [Envanter Tutarı]
,CASE
WHEN STLINE.BILLED='0' THEN (SELECT L_CAPIDIV.NAME FROM L_CAPIDIV WHERE L_CAPIDIV.FIRMNR=211 AND CONVERT(VARCHAR,NR)=(SELECT STFICHE.BRANCH FROM LG_211_01_STFICHE STFICHE WHERE STFICHE.LOGICALREF=STLINE.STFICHEREF))
ELSE (SELECT L_CAPIDIV.NAME FROM L_CAPIDIV WHERE L_CAPIDIV.FIRMNR=211 AND NR=(SELECT INVOICE.BRANCH FROM LG_211_01_INVOICE AS INVOICE WHERE STLINE.INVOICEREF=INVOICE.LOGICALREF))
END AS [Fiş İşyeri]
FROM LG_211_01_STLINE AS STLINE WITH (NOLOCK)
WHERE STLINE.CANCELLED=0 AND
STLINE.LINETYPE=0 AND
--STLINE.TRCODE IN (8) AND
STLINE.TRCODE IN (11,12,13,14,25,50,51,1,2,3,6,7,8) AND
STLINE.IOCODE IN(0,1,2,3,4) AND
STLINE.LPRODSTAT=0
AND YEAR(STLINE.DATE_)=2019
--AND STLINE.STOCKREF IN (SELECT LG_211_ITEMS.LOGICALREF FROM LG_211_ITEMS WHERE LG_211_ITEMS.CODE='1743')
/*
TRCODE IN (1,2,3,13,14,25,50) AND IOCODE IN (0,1,2)-- G İ R İ Ş !!!AMBAR FİŞİ 2
TRCODE IN (6,7,8,11,12,25,51) AND IOCODE IN (3,4) -- Ç I K I Ş !!!AMBAR FİŞİ IOCODE=3
*/
Sentez Live Dev – Sql Queryden Hareketlerin Silinmesi
Aşağıdaki sorgu liveda (peşin satışsız,ncrsız) kartlar dışında hareketleri siler:(malzeme fişleri ,irsaliyeler,faturalar,teklifler,sipariş,muhasebe fişi,banka çek fişleri).
Sildikten sonra shrink yapılmalı aşağıda nasıl yapıldığı anlatılmıştır.Yaklaşık 14 gblık datayı 700mba düşürdü.
Sildikten sonra shrink yapılmalı aşağıda nasıl yapıldığı anlatılmıştır.Yaklaşık 14 gblık datayı 700mba düşürdü.
delete Erp_InventoryReceiptItem
update Erp_Invoice set InventoryReceiptId = null
update Erp_InventoryReceiptAttachment set InventoryReceiptId = null
delete Erp_InventoryReceiptAttachment
delete Erp_InvoiceAttachment
delete Erp_WorkOrderProduction
delete Erp_InventoryReceipt
delete Erp_Invoice
delete Erp_BankAccountTotal
delete Erp_BankCredit
delete Erp_BankReceiptItem
update Erp_ChequeReceipt set BankReceiptId=null
delete Erp_ChequeReceiptItem
delete Erp_ChequeReceiptAttachment
delete Erp_ChequeReceipt
delete Erp_BankReceiptAttachment
delete Erp_BankReceipt
delete Erp_CashTotalItem
delete Erp_CashTotal
delete Erp_Cheque
delete Erp_OrderReceiptItem
delete Erp_OrderReceiptAttachment
delete Erp_OrderReceipt
delete Erp_ContractItem
delete Erp_ContractAttachment
delete Erp_Contract
delete Erp_CurrentAccountReceiptItem
delete Erp_CurrentAccountReceiptAttachment
delete Erp_CurrentAccountReceipt
delete Erp_CurrentAccountTotal
delete Erp_QuotationReceiptItem
delete Erp_QuotationReceiptAttachment
delete Erp_QuotationReceipt
delete Erp_DemandReceiptItem
delete Erp_DemandReceiptAttachment
delete Erp_DemandReceipt
delete Erp_WorkOrderItem
delete Erp_WorkOrderAttachment
delete Erp_WorkOrderExplanation
delete Erp_WorkOrder
delete Erp_GLReceiptItem
delete Erp_GLReceipt
Delete Erp_InventoryTotal
delete Erp_ReceiptPaymentItem
delete Meta_ForexRate
delete Erp_ServiceTotal
Delete Erp_GLAccountTotal
delete Erp_BankAccountTotal
truncate table Log_Transaction
shrink işlemi:
USE LiveHareketler;
GO
ALTER DATABASE LiveHareketler
SET RECOVERY SIMPLE;
GO
--Datadakı log dosyasını shrınk yap yani database sağ tıkla task shrink files log->reorganize page 0 ve tamam ve sonrasındada yine databasee sağ tık task shrink database ok
ALTER DATABASE LiveHareketler
SET RECOVERY FULL;
GO
Sql Server HIZLI Sorgu
select * into #satislar from logo_view_satislar_2014_2015 –sql serverde masterin altinda tempdbye atar sadece kullanılan sezondaki kisi erisebilir sql kapanip acilirsa sifirlanir
select yil,sum(toplamtutar) from #satislar group by yil
Blog of Sem 