SELECT year(T0.[xCreatedAt]) as 'Év'
,DATEPART(wk,T0.[xCreatedAt]) as 'Hét'
,T0.[TicketCode]
,T2.[SAPCardCode]
,T2.[CompanyName]
,T7.SalesPersonName as 'Értékesítő'
,convert(date, T0.[xCreatedAt]) as xCreatedAt
,convert(date, T0.[xModifiedAt]) as xModifiedAt
,T0.TicketStatus as 'Ügy státusza'
,T1.TicketTypeName
,T3.TicketTypeName
,T5.QuittingName as 'Felmondás oka'
,T4.TerminationName as 'Felmondás lezárása'

,(SELECT COUNT(X0.targetid) as Aktiv_target_szam FROM (SELECT        S0.CardCode, s1.targetid
FROM            (SELECT * FROM dbo.[SapIdagenCo] WHERE (ValidFrom <= GETDATE()) AND (ValidTo IS NULL OR ValidTo >= GETDATE())) AS S0 INNER JOIN
                        (SELECT * FROM dbo.[SapIdagenCot] WHERE (ValidFrom <= GETDATE()) AND (ValidTo IS NULL OR ValidTo >= GETDATE())) AS S1 ON S0.DocEntry = S1.DocEntry INNER JOIN
                        (SELECT * FROM dbo.[SapIdagenCoti] WHERE (ValidFrom <= GETDATE()) AND (ValidTo IS NULL OR ValidTo >= GETDATE())) AS S2 ON S0.DocEntry = S2.DocEntry AND S1.LineId = S2.TargetLineNum
WHERE (CASE WHEN S2.[endDate] IS NULL AND S2.[startDate] <= convert(date, T0.[xCreatedAt]) THEN 'Aktív'
WHEN S2.[endDate] >= convert(date, T0.[xCreatedAt]) AND S2.[startDate] <= convert(date, T0.[xCreatedAt]) THEN 'Aktív'
WHEN S2.[endDate] = S2.[startDate] THEN 'Inaktív' ELSE 'Inaktív' END) = 'Aktív' AND S0.CardCode = SAPCardCode group by s0.CardCode, s1.TargetId) AS X0 GROUP BY X0.CardCode) AS Aktiv_targetek_szama_letrehozas

,(SELECT sum(X0.Ar) as Ertek FROM (SELECT    S0.CardCode, sum(s2.Price) as Ar
FROM            (SELECT * FROM dbo.[SapIdagenCo] WHERE (ValidFrom <= GETDATE()) AND (ValidTo IS NULL OR ValidTo >= GETDATE())) AS S0 INNER JOIN
                         (SELECT * FROM dbo.[SapIdagenCot] WHERE (ValidFrom <= GETDATE()) AND (ValidTo IS NULL OR ValidTo >= GETDATE())) AS S1 ON S0.DocEntry = S1.DocEntry INNER JOIN
                         (SELECT * FROM dbo.[SapIdagenCoti] WHERE (ValidFrom <= GETDATE()) AND (ValidTo IS NULL OR ValidTo >= GETDATE())) AS S2 ON S0.DocEntry = S2.DocEntry AND S1.LineId = S2.TargetLineNum
WHERE (CASE WHEN S2.[endDate] IS NULL AND S2.[startDate] <= convert(date, T0.[xCreatedAt]) THEN 'Aktív'
WHEN S2.[endDate] >= convert(date, T0.[xCreatedAt]) AND S2.[startDate] <= convert(date, T0.[xCreatedAt])THEN 'Aktív'
WHEN S2.[endDate] = S2.[startDate] THEN 'Inaktív' ELSE 'Inaktív' END) = 'Aktív' AND S0.CardCode = SAPCardCode group by s0.CardCode) AS X0 GROUP BY X0.CardCode) AS Szerzallomany_ertek_letrehozas

FROM (SELECT * FROM [IDATA_DW].[dbo].[AdamasTicket] WHERE (ValidFrom <= GETDATE()) AND (ValidTo IS NULL OR ValidTo >= GETDATE())) AS T0
  LEFT JOIN (SELECT * FROM [IDATA_DW].[dbo].[AdamasTicketType] WHERE (VaidFrom <= GETDATE()) AND (VaidTo IS NULL OR VaidTo >= GETDATE())) AS T1 ON T0.TicketTypeID = T1.TicketTypeID
  LEFT JOIN (SELECT * FROM [IDATA_DW].[dbo].[AdamasCompany] WHERE (ValidFrom <= GETDATE()) AND (ValidTo IS NULL OR ValidTo >= GETDATE())) AS T2 ON T0.CompanyID = T2.CompanyID
  LEFT JOIN (SELECT * FROM [IDATA_DW].[dbo].[AdamasTicketType] WHERE (VaidFrom <= GETDATE()) AND (VaidTo IS NULL OR VaidTo >= GETDATE())) AS T3 ON T0.TicketType2ID = T3.TicketTypeID
  LEFT JOIN (SELECT * FROM [IDATA_DW].[dbo].[AdamasTermination] WHERE (ValidFrom <= GETDATE()) AND (ValidTo IS NULL OR ValidTo >= GETDATE())) as t4 on t4.TerminationID= t0.TerminationID
  LEFT JOIN (SELECT * FROM [IDATA_DW].[dbo].[AdamasQuitting] WHERE (ValidFrom <= GETDATE()) AND (ValidTo IS NULL OR ValidTo >= GETDATE())) as t5 on t5.QuittingID=t0.QuittingID
  LEFT JOIN (SELECT * FROM [IDATA_DW].[dbo].[SapCustomer] WHERE (ValidFrom <= GETDATE()) AND (ValidTo IS NULL OR ValidTo >= GETDATE())) as t6 on t6.CardCode=t2.SAPCardCode and t6.ValidTo is null
  LEFT JOIN (SELECT * FROM [IDATA_DW].[dbo].[SapSalesperson] WHERE (ValidFrom <= GETDATE()) AND (ValidTo IS NULL OR ValidTo >= GETDATE())) as t7 on t6.SlpCode=t7.SalesPersonID

  WHERE T1.TicketTypeName like '%Felmondás%'