Tarih aralığına göre listelemek

[burakturk]

Merhaba. SQL Server üzerinden aşağıdaki komutlar ile bir tablo çekmekteyim. Bu tabloya tarih kriteri koyarak, son bir hafta içerisindeki verileri listelemem gerekiyor. WHERE CONVERT(DATE,LG_121_01_INVOICE.DATE_,103) > CONVERT(DATE,(GETDATE()-7),103) AND ... şeklinde bir filtre ekleyerek yapmaya çalıştım ancak

"[Microsoft][ODBC SQL Server Driver][SQL Server]The multi-part identifier "LG_121_01_INVOICE.DATE_" could not be bound."


şeklinde bir hata alıyorum. Muhtemelen basit bir hata yüzünden böyle sonuç dönüyor ancak ben çözemedim.

Yardımcı olacak arkadaşlara şimdiden çok teşekkür ederim

 

[YUSUF44]

Çok anlamam ancak bu hata daha önce belirlenmeyen bir değişken kullanımıyla ilgiliymiş gibi görünüyor.

 

[burakturk]

Çok anlamam ancak bu hata daha önce belirlenmeyen bir değişken kullanımıyla ilgiliymiş gibi görünüyor.

Burada yaşadığım sorun zannedersem formatlarının eşleşmemesi kaynaklı. Bir çok kaynaktan araştırdım ancak bir cevap bulamadım.

 

[mancubus]

şunlardan biri iş görüyor mu?

WHERE LG_121_01_INVOICE.DATE_ BETWEEN '11.03.2021' AND '20.03.2021'

WHERE LG_121_01_INVOICE.DATE_ BETWEEN GETDATE() AND DATEADD(DAY,-7,GETDATE())

WHERE LG_121_01_INVOICE.DATE_ >= DATEADD(DAY,-7,GETDATE())'

 

[burakturk]

şunlardan biri iş görüyor mu?

WHERE LG_121_01_INVOICE.DATE_ BETWEEN '11.03.2021' AND '20.03.2021'

WHERE LG_121_01_INVOICE.DATE_ BETWEEN GETDATE() AND DATEADD(DAY,-7,GETDATE())

WHERE LG_121_01_INVOICE.DATE_ >= DATEADD(DAY,-7,GETDATE())'

Merhaba, bunu denemedim ancak aşağıdaki şekilde kodlarımı doğru bir şekilde çalıştırabildim.

SELECT LG_121_01_INVOICE.FICHENO AS [Fis No], LG_121_01_INVOICE.DATE_ AS Tarih, 
                      CASE WHEN LG_121_01_STLINE.LINETYPE = 4 THEN LG_121_SRVCARD.CODE ELSE LG_121_ITEMS.CODE END AS [Hizmet Kodu],
                      CASE WHEN LG_121_01_STLINE.LINETYPE = 4 THEN LG_121_SRVCARD.DEFINITION_ ELSE LG_121_ITEMS.NAME END AS [Hizmet Açıklaması], LG_121_CLCARD.CODE AS [Cari kodu],
                      LG_121_CLCARD.DEFINITION_ AS [Cari Adı], LG_121_CLCARD.CITY AS [Şehir] , LG_121_CLCARD.TOWN AS [İlçe], LG_121_CLCARD.SPECODE2 AS [Ana_Bölge], LG_121_CLCARD.SPECODE3 AS [Bölge] , ISNULL
                          ((SELECT     TOP (1) PRICE
                              FROM         LG_121_PRCLIST AS PRC
                              WHERE     (LG_121_ITEMS.LOGICALREF = CARDREF) AND (PTYPE = 1) AND (ACTIVE = 0) AND (LG_121_01_INVOICE.DATE_ BETWEEN BEGDATE AND ENDDATE)
                              ORDER BY BEGDATE DESC), 0) AS [Satış Fiyatı],
                              CASE WHEN LG_121_01_INVOICE.TRCODE = 2 THEN (-1* LG_121_01_STLINE.AMOUNT) ELSE CASE WHEN LG_121_01_INVOICE.TRCODE = 3 THEN (-1* LG_121_01_STLINE.AMOUNT) ELSE CASE WHEN LG_121_01_STLINE.DECPRDIFF  = 1 THEN (-1* LG_121_01_STLINE.AMOUNT) ELSE LG_121_01_STLINE.AMOUNT END END END AS Miktar,
CASE WHEN LG_121_01_STLINE.AMOUNT >0 THEN LG_121_01_STLINE.LINENET / LG_121_01_STLINE.AMOUNT ELSE 0 END AS [Birim Fiyat],
                      CASE WHEN LG_121_01_INVOICE.TRCODE = 2 THEN (-1* LG_121_01_STLINE.LINENET) ELSE CASE WHEN LG_121_01_INVOICE.TRCODE = 3 THEN (-1* LG_121_01_STLINE.LINENET) ELSE CASE WHEN LG_121_01_STLINE.DECPRDIFF  = 1 THEN (-1* LG_121_01_STLINE.LINENET) ELSE LG_121_01_STLINE.LINENET END END END AS [Net Tutar],LG_SLSMAN.DEFINITION_ AS [Satış Elemanı], 
                      LG_121_01_INVOICE.BRANCH AS [İş Yeri]
