SQL Sorgusuna bugün tarih kriteri eklemek

burakturk

Altın Üye
Katılım
12 Şubat 2013
Mesajlar
52
Excel Vers. ve Dili
Türkçe Excel 2019
Merhaba arkadaşlar,
Çalıştığım yerde Logo Tiger kullanıyoruz, SQL database üzerinden aşağıdaki sorguyu kullanarak Excel'e bir tablo çekiyorum. Tabloya dönem başından itibaren tüm faturaları getirebiliyorum ancak bunu günlük olarak getirmek istiyorum. Ne yaptım, ne ettiysem bir türlü başaramadım.

Bu konu ile alakalı bana yardımcı olabilecek arkadaşlar varsa yardımlarını rica ediyorum, eminim çok basit bir işlemdir. Şimdiden teşekkür ediyorum.

Ayrıca mantığını anlayabilmem açısından açıklayarak yazabilirseniz çok sevinirim.

SQL Komut Metni aşağıdaki gibidir;
Kod:
SELECT LG_120_01_INVOICE.FICHENO AS [Fiş No], LG_120_01_INVOICE.DATE_ AS Tarih, CASE WHEN LG_120_01_INVOICE.TRCODE = 2 THEN 'Perakende Satış İade Faturası' ELSE CASE WHEN LG_120_01_INVOICE.TRCODE = 3 THEN 'Toptan Satış İade Faturası'
ELSE CASE WHEN LG_120_01_INVOICE.TRCODE = 7 THEN 'Perakende Satış Faturası' ELSE CASE WHEN LG_120_01_INVOICE.TRCODE = 8 THEN 'Toptan Satış Faturası' ELSE CASE WHEN LG_120_01_INVOICE.TRCODE = 14 THEN 'Satış Fiyat Farkı Ft.' END END END END END AS [Fiş Türü],
                      CASE WHEN LG_120_01_STLINE.LINETYPE = 4 THEN LG_120_SRVCARD.CODE ELSE LG_120_ITEMS.CODE END AS [Hizmet Kodu],
                      CASE WHEN LG_120_01_STLINE.LINETYPE = 4 THEN LG_120_SRVCARD.DEFINITION_ ELSE LG_120_ITEMS.NAME END AS [Hizmet Açıklaması], LG_120_CLCARD.CODE AS [Cari kodu],
                      LG_120_CLCARD.DEFINITION_ AS [Cari Adı], LG_120_CLCARD.CITY AS Şehir , LG_120_CLCARD.TOWN AS İlçe, LG_120_ITEMS.PRODUCERCODE AS CAİ, ISNULL
                          ((SELECT     TOP (1) PRICE
                              FROM         LG_120_PRCLIST AS PRC
                              WHERE     (LG_120_ITEMS.LOGICALREF = CARDREF) AND (ORDERNR = 12) AND (ACTIVE = 0) AND (LG_120_01_INVOICE.DATE_ BETWEEN BEGDATE AND ENDDATE)
                              ORDER BY BEGDATE DESC), 0) AS [Per Vad],
                              CASE WHEN LG_120_01_INVOICE.TRCODE = 2 THEN (-1* LG_120_01_STLINE.AMOUNT) ELSE CASE WHEN LG_120_01_INVOICE.TRCODE = 3 THEN (-1* LG_120_01_STLINE.AMOUNT) ELSE CASE WHEN LG_120_01_STLINE.DECPRDIFF  = 1 THEN (-1* LG_120_01_STLINE.AMOUNT) ELSE LG_120_01_STLINE.AMOUNT END END END AS Miktar,
                              CASE WHEN LG_120_01_STLINE.TOTAL > 0 AND
                      LG_120_01_STLINE.AMOUNT >0 THEN LG_120_01_STLINE.LINENET / LG_120_01_STLINE.AMOUNT ELSE 0 END AS [Satış Fiyatı],LG_120_PAYPLANS.CODE AS [Ödeme Planı], LG_SLSMAN.DEFINITION_ AS [Satış Elamanı], LG_120_01_INVOICE.SPECODE AS [Destek],
                      LG_120_01_INVOICE.BRANCH AS [İş Yeri]
