CREATE  Procedure  [dbo].[sp_LastPriceAnbar]  @BeforeDate smalldatetime, @Type Char(1) As
  Declare @StrCommand NVarchar(4000)
  Declare @Parameter Nvarchar(50)
  IF @Type = '0' 
  Set @StrCommand =
      ' Insert Into ##SoodZian(K_code, A_code, CalcPrice) 
       Select T.K_Code, Reciver A_code, Price_KHarid * Mandeh CalcPrice From (
      Select T.K_Code, Reciver, T.Tarikh, T.Price_Kharid From (
      SELECT Havaleh.K_Code, Convert(Varchar(10),headh.Tarikh_faktor,111) Tarikh , Price_KHarid, AutoId, Reciver
      FROM headH INNER JOIN  Havaleh ON headH.Id_Havaleh =Havaleh.Id_Havaleh 
      Where HeadH.Tarikh_Faktor <  @Date   
      And Kind_Flag in (2,3) 
      Group By Havaleh.K_Code , Reciver,
      Convert(Varchar(10),headh.Tarikh_faktor,111), 
      Price_KHarid, Autoid
      ) As T
      Inner Join 
      (
        SELECT Havaleh.K_Code, Max(Convert(Varchar(10),Tarikh_faktor,111))  MaxTarikh, MAX(Autoid) MaxAutoid
        FROM headH INNER JOIN  Havaleh ON headH.Id_Havaleh =Havaleh.Id_Havaleh 
        Where HeadH.Tarikh_Faktor <  @Date
        And Kind_Flag  in (2,3) 
        Group By Havaleh.K_Code 
       ) As T1 on T.K_Code = T1.K_Code And T.Tarikh = T1.MaxTarikh And T1.MaxAutoid = T.AutoId
      ) As T
      Inner Join 
      (
        Select K_Code, IsNull(Sum(Vorod),0) - IsNull(Sum(Sodor),0) Mandeh 
        From (
              Select K_Code , 
              Case
		  	    When Len(Sender) =3 Then SUM(dbo.Havaleh.K_Qty1)
			  End As Sodor,
			  Case
			     When Len(Reciver) = 3 Then  SUM(dbo.Havaleh.K_Qty1) 
			  End As Vorod
			  FROM headH INNER JOIN  Havaleh ON headH.Id_Havaleh = Havaleh.Id_Havaleh 
			  WHERE ((Len(Havaleh.Sender) =3) or (Len(Havaleh.Reciver)= 3 )) 
			  And HeadH.Tarikh_Faktor <=  @Date   
			  Group by Sender , K_Code, Reciver
            ) As T1 
            Group By K_Code
        ) As T1 on T.K_Code = T1.K_Code
        Where Mandeh > 0'
      Else
        Set @StrCommand = 
      ' Insert Into ##SoodZian(K_code, A_code, CalcPrice) 
      Select '''',A_Code, Sum(Calcprice) SumKharid From (
      Select T.K_Code, Reciver A_code, Price_KHarid * Mandeh CalcPrice From (
      Select T.K_Code, Reciver, T.Tarikh, T.Price_Kharid From (
      SELECT Havaleh.K_Code, Convert(Varchar(10),headh.Tarikh_faktor,111) Tarikh , Price_KHarid, AutoId, Reciver
      FROM headH INNER JOIN  Havaleh ON headH.Id_Havaleh =Havaleh.Id_Havaleh 
      Where Kind_Flag in (2,3) 
      Group By Havaleh.K_Code , Reciver,
      Convert(Varchar(10),headh.Tarikh_faktor,111), 
      Price_KHarid, Autoid
      ) As T
      Inner Join 
      (
        SELECT Havaleh.K_Code, Max(Convert(Varchar(10),Tarikh_faktor,111))  MaxTarikh, MAX(Autoid) MaxAutoid
        FROM headH INNER JOIN  Havaleh ON headH.Id_Havaleh =Havaleh.Id_Havaleh 
        Where Kind_Flag  in (2,3) 
        Group By Havaleh.K_Code 
       ) As T1 on T.K_Code = T1.K_Code And T.Tarikh = T1.MaxTarikh And T1.MaxAutoid = T.AutoId
      ) As T
      Inner Join 
      (
        Select K_Code, IsNull(Sum(Vorod),0) - IsNull(Sum(Sodor),0) Mandeh 
        From (
              Select K_Code , 
              Case
		  	    When Len(Sender) =3 Then SUM(dbo.Havaleh.K_Qty1)
			  End As Sodor,
			  Case
			     When Len(Reciver) = 3 Then  SUM(dbo.Havaleh.K_Qty1) 
			  End As Vorod
			  FROM headH INNER JOIN  Havaleh ON headH.Id_Havaleh = Havaleh.Id_Havaleh 
			  WHERE ((Len(Havaleh.Sender) =3) or (Len(Havaleh.Reciver)= 3 )) 
			  Group by Sender , K_Code, Reciver
            ) As T1 
            Group By K_Code
        ) As T1 on T.K_Code = T1.K_Code
        Where Mandeh > 0
        ) As T Group By A_code'
        
  Set @Parameter = N'@Date smalldatetime'
  Exec Sp_ExecuteSql  @StrCommand, @Parameter, @Date = @BeforeDate



