Queries to verify billing integritiy

select sum(Total) from Bills where NewBillId is not null and VatAccountId=1 and Status in (3,4) and CreatedOn >= '2025-01-01' and CreatedOn < '2026-01-01';

select sum(Total) from Bills where id in (select NewBillId from Bills where NewBillId is not null and VatAccountId=1 and Status in (3,4) and CreatedOn >= '2025-01-01' and CreatedOn < '2026-01-01') and status in (1,2);

select sum(EndCounter-StartCounter) from AircraftLogs where id in ( select AircraftLogId from Transactions where billid in ( select id from Bills where NewBillId is not null and VatAccountId=1 and Status in (3,4) and CreatedOn >= '2025-01-01' and CreatedOn < '2026-01-01'));

select sum(Quantity) from Transactions where BillId in (select ID from Bills where NewBillId is not null and VatAccountId=1 and Status in (3,4) and CreatedOn >= '2025-01-01' and CreatedOn < '2026-01-01') and AircraftLogId is not null and ArticleId <> 10;

select sum(Quantity) from Transactions where BillId in ( select id from Bills where id in (select NewBillId from Bills where NewBillId is not null and VatAccountId=1 and Status in (3,4) and CreatedOn >= '2025-01-01' and CreatedOn < '2026-01-01') and status in (1,2)) and AircraftLogId is not null and ArticleId <> 10;

select sum(mx-mn) from (select max(EndCounter) as "mx", min(StartCounter) as "mn",Aircrafts.CallSign from AircraftLogs left join Aircrafts on AircraftLogs.AircraftId = Aircrafts.Id where DepartureTime >= '2025-01-01' and DepartureTime < '2026-01-01' and AircraftId <> 1488 and AircraftId <> 3318 and AircraftId <> 72 and AircraftId <> 73 group by Aircrafts.CallSign) as q1;

select max(EndCounter) as "mx", min(StartCounter) as "mn",Aircrafts.CallSign from AircraftLogs left join Aircrafts on AircraftLogs.AircraftId = Aircrafts.Id where DepartureTime >= '2025-01-01' and DepartureTime < '2026-01-01' and AircraftId <> 1488 and AircraftId <> 3318 and AircraftId <> 72 and AircraftId <> 73 AND FlightActivityTypeId in (1,7,11) group by Aircrafts.CallSign;

select sum(EndCounter-StartCounter) as "qt",Aircrafts.CallSign from AircraftLogs left join Aircrafts on AircraftLogs.AircraftId = Aircrafts.Id where DepartureTime >= '2025-01-01' and DepartureTime < '2026-01-01' and AircraftId <> 1488 and AircraftId <> 3318 and AircraftId <> 72 and AircraftId <> 73 AND FlightActivityTypeId in (1,7,11) group by Aircrafts.CallSign order by qt desc;

SELECT Aircrafts.CallSign, COUNT() "NbDiffs", SUM(Transactions.Quantity) "Billded", SUM(AircraftLogs.EndCounter-AircraftLogs.StartCounter) "AircraftLogTotal" -- SELECT Aircrafts.CallSign, Bills.Id ,Transactions. , (AircraftLogs.EndCounter-AircraftLogs.StartCounter) "AircraftLogTotal" -- SELECT AircraftLogs.* From Transactions left join AircraftLogs on AircraftLogs.Id = Transactions.AircraftLogId left join Aircrafts on Aircrafts.id = AircraftLogs.AircraftId -- left join ArticlePrices on Transactions.ArticlePriceId = ArticlePrices.Id inner join Bills on Transactions.BillId = Bills.Id where AircraftLogs.AircraftId <> 1488 and AircraftLogs.AircraftId <> 3318 and AircraftLogs.AircraftId <> 72 and AircraftLogs.AircraftId <> 73 and Transactions.Quantity <> (AircraftLogs.EndCounter-AircraftLogs.StartCounter) and FlightActivityTypeId in (1,7,11) and DepartureTime >= '2025-03-01' and DepartureTime < '2026-01-01' and Bills.Status in (1,2) -- and AircraftLogs.Id = 22762 and Transactions.ArticleId IN (11,12) -- and ArticlePrices.ArticleId IN (11,12) order by AircraftLogs.id desc

-- group by Aircrafts.CallSign