FROM         LG_120_01_INVOICE INNER JOIN
                      LG_120_01_STLINE ON LG_120_01_INVOICE.LOGICALREF = LG_120_01_STLINE.INVOICEREF LEFT OUTER JOIN
                      LG_120_ITEMS ON LG_120_01_STLINE.STOCKREF = LG_120_ITEMS.LOGICALREF LEFT OUTER JOIN
                      LG_120_SRVCARD ON LG_120_01_STLINE.STOCKREF = LG_120_SRVCARD.LOGICALREF LEFT OUTER JOIN
                      LG_SLSMAN ON LG_120_01_INVOICE.SALESMANREF = LG_SLSMAN.LOGICALREF LEFT OUTER JOIN
                      LG_120_PAYPLANS ON LG_120_01_INVOICE.PAYDEFREF = LG_120_PAYPLANS.LOGICALREF LEFT OUTER JOIN
                      LG_120_CLCARD ON LG_120_01_INVOICE.CLIENTREF = LG_120_CLCARD.LOGICALREF
WHERE     (LG_120_01_INVOICE.TRCODE IN (2,3,7, 8, 14)) AND LG_120_01_STLINE.LINETYPE IN (0,4,8)AND LG_120_01_INVOICE.CANCELLED = 0 AND LG_120_ITEMS.CODE LIKE 'L.%.K%' AND LG_120_CLCARD.DEFINITION_ NOT LIKE 'BOMONO%' AND LG_120_01_INVOICE.TRCODE NOT IN (3,8)
UNION ALL SELECT     LG_120_01_STFICHE.FICHENO AS [Fiş No], LG_120_01_STFICHE.DATE_ AS Tarih,
                      CASE WHEN LG_120_01_STFICHE.TRCODE = 2 THEN 'Perakende Saıtş İade İrsaliyesi' ELSE CASE WHEN LG_120_01_STFICHE.TRCODE = 3 THEN 'Toptan Satış İade İrsaliyesi' ELSE CASE WHEN LG_120_01_STFICHE.TRCODE
                       = 7 THEN 'Perakende Satış İrsaliyesi' ELSE CASE WHEN LG_120_01_STFICHE.TRCODE = 8 THEN 'Toptan Satış İrsaliyesi' ELSE CASE WHEN LG_120_01_STFICHE.TRCODE
                       = 14 THEN 'Satış Fiyat Farkı İrs.' END END END END END AS [Fiş Türü],
                      CASE WHEN LG_120_01_STLINE.LINETYPE = 4 THEN LG_120_SRVCARD.CODE ELSE LG_120_ITEMS.CODE END AS [Hizmet Kodu],
                      CASE WHEN LG_120_01_STLINE.LINETYPE = 4 THEN LG_120_SRVCARD.DEFINITION_ ELSE LG_120_ITEMS.NAME END AS [Hizmet Açıklaması], LG_120_CLCARD.CODE AS [Cari kodu],
                      LG_120_CLCARD.DEFINITION_ AS [Cari Adı], LG_120_CLCARD.CITY AS Şehir, LG_120_CLCARD.TOWN AS İlçe, LG_120_ITEMS.PRODUCERCODE AS CAİ ,ISNULL
                          ((SELECT     TOP (1) PRICE
                              FROM         LG_120_PRCLIST AS PRC
                              WHERE     (LG_120_ITEMS.LOGICALREF = CARDREF) AND (ORDERNR = 12) AND (ACTIVE = 0) AND (LG_120_01_STFICHE.DATE_ BETWEEN BEGDATE AND ENDDATE)
                              ORDER BY BEGDATE DESC), 0) AS [Per Vad],CASE WHEN LG_120_01_STFICHE.TRCODE = 2 THEN (- 1 * LG_120_01_STLINE.AMOUNT)
                      ELSE CASE WHEN LG_120_01_STFICHE.TRCODE = 3 THEN (- 1 * LG_120_01_STLINE.AMOUNT) ELSE CASE WHEN LG_120_01_STLINE.DECPRDIFF = 1 THEN (- 1 * LG_120_01_STLINE.AMOUNT)
                      ELSE LG_120_01_STLINE.AMOUNT END END END AS Miktar, CASE WHEN LG_120_01_STLINE.TOTAL > 0 AND
                      LG_120_01_STLINE.AMOUNT > 0 THEN LG_120_01_STLINE.LINENET / LG_120_01_STLINE.AMOUNT ELSE 0 END AS [Satış Fiyatı],
                      LG_120_PAYPLANS.CODE AS [Ödeme Planı], LG_SLSMAN.DEFINITION_ AS [Satış Elamanı], LG_120_01_STFICHE.SPECODE AS [Destek], LG_120_01_STFICHE.BRANCH AS [İş Yeri]
                     FROM         LG_SLSMAN RIGHT OUTER JOIN
                      LG_120_01_STFICHE INNER JOIN
                      LG_120_01_STLINE ON LG_120_01_STFICHE.LOGICALREF = LG_120_01_STLINE.STFICHEREF LEFT OUTER JOIN
                      LG_120_PAYPLANS ON LG_120_01_STFICHE.PAYDEFREF = LG_120_PAYPLANS.LOGICALREF ON LG_SLSMAN.LOGICALREF = LG_120_01_STFICHE.SALESMANREF LEFT OUTER JOIN
                      LG_120_CLCARD ON LG_120_01_STFICHE.CLIENTREF = LG_120_CLCARD.LOGICALREF LEFT OUTER JOIN
                      LG_120_ITEMS ON LG_120_01_STLINE.STOCKREF = LG_120_ITEMS.LOGICALREF LEFT OUTER JOIN
                      LG_120_SRVCARD ON LG_120_01_STLINE.STOCKREF = LG_120_SRVCARD.LOGICALREF
