1. Valider que toutes les factures sont présentes et qu'il n'y a aucun doublon
SELECT
      [Invoices].[id]
      ,[Invoices].[ModifiedDate]
      ,[Branches].[Name]
      ,[Invoices].[BillingPeriod]
      ,[Invoices].[Discount]
      ,[Invoices].[Allocation]
      ,[Invoices].[GrandTotal]
      ,[Invoices].[SummaryFile]
  FROM [dbo].[Invoices]
join [dbo].[Branches] on [BranchId] = [Branches].[id]
order by [Invoices].[CreatedDate] desc


  1. Identifier les doublons et les manquants

  3. Les doublons on les efface, ça efface les éléments de invoices et invoiceitems
  4.  
  5. On crée les nouvelles factures
--Ceci est la query pour la facturation RAMQ
  --Attention, filter les bonnes factures seulement!
  
SELECT DISTINCT
      [InvoiceItems].[Manufacturer]
      ,[InvoiceItems].[DrugName]
      ,[InvoiceItems].[DINServed]
      ,[InvoiceItems].[ConcentrationServed]
      ,[AQPPProducts].[Type]
      --Left join avec shape
      ,[InvoiceItems].[SousTotal]
      ,[InvoiceItems].[Discount]
      ,[InvoiceItems].[TotalCost]
      --,[Invoices].[BranchId]
      ,[Branches].[Name]
      ,[Branches].[FullName]
      ,[Branches].[CPOResponsible]
      ,[InvoiceItems].[ProfessionalAllowanceDiscount]
      ,[InvoiceItems].[TransactionTimeStamp]
      ,[InvoiceItems].[InvoiceId]
      ,[Invoices].[BillingPeriod]
  FROM [dbo].[InvoiceItems]
  LEFT join dbo.AQPPProducts on [InvoiceItems].DINServed = AQPPProducts.DIN
  LEFT join dbo.Invoices on InvoiceId = Invoices.Id
  LEFT  join dbo.Branches on [Invoices].[BranchId] = Branches.id
 -- where [TransactionTimeStamp] BETWEEN '2022-01-01'  AND '2022-12-31' 
  Where [Invoices].[SummaryFile] like '%2022%'
 ORDER BY [InvoiceItems].[InvoiceId]



ON valide que le nombre de rangées est bon