FROM         LG_121_01_INVOICE INNER JOIN
                      LG_121_01_STLINE ON LG_121_01_INVOICE.LOGICALREF = LG_121_01_STLINE.INVOICEREF LEFT OUTER JOIN
                      LG_121_ITEMS ON LG_121_01_STLINE.STOCKREF = LG_121_ITEMS.LOGICALREF LEFT OUTER JOIN
                      LG_121_SRVCARD ON LG_121_01_STLINE.STOCKREF = LG_121_SRVCARD.LOGICALREF LEFT OUTER JOIN
                      LG_SLSMAN ON LG_121_01_INVOICE.SALESMANREF = LG_SLSMAN.LOGICALREF LEFT OUTER JOIN
                      LG_121_PAYPLANS ON LG_121_01_INVOICE.PAYDEFREF = LG_121_PAYPLANS.LOGICALREF LEFT OUTER JOIN
                      LG_121_CLCARD ON LG_121_01_INVOICE.CLIENTREF = LG_121_CLCARD.LOGICALREF
WHERE   (CONVERT(DATE,LG_121_01_INVOICE.DATE_,103) > CONVERT(DATE,(GETDATE()-7),103)) and (LG_121_ITEMS.CODE LIKE 'C.%') AND (LG_121_01_INVOICE.TRCODE IN (3,8)) AND LG_121_01_STLINE.LINETYPE IN (0,4,8)AND LG_121_01_INVOICE.CANCELLED = 0
UNION ALL SELECT    LG_121_01_STFICHE.FICHENO AS [Fiş No], LG_121_01_STFICHE.DATE_ AS Tarih,
                      CASE WHEN LG_121_01_STLINE.LINETYPE = 4 THEN LG_121_SRVCARD.CODE ELSE LG_121_ITEMS.CODE END AS [Hizmet Kodu],
                      CASE WHEN LG_121_01_STLINE.LINETYPE = 4 THEN LG_121_SRVCARD.DEFINITION_ ELSE LG_121_ITEMS.NAME END AS [Hizmet Açıklaması], LG_121_CLCARD.CODE AS [Cari kodu],
                      LG_121_CLCARD.DEFINITION_ AS [Cari Adı], LG_121_CLCARD.CITY AS Þehir, LG_121_CLCARD.TOWN AS İlçe, LG_121_CLCARD.SPECODE2 AS Ana_Bölge, LG_121_CLCARD.SPECODE3 AS Bölge, ISNULL
                          ((SELECT     TOP (1) PRICE
                              FROM         LG_121_PRCLIST AS PRC
                              WHERE     (LG_121_ITEMS.LOGICALREF = CARDREF) AND (PTYPE = 1) AND (ACTIVE = 0) AND (LG_121_01_STFICHE.DATE_ BETWEEN BEGDATE AND ENDDATE)
                              ORDER BY BEGDATE DESC), 0) AS [Satış Fiyatı], CASE WHEN LG_121_01_STFICHE.TRCODE = 2 THEN (- 1 * LG_121_01_STLINE.AMOUNT)
                      ELSE CASE WHEN LG_121_01_STFICHE.TRCODE = 3 THEN (- 1 * LG_121_01_STLINE.AMOUNT) ELSE CASE WHEN LG_121_01_STLINE.DECPRDIFF = 1 THEN (- 1 * LG_121_01_STLINE.AMOUNT)
                      ELSE LG_121_01_STLINE.AMOUNT END END END AS Miktar,
                      CASE WHEN LG_121_01_STLINE.AMOUNT > 0 THEN LG_121_01_STLINE.LINENET / LG_121_01_STLINE.AMOUNT ELSE 0 END AS [Birim Fiyat], 
                      CASE WHEN LG_121_01_STFICHE.TRCODE = 2 THEN (- 1 * LG_121_01_STLINE.LINENET) ELSE CASE WHEN LG_121_01_STFICHE.TRCODE = 3 THEN (- 1 * LG_121_01_STLINE.LINENET)
                      ELSE CASE WHEN LG_121_01_STLINE.DECPRDIFF = 1 THEN (- 1 * LG_121_01_STLINE.LINENET) ELSE LG_121_01_STLINE.LINENET END END END AS [Net Tutar],
                      LG_SLSMAN.DEFINITION_ AS [Satış Elemanı], LG_121_01_STFICHE.BRANCH AS [İş Yeri]
                     FROM         LG_SLSMAN RIGHT OUTER JOIN
                      LG_121_01_STFICHE INNER JOIN
                      LG_121_01_STLINE ON LG_121_01_STFICHE.LOGICALREF = LG_121_01_STLINE.STFICHEREF LEFT OUTER JOIN
                      LG_121_PAYPLANS ON LG_121_01_STFICHE.PAYDEFREF = LG_121_PAYPLANS.LOGICALREF ON LG_SLSMAN.LOGICALREF = LG_121_01_STFICHE.SALESMANREF LEFT OUTER JOIN
                      LG_121_CLCARD ON LG_121_01_STFICHE.CLIENTREF = LG_121_CLCARD.LOGICALREF LEFT OUTER JOIN
                      LG_121_ITEMS ON LG_121_01_STLINE.STOCKREF = LG_121_ITEMS.LOGICALREF LEFT OUTER JOIN
                      LG_121_SRVCARD ON LG_121_01_STLINE.STOCKREF = LG_121_SRVCARD.LOGICALREF
WHERE   (CONVERT(DATE,LG_121_01_STFICHE.DATE_ ,103) > CONVERT(DATE,(GETDATE()-7),103)) and (LG_121_ITEMS.CODE LIKE 'C.%%') AND  (LG_121_01_STFICHE.TRCODE IN (3,8)) AND (LG_121_01_STLINE.LINETYPE IN (0, 4, 8)) AND (LG_121_01_STFICHE.CANCELLED = 0) AND LG_121_01_STFICHE.BILLED = 0
ORDER BY Tarih