SHARE
TWEET

Untitled

zolichess Nov 28th, 2019 203 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT year(T0.[xCreatedAt]) as 'Év'
  2. ,DATEPART(wk,T0.[xCreatedAt]) as 'Hét'
  3. ,T0.[TicketCode]
  4. ,T2.[SAPCardCode]
  5. ,T2.[CompanyName]
  6. ,T7.SalesPersonName as 'Értékesítő'
  7. ,convert(date, T0.[xCreatedAt]) as xCreatedAt
  8. ,convert(date, T0.[xModifiedAt]) as xModifiedAt
  9. ,T0.TicketStatus as 'Ügy státusza'
  10. ,T1.TicketTypeName
  11. ,T3.TicketTypeName
  12. ,T5.QuittingName as 'Felmondás oka'
  13. ,T4.TerminationName as 'Felmondás lezárása'
  14.  
  15. ,(SELECT COUNT(X0.targetid) as Aktiv_target_szam FROM (SELECT        S0.CardCode, s1.targetid
  16. FROM            (SELECT * FROM dbo.[SapIdagenCo] WHERE (ValidFrom <= GETDATE()) AND (ValidTo IS NULL OR ValidTo >= GETDATE())) AS S0 INNER JOIN
  17.                         (SELECT * FROM dbo.[SapIdagenCot] WHERE (ValidFrom <= GETDATE()) AND (ValidTo IS NULL OR ValidTo >= GETDATE())) AS S1 ON S0.DocEntry = S1.DocEntry INNER JOIN
  18.                         (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
  19. WHERE (CASE WHEN S2.[endDate] IS NULL AND S2.[startDate] <= convert(date, T0.[xCreatedAt]) THEN 'Aktív'
  20. WHEN S2.[endDate] >= convert(date, T0.[xCreatedAt]) AND S2.[startDate] <= convert(date, T0.[xCreatedAt]) THEN 'Aktív'
  21. 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
  22.  
  23. ,(SELECT sum(X0.Ar) as Ertek FROM (SELECT    S0.CardCode, sum(s2.Price) as Ar
  24. FROM            (SELECT * FROM dbo.[SapIdagenCo] WHERE (ValidFrom <= GETDATE()) AND (ValidTo IS NULL OR ValidTo >= GETDATE())) AS S0 INNER JOIN
  25.                          (SELECT * FROM dbo.[SapIdagenCot] WHERE (ValidFrom <= GETDATE()) AND (ValidTo IS NULL OR ValidTo >= GETDATE())) AS S1 ON S0.DocEntry = S1.DocEntry INNER JOIN
  26.                          (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
  27. WHERE (CASE WHEN S2.[endDate] IS NULL AND S2.[startDate] <= convert(date, T0.[xCreatedAt]) THEN 'Aktív'
  28. WHEN S2.[endDate] >= convert(date, T0.[xCreatedAt]) AND S2.[startDate] <= convert(date, T0.[xCreatedAt])THEN 'Aktív'
  29. 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
  30.  
  31. FROM (SELECT * FROM [IDATA_DW].[dbo].[AdamasTicket] WHERE (ValidFrom <= GETDATE()) AND (ValidTo IS NULL OR ValidTo >= GETDATE())) AS T0
  32.   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
  33.   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
  34.   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
  35.   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
  36.   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
  37.   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
  38.   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
  39.  
  40.   WHERE T1.TicketTypeName like '%Felmondás%'
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top