WHERE     (LG_120_01_STFICHE.TRCODE IN (2, 3, 7, 8)) AND (LG_120_01_STLINE.LINETYPE IN (0, 4, 8)) AND (LG_120_01_STFICHE.CANCELLED = 0) AND LG_120_01_STFICHE.BILLED = 0 AND LG_120_ITEMS.CODE LIKE 'L.%.K%'AND LG_120_CLCARD.DEFINITION_ NOT LIKE 'BOMONO%' AND LG_120_01_STFICHE.TRCODE NOT IN (3,8)
ORDER BY Tarih
 

Zeki Gürsoy

Uzman
Uzman
Katılım
31 Aralık 2005
Mesajlar
4,225
Excel Vers. ve Dili
Office 2019 (64 bit) - Türkçe
SQL:
LG_120_01_STFICHE.DATE_ = '2020-06-25'

.
 

burakturk

Altın Üye
Katılım
12 Şubat 2013
Mesajlar
52
Excel Vers. ve Dili
Türkçe Excel 2019
Cevabınız için teşekkür ederim.

bu şekilde denedim ancak bu sefer listeyi boş getiriyor. ayrıca iki tane tanım yapmam gerekiyor.

birisi LG_120_01_INVOICE.DATE_ diğeri sizin yazdığınız LG_120_01_STFICHE.DATE_

iksinde de "=" veya "LIKE" komutu ile denedim ancak ya liste boş geldi yada "The conversion of a varchat data type..." diye bir hata aldım. Farklı bir çözümü var mıdır?
 
Katılım
4 Ocak 2010
Mesajlar
2,074
Excel Vers. ve Dili
OFFICE 2007 PRO TR - Win7 X64
Merhaba,

Stfıche irsaliye fişlerinin tutulduğu tablo Invoıce ise Fatura başlıkların tutulduğu tablo burada size eğer ki her iki tabloya göre getirmek istiyorsanız.
Aşağıdaki şekilde değiştirip deneyebilir misiniz.

Aslında ADO ile excelden parametre verip o şekilde de alınabilir.


