CREATE VIEW dbo.SIPARIS_FISI_LISTESI
AS
SELECT        FIS.FICHENO, FIS.DATE_, FST.DURUM, DATEDIFF(DAY, FIS.DATE_, GETDATE()) AS GUN, KUL.KULLANICI, FIS.GENEXP, ISNULL
                             ((SELECT        COUNT(*) AS Expr1
                                 FROM            dbo.FICHE_LINES
                                 WHERE        (FICHEREF = FIS.REF)), 0) AS KALEM, ISNULL
                             ((SELECT        COUNT(DISTINCT COMPANYREF) AS Expr1
                                 FROM            dbo.FICHE_LINES AS FICHE_LINES_1
                                 WHERE        (FICHEREF = FIS.REF)), 0) AS FIRMA, ISNULL(STT.TPL, 0) AS TOPLAM, FIS.CBM, FIS.REPORTRATE, ISNULL(FB.FISBAK, 0) AS FISBAK, ISNULL(STT.TPL, 0) - ISNULL(FB.FISBAK, 0) AS KALAN
FROM            dbo.FICHES AS FIS LEFT OUTER JOIN
                         dbo.FICHE_STATUS AS FST ON FST.REF = FIS.STATUS LEFT OUTER JOIN
                         dbo.KULLANICILAR AS KUL ON KUL.REF = FIS.USERREF LEFT OUTER JOIN
                         dbo.SIPARIS_TOPLAM_TUTARLAR AS STT ON STT.FICHEREF = FIS.REF LEFT OUTER JOIN
                         dbo.FIS_BAKIYELERI AS FB ON FB.FICHENO = FIS.FICHENO