თიკოს წერილის შემთხვევა, სადაც აქტივაცია მეტია დეაქტივაციაზე:

Select

       Org.ContractID, P.GeoName, Org.OrganizationID, F.ArtificialCode, CF.FinancingItemID, CF.ActivationDate, CF.DeactivationDate, CF.DiagnoseTypeID, CF.MaxUnitCost, CF.DateCreated, CF.DateChanged, CF.DateDeleted

From dbo.BL_Contracts C

Join dbo.BL_ContractOrganizations Org On Org.ContractID = C.ID

Join dbo.BL_ContractFinancingItems CF ON CF.ContractOrganizationID = Org.ID

Join dbo.BL_FinancingItems F On F.ID = CF.FinancingItemID

Join dbo.BL_Providers P On P.ID = Org.OrganizationID

Where

       --CF.DateDeleted Is Null

       --And

       CF.DiagnoseTypeID = '44C0CCBB-1E8A-4892-A238-D901E67C9694'

       And Org.ID = '5CDC70C2-CD5E-45AF-B789-46BEE7D6CB7D'

       And CF.FinancingItemID = 'DB78971D-565C-4A0E-A06C-134994150E16'

Order By CF.DateCreated, CF.ActivationDate

ყველა ფინანსური ერთეული, სადაც აქტივაცია მეტია დეაქტივაციაზე:

Select

       D.Name,

       CF1.ID, CF1.FinancingItemID, CF1.DiagnoseTypeID, CF1.ActivationDate, CF1.DeactivationDate, CF1.MaxUnitCost, CF1.DateCreated, CF1.DateChanged, CF1.DateDeleted,

       CF2.ID, CF2.FinancingItemID, CF2.DiagnoseTypeID, CF2.ActivationDate, CF2.DeactivationDate, CF2.MaxUnitCost, CF2.DateCreated, CF2.DateChanged, CF2.DateDeleted

From dbo.BL_ContractFinancingItems CF1

Join dbo.BL_ContractOrganizations Org On Org.ID = CF1.ContractOrganizationID

Join dbo.BL_Contracts C On C.ID = Org.ContractID

Join dbo.BL_ProviderDepartments D ON D.ID = C.FinancingAgentDepartmentID

Join BL_ContractFinancingItems CF2 On CF2.ContractOrganizationID = CF1.ContractOrganizationID

                                                              And CF2.FinancingItemID = CF1.FinancingItemID

                                                              And CF2.DiagnoseTypeID = CF1.DiagnoseTypeID

                                                              And CF2.ID != CF1.ID

                                                              And (CF1.ActivationDate Between CF2.ActivationDate And CF2.DeactivationDate Or

                                                                     CF1.DeactivationDate Between CF2.ActivationDate And CF2.DeactivationDate)

Where CF1.DateDeleted Is Null

       And CF2.DateDeleted Is Null

Order By D.Name, CF1.DateCreated

======================================================================================================================================================================

თანაკვეთადი პერიოდების ფინანს. ერთეულები (ერთ პერიოდში აქვს რამდენიმე ფასი):

Select

       D.Name,

       CF1.ID, CF1.FinancingItemID, CF1.DiagnoseTypeID, CF1.ActivationDate, CF1.DeactivationDate, CF1.MaxUnitCost, CF1.DateCreated, CF1.DateChanged, CF1.DateDeleted,

       CF2.ID, CF2.FinancingItemID, CF2.DiagnoseTypeID, CF2.ActivationDate, CF2.DeactivationDate, CF2.MaxUnitCost, CF2.DateCreated, CF2.DateChanged, CF2.DateDeleted

From dbo.BL_ContractFinancingItems CF1

Join dbo.BL_ContractOrganizations Org On Org.ID = CF1.ContractOrganizationID

Join dbo.BL_Contracts C On C.ID = Org.ContractID

Join dbo.BL_ProviderDepartments D ON D.ID = C.FinancingAgentDepartmentID

Join BL_ContractFinancingItems CF2 On CF2.ContractOrganizationID = CF1.ContractOrganizationID

                                                              And CF2.FinancingItemID = CF1.FinancingItemID

                                                              And CF2.DiagnoseTypeID = CF1.DiagnoseTypeID

                                                              And CF2.ID != CF1.ID

                                                              And (CF1.ActivationDate Between CF2.ActivationDate And CF2.DeactivationDate Or

                                                                     CF1.DeactivationDate Between CF2.ActivationDate And CF2.DeactivationDate)

Where CF1.DateDeleted Is Null

       And CF2.DateDeleted Is Null

Order By D.Name, CF1.DateCreated

კონკრეული მაგალითი თანაკვეთის:

Select

       Org.ContractID, P.GeoName, Org.OrganizationID, F.ArtificialCode, CF.FinancingItemID, CF.ActivationDate, CF.DeactivationDate, CF.DiagnoseTypeID, CF.MaxUnitCost, CF.DateCreated, CF.DateChanged, CF.DateDeleted

From dbo.BL_Contracts C

Join dbo.BL_ContractOrganizations Org On Org.ContractID = C.ID

Join dbo.BL_ContractFinancingItems CF ON CF.ContractOrganizationID = Org.ID

Join dbo.BL_FinancingItems F On F.ID = CF.FinancingItemID

Join dbo.BL_Providers P On P.ID = Org.OrganizationID

Where

       --CF.DateDeleted Is Null

       --And

       CF.DiagnoseTypeID = '44C0CCBB-1E8A-4892-A238-D901E67C9694'

       And Org.ID = '5CDC70C2-CD5E-45AF-B789-46BEE7D6CB7D'

       And CF.FinancingItemID = 'DB78971D-565C-4A0E-A06C-134994150E16'

Order By CF.DateCreated, CF.ActivationDate

======================================================================================================================================================================

პირველ შემთხვევაში თუ არის თანაკვეთადი პერიოდები, საყოველთაოს შევთავაზოთ, რომ აღვადგინოთ გადმოგზავნამდე მდგომარეობა (გაუქმდეს დამატებული 2 ფასი და აღდგეს გაუქმებული ფასი), მივცეთ დაწესებულებების სია და თავიდან გადმოაგზავნონ განახლებული ლოგიკით.

მეორე შემთხვევაში გამოსაკვლევია რა მოხდა, რომ შესაბამისად მოვიფიქროთ როგორ უნდა გასწორდეს. გავასწოროთ მხოლოდ ბოლო 4 თვის შემთხვევები.

სერგო