SQL:
SELECT LG_006_01_INVOICE.FICHENO AS [Fiş No], LG_006_01_INVOICE.DATE_ AS Tarih, CASE WHEN LG_006_01_INVOICE.TRCODE = 2 THEN 'Perakende Satış İade Faturası' ELSE CASE WHEN LG_006_01_INVOICE.TRCODE = 3 THEN 'Toptan Satış İade Faturası'
ELSE CASE WHEN LG_006_01_INVOICE.TRCODE = 7 THEN 'Perakende Satış Faturası' ELSE CASE WHEN LG_006_01_INVOICE.TRCODE = 8 THEN 'Toptan Satış Faturası' ELSE CASE WHEN LG_006_01_INVOICE.TRCODE = 14 THEN 'Satış Fiyat Farkı Ft.' END END END END END AS [Fiş Türü],
                      CASE WHEN LG_006_01_STLINE.LINETYPE = 4 THEN LG_006_SRVCARD.CODE ELSE LG_006_ITEMS.CODE END AS [Hizmet Kodu],
                      CASE WHEN LG_006_01_STLINE.LINETYPE = 4 THEN LG_006_SRVCARD.DEFINITION_ ELSE LG_006_ITEMS.NAME END AS [Hizmet Açıklaması], LG_006_CLCARD.CODE AS [Cari kodu],
                      LG_006_CLCARD.DEFINITION_ AS [Cari Adı], LG_006_CLCARD.CITY AS Şehir , LG_006_CLCARD.TOWN AS İlçe, LG_006_ITEMS.PRODUCERCODE AS CAİ, ISNULL
                          ((SELECT     TOP (1) PRICE
                              FROM         LG_006_PRCLIST AS PRC
                              WHERE     (LG_006_ITEMS.LOGICALREF = CARDREF) AND (ORDERNR = 12) AND (ACTIVE = 0) AND (LG_006_01_INVOICE.DATE_ BETWEEN BEGDATE AND ENDDATE)
                              ORDER BY BEGDATE DESC), 0) AS [Per Vad],
                              CASE WHEN LG_006_01_INVOICE.TRCODE = 2 THEN (-1* LG_006_01_STLINE.AMOUNT) ELSE CASE WHEN LG_006_01_INVOICE.TRCODE = 3 THEN (-1* LG_006_01_STLINE.AMOUNT) ELSE CASE WHEN LG_006_01_STLINE.DECPRDIFF  = 1 THEN (-1* LG_006_01_STLINE.AMOUNT) ELSE LG_006_01_STLINE.AMOUNT END END END AS Miktar,
                              CASE WHEN LG_006_01_STLINE.TOTAL > 0 AND
                      LG_006_01_STLINE.AMOUNT >0 THEN LG_006_01_STLINE.LINENET / LG_006_01_STLINE.AMOUNT ELSE 0 END AS [Satış Fiyatı],LG_006_PAYPLANS.CODE AS [Ödeme Planı], LG_SLSMAN.DEFINITION_ AS [Satış Elamanı], LG_006_01_INVOICE.SPECODE AS [Destek],
                      LG_006_01_INVOICE.BRANCH AS [İş Yeri]
FROM         LG_006_01_INVOICE INNER JOIN
                      LG_006_01_STLINE ON LG_006_01_INVOICE.LOGICALREF = LG_006_01_STLINE.INVOICEREF LEFT OUTER JOIN
                      LG_006_ITEMS ON LG_006_01_STLINE.STOCKREF = LG_006_ITEMS.LOGICALREF LEFT OUTER JOIN
                      LG_006_SRVCARD ON LG_006_01_STLINE.STOCKREF = LG_006_SRVCARD.LOGICALREF LEFT OUTER JOIN
                      LG_SLSMAN ON LG_006_01_INVOICE.SALESMANREF = LG_SLSMAN.LOGICALREF LEFT OUTER JOIN
                      LG_006_PAYPLANS ON LG_006_01_INVOICE.PAYDEFREF = LG_006_PAYPLANS.LOGICALREF LEFT OUTER JOIN
                      LG_006_CLCARD ON LG_006_01_INVOICE.CLIENTREF = LG_006_CLCARD.LOGICALREF
