
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[STOKMIADHASTABAKIYE]') AND OBJECTPROPERTY(id,N'IsVIew') = 1)
BEGIN
    INSERT INTO SCRIPT_BACKUPS (SCRIPTNAME,SCRIPT,SCRIPTDATE) SELECT 'STOKMIADHASTABAKIYE' SCRIPTNAME,OBJECT_DEFINITION(OBJECT_ID('STOKMIADHASTABAKIYE')) AS SCRIPT,GETDATE() AS SCRIPTDATE
    DROP VIEW STOKMIADHASTABAKIYE
END
GO    


CREATE VIEW [dbo].[STOKMIADHASTABAKIYE] 
AS
SELECT    VWHDOSYA.HDOSYAID,VWHDOSYA.ADISOYADI,STOKMIADBAKIYE.SUBEID, SUBELER.SUBEKODU, SUBELER.SUBEADI, STOKMIADBAKIYE.DEPOID, 
                    DEPO.DEPOADI,
                      STOKMIADBAKIYE.STOKID, '' KODU,/*VWSTOKISL.KODU,*/ VWSTOKISL.ADI, VWSTOKISL.SBIRIMI, STOKMIADBAKIYE.SKTARIHI, 
					  DATEDIFF(d,dbo.TARIHAL(GETDATE()), STOKMIADBAKIYE.SKTARIHI) AS MIADGUNSAYISI
					  ,SUM(GCADET) AS HASTAMIKTAR
					  ,SUM(GCADET/ (CASE WHEN ISNULL(VWSTOKISL.KATSAYI2, 0) = 0 THEN 1 ELSE  VWSTOKISL.KATSAYI2 END )) AS HASTA2BMIKTAR
					  ,CASE WHEN (LTRIM( VWSTOKISL.BIRIM2) = '''') THEN  NULL ELSE  VWSTOKISL.BIRIM2 END AS BIRIM2,DATEPART(YEAR,HDTARIH) DOSYAYILI

			

FROM         STOKMIADBAKIYE (NOLOCK)
                      INNER JOIN VWSTOKISL (NOLOCK) ON STOKMIADBAKIYE.STOKID=VWSTOKISL.STOKID AND VWSTOKISL.DEPOID=STOKMIADBAKIYE.DEPOID AND 
					                           HDOSYAID IS NOT NULL AND STOKMIADBAKIYE.SKTARIHI=VWSTOKISL.SKTARIHI
					  INNER JOIN DEPO (NOLOCK) ON STOKMIADBAKIYE.DEPOID = DEPO.DEPOID 
					  INNER JOIN SUBELER (NOLOCK) ON STOKMIADBAKIYE.SUBEID = SUBELER.SUBEID	
					  INNER JOIN VWHDOSYA (NOLOCK) ON VWSTOKISL.HDOSYAID=VWHDOSYA.HDOSYAID				  
WHERE     (STOKMIADBAKIYE.MIKTAR <> 0)           
		  GROUP BY STOKMIADBAKIYE.SUBEID, SUBELER.SUBEKODU, SUBELER.SUBEADI, STOKMIADBAKIYE.DEPOID, DEPO.DEPOADI,
                      STOKMIADBAKIYE.STOKID, /*VWSTOKISL.KODU,*/ VWSTOKISL.ADI, VWSTOKISL.SBIRIMI,VWSTOKISL.BIRIM2, STOKMIADBAKIYE.SKTARIHI, 
					  DATEDIFF(d,dbo.TARIHAL(GETDATE()), STOKMIADBAKIYE.SKTARIHI),VWHDOSYA.HDOSYAID,DATEPART(YEAR,HDTARIH),VWHDOSYA.ADISOYADI
					  HAVING SUM(GCADET)>0




GO

