CREATE VIEW dbo.CARI_BAKIYELER_TARIHLI
AS
SELECT        CC.CODE AS [FIRMA KODU], CC.DEFINITION_ AS [FIRMA ÜNVANI], UPPER(CC.CITY) AS [SEHIR], CC.SPECODE AS [AY SONU], CC.SPECODE2 AS PLASIYER, CASE WHEN ABS(SUM(ALACAK) - SUM(BORC)) 
                         < 50 THEN 0 ELSE ABS(SUM(ALACAK) - SUM(BORC)) END AS BAKIYE, CASE WHEN SUM(ALACAK) - SUM(BORC) > 0 THEN 'ALACAK' WHEN SUM(ALACAK) - SUM(BORC) = 0 THEN '' ELSE 'BORÇ' END AS DURUM, ISNULL
                             ((SELECT        SUM(TRNET)
                                 FROM            LG_011_07_CLFLINE
                                 WHERE        CLIENTREF = CC.LOGICALREF AND [SIGN] = 1 AND CANCELLED = 0 AND DATE_ BETWEEN CAST('2023-03-01' AS DATE) AND CAST('2023-03-04' AS DATE)), 0) AS ODEME, ISNULL(CB.BAKIYE, 0) AS [SON BAKIYE], 
                         ISNULL(CB.DURUM, '') AS [SON DURUM]
FROM            ((SELECT        CLIENTREF, TRNET AS BORC, 0 AS ALACAK
                            FROM            LG_011_07_CLFLINE
                            WHERE        [SIGN] = 0 AND CANCELLED = 0 AND DATE_ <= CAST('2023-02-28' AS DATE))
                         UNION ALL
                         (SELECT        CLIENTREF, 0 AS BORC, TRNET AS ALACAK
                          FROM            LG_011_07_CLFLINE
                          WHERE        [SIGN] = 1 AND CANCELLED = 0 AND DATE_ <= CAST('2023-02-28' AS DATE))) AS QQ LEFT OUTER JOIN
LG_011_CLCARD AS CC ON CC.LOGICALREF = QQ.CLIENTREF LEFT OUTER JOIN
dbo.[CARI_BAKIYELER] AS CB ON CB.CLIENTREF = QQ.CLIENTREF
GROUP BY CC.CODE, CC.DEFINITION_, CC.CITY, CC.SPECODE, CC.SPECODE2, CB.BAKIYE, CB.DURUM, CC.LOGICALREF
HAVING        ABS(SUM(QQ.ALACAK) - SUM(QQ.BORC)) > 50