WHERE     (LG_006_01_INVOICE.TRCODE IN (2,3,7, 8, 14)) AND LG_006_01_STLINE.LINETYPE IN (0,4,8)AND LG_006_01_INVOICE.CANCELLED = 0 AND LG_006_ITEMS.CODE LIKE 'L.%.K%' AND LG_006_CLCARD.DEFINITION_ NOT LIKE 'BOMONO%' AND LG_006_01_INVOICE.TRCODE NOT IN (3,8)
UNION ALL SELECT     LG_006_01_STFICHE.FICHENO AS [Fiş No], LG_006_01_STFICHE.DATE_ AS Tarih,
                      CASE WHEN LG_006_01_STFICHE.TRCODE = 2 THEN 'Perakende Saıtş İade İrsaliyesi' ELSE CASE WHEN LG_006_01_STFICHE.TRCODE = 3 THEN 'Toptan Satış İade İrsaliyesi' ELSE CASE WHEN LG_006_01_STFICHE.TRCODE
                       = 7 THEN 'Perakende Satış İrsaliyesi' ELSE CASE WHEN LG_006_01_STFICHE.TRCODE = 8 THEN 'Toptan Satış İrsaliyesi' ELSE CASE WHEN LG_006_01_STFICHE.TRCODE
                       = 14 THEN 'Satış Fiyat Farkı İrs.' END END END END END AS [Fiş Türü],
                      CASE WHEN LG_006_01_STLINE.LINETYPE = 4 THEN LG_006_SRVCARD.CODE ELSE LG_006_ITEMS.CODE END AS [Hizmet Kodu],
                      CASE WHEN LG_006_01_STLINE.LINETYPE = 4 THEN LG_006_SRVCARD.DEFINITION_ ELSE LG_006_ITEMS.NAME END AS [Hizmet Açıklaması], LG_006_CLCARD.CODE AS [Cari kodu],
                      LG_006_CLCARD.DEFINITION_ AS [Cari Adı], LG_006_CLCARD.CITY AS Şehir, LG_006_CLCARD.TOWN AS İlçe, LG_006_ITEMS.PRODUCERCODE AS CAİ ,ISNULL
                          ((SELECT     TOP (1) PRICE
                              FROM         LG_006_PRCLIST AS PRC
                              WHERE     (LG_006_ITEMS.LOGICALREF = CARDREF) AND (ORDERNR = 12) AND (ACTIVE = 0) AND (LG_006_01_STFICHE.DATE_ BETWEEN BEGDATE AND ENDDATE)
                              ORDER BY BEGDATE DESC), 0) AS [Per Vad],CASE WHEN LG_006_01_STFICHE.TRCODE = 2 THEN (- 1 * LG_006_01_STLINE.AMOUNT)
                      ELSE CASE WHEN LG_006_01_STFICHE.TRCODE = 3 THEN (- 1 * LG_006_01_STLINE.AMOUNT) ELSE CASE WHEN LG_006_01_STLINE.DECPRDIFF = 1 THEN (- 1 * LG_006_01_STLINE.AMOUNT)
                      ELSE LG_006_01_STLINE.AMOUNT END END END AS Miktar, CASE WHEN LG_006_01_STLINE.TOTAL > 0 AND
                      LG_006_01_STLINE.AMOUNT > 0 THEN LG_006_01_STLINE.LINENET / LG_006_01_STLINE.AMOUNT ELSE 0 END AS [Satış Fiyatı],
                      LG_006_PAYPLANS.CODE AS [Ödeme Planı], LG_SLSMAN.DEFINITION_ AS [Satış Elamanı], LG_006_01_STFICHE.SPECODE AS [Destek], LG_006_01_STFICHE.BRANCH AS [İş Yeri]
                     FROM         LG_SLSMAN RIGHT OUTER JOIN
                      LG_006_01_STFICHE INNER JOIN
                      LG_006_01_STLINE ON LG_006_01_STFICHE.LOGICALREF = LG_006_01_STLINE.STFICHEREF LEFT OUTER JOIN
                      LG_006_PAYPLANS ON LG_006_01_STFICHE.PAYDEFREF = LG_006_PAYPLANS.LOGICALREF ON LG_SLSMAN.LOGICALREF = LG_006_01_STFICHE.SALESMANREF LEFT OUTER JOIN
                      LG_006_CLCARD ON LG_006_01_STFICHE.CLIENTREF = LG_006_CLCARD.LOGICALREF LEFT OUTER JOIN
                      LG_006_ITEMS ON LG_006_01_STLINE.STOCKREF = LG_006_ITEMS.LOGICALREF LEFT OUTER JOIN
                      LG_006_SRVCARD ON LG_006_01_STLINE.STOCKREF = LG_006_SRVCARD.LOGICALREF LEFT OUTER JOIN
                      LG_006_01_INVOICE  INVOICE  ON INVOICE.LOGICALREF=LG_006_01_STFICHE.INVOICEREF

WHERE    (INVOICE.DATE_ = '2020-06-25'  OR LG_006_01_STFICHE.DATE_ = '2020-06-25') AND
(LG_006_01_STFICHE.TRCODE IN (2, 3, 7, 8)) AND (LG_006_01_STLINE.LINETYPE IN (0, 4, 8)) AND (LG_006_01_STFICHE.CANCELLED = 0) AND LG_006_01_STFICHE.BILLED = 0 AND
LG_006_ITEMS.CODE LIKE 'L.%.K%' ---AND LG_006_CLCARD.DEFINITION_ NOT LIKE 'BOMONO%' AND LG_006_01_STFICHE.TRCODE NOT IN (3,8) and LG_006_01_INVOICE.DATE_ = '2020-06-25'
ORDER BY Tarih
 
Üst