--Drop Table ##TTT

Select 
	*, BudgetAmount/MonthCount As MonthSallary
	Into ##TTT
From (
		Select 
			C.ID As ContractID,
			C.ContractNumber, B.BudgetAmount, --F.Name, * 
			C.StartDate, C.EndDate, S.GeoName As [ContractStatus], --C.SignDate,
			P.TAX, P.GeoName, P.[Address], C.OldID As [ContractOldID],
			DateDiff(Month, C.StartDate, C.EndDate) + 1 As MonthCount, Legal.GeoName As LegalForm
		From dbo.BL_Contracts C
		Join dbo.BL_ContractBudget B On B.ContractID = C.ID
		--Join dbo.BL_ContractReportingForms RF On RF.ContractID = C.ID
		--Join dbo.BL_ReportingForms F On F.ID = RF.ReportingFormID
		Join dbo.BL_Providers P On C.OrganizationID = P.ID
		Join dbo.BL_ContractStatuses S On S.ID = C.StatusID
		JOin dbo.BL_LegalForms Legal On Legal.ID = P.LegalFormID
		Where 
			C.BudgetHierarchyID = '6c5814ed-b821-47b6-92b9-9e7f58fc8a11'
			And B.[Year] = 2014
			And C.StartDate >= '2014-05-01'
			And S.GeoName != N'გაუქმებული (შეცდომით შეყვანილი)'
			--And B.BudgetAmount In (3640.00, 5200)
			And P.TAX != '123123123'
			And (P.TAX In ('239403463', '227721383') Or Legal.GeoName != N'შპს')
		--Order By LegalForm --B.BudgetAmount--, C.StartDate Desc, C.EndDate
) T
Where BudgetAmount/MonthCount In (350, 455, 500, 650)
--Where BudgetAmount/MonthCount In (350, 455)
--Where BudgetAmount/MonthCount In (500, 650)
Order By MonthSallary

Select * From dbo.BL_ReportingForms 
WHERE ID = '91BEE919-871D-487C-B0D6-5AAB2A639E4D' Or ID = '7D651827-DE89-4E2A-83C2-4A93E76A11B4'


Select F.Name, * 
From ##TTT C
Left Join dbo.BL_ContractReportingForms RF On RF.ContractID = C.ID
Left Join dbo.BL_ReportingForms F On F.ID = RF.ReportingFormID
Where RF.DateDeleted Is Null
	--And MonthSallary In (350, 455)
	And MonthSallary In (500, 650)
	And RF.ID Is Null



Declare @CurrentDate DateTime
Set @CurrentDate = '2014-07-07 16:45:00'

Begin Tran



Insert Into dbo.BL_ContractReportingForms
        ( ID ,
          ContractID ,
          ReportingFormID ,
          DateCreated
        )
Select	  NewId() , --ID
          C.ContractID , --ContractID
          '91BEE919-871D-487C-B0D6-5AAB2A639E4D' , --ReportingFormID 23 სოფლის ექიმი
          @CurrentDate  -- DateCreated
--Select F.Name, * 
From ##TTT C
Left Join dbo.BL_ContractReportingForms RF On RF.ContractID = C.ContractID
Left Join dbo.BL_ReportingForms F On F.ID = RF.ReportingFormID
Where RF.DateDeleted Is Null
	--And MonthSallary In (350, 455)
	And MonthSallary In (500, 650)
	And RF.ID Is Null


Insert Into dbo.BL_ContractReportingForms
        ( ID ,
          ContractID ,
          ReportingFormID ,
          DateCreated
        )
Select	  NewId() , --ID
          C.ContractID , --ContractID
          '7D651827-DE89-4E2A-83C2-4A93E76A11B4' , --ReportingFormID 23 სოფლის ექთანი
          @CurrentDate  -- DateCreated
--Select F.Name, * 
From ##TTT C
Left Join dbo.BL_ContractReportingForms RF On RF.ContractID = C.ContractID
Left Join dbo.BL_ReportingForms F On F.ID = RF.ReportingFormID
Where RF.DateDeleted Is Null
	And MonthSallary In (350, 455)
	--And MonthSallary In (500, 650)
	And RF.ID Is Null


Rollback

Commit