CREATE VIEW dbo.FIRMA_SIPARIS_TOPLAMLARI
AS
SELECT 
    FIS.FICHENO, 
    FIS.DATE_, 
    FST.DURUM, 
    FIS.DEFINITION_, 
    DATEDIFF(DAY, FIS.DATE_, GETDATE()) AS GUN, 
    FIS.KULLANICI, 
    FIS.GENEXP, 
    FIS.TOPLAM, 
    ISNULL(FB.FISBAK, 0) AS FISBAK, 
    ISNULL(FIS.TOPLAM, 0) - ISNULL(FB.FISBAK, 0) AS KALAN
FROM 
    dbo.FIRMAYA_GORE_FIS_TOPLAMLARI AS FIS 
    LEFT OUTER JOIN dbo.FICHE_STATUS AS FST ON FST.REF = FIS.STATUS 
    LEFT OUTER JOIN dbo.FISNO_FIRMA_BAKIYE AS FB ON FB.FICHENO = FIS.FICHENO 
    LEFT OUTER JOIN dbo.COMPANIES AS CMP ON CMP.DEFINITION_ = FB.FIRMA AND FB.FIRMA=FIS.DEFINITION_ 
WHERE 
    FIS.STATUS NOT IN (3, 4) 
    AND REPLACE(FIS.DEFINITION_, ' ', '') = REPLACE(ISNULL(FB.FIRMA, ''), ' ', '')
    AND ISNULL(CMP.DEFINITION_, '') <> ''

UNION

-- FB.FIRMA degeri bos olanlari getirir
SELECT 
    FIS.FICHENO, 
    FIS.DATE_, 
    FST.DURUM, 
    FIS.DEFINITION_, 
    DATEDIFF(DAY, FIS.DATE_, GETDATE()) AS GUN, 
    FIS.KULLANICI, 
    FIS.GENEXP, 
    FIS.TOPLAM, 
    ISNULL(FB.FISBAK, 0) AS FISBAK, 
    ISNULL(FIS.TOPLAM, 0) - ISNULL(FB.FISBAK, 0) AS KALAN
FROM 
    dbo.FIRMAYA_GORE_FIS_TOPLAMLARI AS FIS 
    LEFT OUTER JOIN dbo.FICHE_STATUS AS FST ON FST.REF = FIS.STATUS 
    LEFT OUTER JOIN dbo.FISNO_FIRMA_BAKIYE AS FB ON FB.FICHENO = FIS.FICHENO  AND FB.FIRMA=FIS.DEFINITION_ 
WHERE 
    FIS.STATUS NOT IN (3, 4) 
    AND ISNULL(FB.FIRMA, '') = ''