SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT DISTINCT Cs.ShortName as 'Участник (наименование)', count (DISTINCT U.Email) as 'Количество пользователей участника',
CASE WHEN C.ActivityType=2 then 'Да' else 'Нет' END as 'Грузы',
CASE WHEN C.ActivityType=3 then 'Да' else 'Нет' END as 'Заправки',
COUNT(DISTINCT B.ID) as 'Количество ТС', count(distinct TDS.ID) as 'Количество водителей'
FROM [PortalFortisIdentity].[dbo].[AspNetUsers] U
JOIN [FortisDataStore].[dbo].[Contractors] Cs ON U.ClientId=Cs.MemberId
JOIN [FortisDataStore].[dbo].[Contracts] C ON Cs.Id=C.ContractorId
JOIN [FortisDataStore].[dbo].[Clients] D on Cs.INN=D.Inn
JOIN [FortisDataStore].[dbo].[Trucks] B ON D.Id=B.ClientId
JOIN [FortisDataStore].[dbo].[TruckDriverCouples] TDS ON TDS.TruckId = B.Id
WHERE (U.Email NOT LIKE '%fortis%online%' AND U.Email NOT LIKE '%monopoly.su' AND U.Email NOT LIKE '%[А-яЁё]%' AND U.Email LIKE '%@%')
AND (U.UpdatedBy!='QuickRegistration' AND U.IsQuickRegistration!='1')
AND C.ActivityType in (2,3)
AND B.IsClosed='0' AND C.IsClosed='0' AND U.IsClosed=0
AND (((C.FactEndDate>GETDATE() OR C.FactEndDate IS NULL) AND C.AutomaticProlongation IN ('0', '1')) OR C.AutomaticProlongation='2')
GROUP BY Cs.ShortName, C.ActivityType, C.Id
SET DEADLOCK_PRIORITY LOW

SELECT DISTINCT Cl.Name as 'Наименование участника', 
COUNT(DISTINCT Tr.ID) as 'Количество заправок за указанный месяц', SUM(Tr.RefuelVolume) as 'Сумма заправок, л', SUM (TransactionCosts.Cost) as 'Сумма расходов на топливо'
FROM [Fuel].[dbo].[Transactions] AS Tr
join [Fuel].[dbo].[TransactionCosts] ON TransactionCosts.TransactionId = Tr.Id
JOIN [FortisDataStore].[dbo].[Trucks] AS T ON Tr.TruckId=T.Id
JOIN [FortisDataStore].[dbo].[Clients] AS Cl ON Cl.Id=T.ClientId
WHERE Tr.Status = '1' AND      
Tr.RefuelingDate between '2019-03-31 21:00:00.000' and  '2020-03-31 21:00:00.000'                             --период выборки 
GROUP BY Cl.Name