CREATE VIEW dbo.SIPARIS_FISI_KISMI
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
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
						 WHERE FIS.REF IN (SELECT DISTINCT FICHEREF FROM dbo.FICHE_LINES WHERE [STATUS] IN (1,2))