Guest User

Untitled

a guest
Apr 19th, 2016
358
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.66 KB | None | 0 0
  1. declare @today as date
  2. set @today = DATEADD(month, 0, GETDATE());
  3. declare @snapshot as nvarchar(7)
  4. set @snapshot = LEFT(@today, 4) + '' + RIGHT(LEFT(@today, 7), 2);
  5. declare @product as nvarchar(30)
  6. set @product = 'Pro';
  7. ​
  8. With GrossBookings as (
  9. SELECT
  10. SD.Date_Key
  11. , SD.Country_Key
  12. , STL.SubscriptionType
  13. , STL.SubscriptionChannel
  14. , 'Pro' as ProductName
  15. , SD.License_Key
  16. , C.CountryCode
  17. , SUM(SD.[M_SubscriptionDetail_GrossBookings] ) as GrossBookings
  18. FROM
  19. [EDW].[dbo].[Fact_SubscriptionDetail] SD
  20. join Product P on P.Product_Key = SD.Product_Key
  21. join Date D on D.Date_Key = SD.Date_Key
  22. join Country C on C.Country_Key = SD.Country_Key
  23. join SubscriptionTypeLostSeatsType STL on STL.SubscriptionTypeLostSeatsType_Key = SD.SubscriptionTypeLostSeatsType_Key
  24. WHERE
  25. P.ProductProductToName in ('Pro', <other products>)
  26. AND SD.SnapshotDate_Key like CONCAT(@snapshot, '%')
  27. GROUP BY
  28. SD.Date_Key
  29. , SD.Country_Key
  30. , STL.SubscriptionType
  31. , STL.SubscriptionChannel
  32. , SD.License_Key
  33. , C.CountryCode
  34. )
  35. , Daily_Numbers as (
  36. SELECT
  37. SD.Date_Key
  38. , C.CountryCode
  39. , STL.SubscriptionType
  40. , STL.SubscriptionChannel
  41. , P.ProductProductToName as ProductName
  42. , SUM(SD.[M_SubscriptionDetail_NewSeats] ) as NewSeats
  43. , SUM(SD.[M_SubscriptionDetail_WinbackSeats] ) as WinbackSeats
  44. , SUM(SD.[M_SubscriptionDetail_RenewedFlexSeats] ) as RenewedFlex
  45. , SUM(SD.[M_SubscriptionDetail_RenewedCommitSeats] ) as RenewedCommit
  46. , SUM(SD.[M_SubscriptionDetail_RenewedSeats] ) as RenewedSeats
  47. , SUM(SD.[M_SubscriptionDetail_LostNonPaymentSeats] ) as LostNonPayment
  48. , SUM(SD.[M_SubscriptionDetail_LostGraceInactiveSeats] ) as LostGraceInactive
  49. , SUM(SD.[M_SubscriptionDetail_LostOtherSeats] ) as LostOther
  50. , SUM(SD.[M_SubscriptionDetail_LostSeats] ) as LostSeats
  51. , CASE WHEN STL.SubscriptionType = 'COMMIT' THEN SUM(SD.[M_SubscriptionDetail_LostSeats]) ELSE 0 END AS LostCommit
  52. , CASE WHEN STL.SubscriptionType = 'FLEXI' THEN SUM(SD.[M_SubscriptionDetail_LostSeats]) ELSE 0 END AS LostFlex
  53. , SUM(SD.[M_SubscriptionDetail_ActiveFlexSeats] ) as ActiveFlex
  54. , SUM(SD.[M_SubscriptionDetail_ActiveCommitSeats] ) as ActiveCommit
  55. , SUM(SD.[M_SubscriptionDetail_ActiveSeats] ) as ActiveSeats
  56. , SUM(SD.[M_SubscriptionDetail_ActiveSeatsEndMonth] ) as ActiveEoM
  57. , SUM(GB.GrossBookings) as GrossBookings
  58. FROM
  59. [EDW].[dbo].[Fact_SubscriptionDetail] SD
  60. join Product P on P.Product_Key = SD.Product_Key
  61. join Country C on C.Country_Key = SD.Country_Key
  62. join SubscriptionTypeLostSeatsType STL on STL.SubscriptionTypeLostSeatsType_Key = SD.SubscriptionTypeLostSeatsType_Key
  63. join GrossBookings GB on GB.Date_Key = SD.Date_Key and GB.Country_Key = SD.Country_Key and GB.ProductName = P.ProductProductToName and GB.License_Key = SD.License_Key
  64. WHERE
  65. SD.SnapshotDate_Key like CONCAT(@snapshot, '%')
  66. and P.ProductProductToName = @product
  67. GROUP BY
  68. SD.Date_Key
  69. , C.CountryCode
  70. , P.ProductProductToName
  71. , STL.SubscriptionType
  72. , STL.SubscriptionChannel
  73. )
  74. , FlexInfo as (
  75. SELECT
  76. D2.Date_Key
  77. , C.CountryCode
  78. , STL.SubscriptionType
  79. , STL.SubscriptionChannel
  80. , P.ProductProductToName
  81. , SUM(SD.[M_SubscriptionDetail_RenewedFlexSeats]) as RenewedFlex
  82. , SUM(SD.M_SubscriptionDetail_RenewedCommitSeats) as RenewedCommit
  83. , CASE WHEN STL.SubscriptionType = 'FLEXI' THEN SUM(SD.[M_SubscriptionDetail_LostSeats]) ELSE 0 END AS LostFlex
  84. , CASE WHEN STL.SubscriptionType = 'COMMIT' THEN SUM(SD.[M_SubscriptionDetail_LostSeats]) ELSE 0 END AS LostCommit
  85. , SUM(SD.M_SubscriptionDetail_WinbackSeats) as Winback
  86. , SUM(SD.M_SubscriptionDetail_NewSeats) as New
  87. FROM
  88. [EDW].[dbo].[Fact_SubscriptionDetail] SD
  89. JOIN Date D1 on D1.Date_Key = SD.Date_Key
  90. JOIN Date D2 on D2.Date_Key = YEAR(DATEADD(day, 1, EOMONTH(D1.Date))) * 10000 + MONTH(DATEADD(day, 1, EOMONTH(D1.Date))) * 100 + DAY(DATEADD(day, 1, EOMONTH(D1.Date)))
  91. join Product P on P.Product_Key = SD.Product_Key
  92. join Country C on C.Country_Key = SD.Country_Key
  93. join SubscriptionTypeLostSeatsType STL on STL.SubscriptionTypeLostSeatsType_Key = SD.SubscriptionTypeLostSeatsType_Key
  94. WHERE
  95. SD.SnapshotDate_Key like CONCAT(@snapshot, '%')
  96. and P.ProductProductToName = @product
  97. GROUP BY
  98. D2.Date_Key
  99. , C.CountryCode
  100. , STL.SubscriptionType
  101. , STL.SubscriptionChannel
  102. , P.ProductProductToName
  103. )
  104. , Totals as (
  105. SELECT
  106. DN.Date_Key
  107. , DN.CountryCode
  108. , DN.SubscriptionChannel
  109. , DN.ProductName
  110. , SUM(DN.NewSeats ) as NewSeats
  111. , SUM(DN.WinbackSeats ) as WinbackSeats
  112. , SUM(DN.RenewedFlex ) as RenewedFlex
  113. , SUM(DN.RenewedCommit ) as RenewedCommit
  114. , SUM(DN.RenewedSeats ) as RenewedSeats
  115. , SUM(-DN.LostNonPayment) as LostNonPayment
  116. , SUM(-DN.LostGraceInactive) as LostGraceInactive
  117. , SUM(-DN.LostOther ) as LostOther
  118. , SUM(-DN.LostSeats ) as LostSeats
  119. , SUM(-DN.LostFlex ) as LostFlex
  120. , SUM(-DN.LostCommit ) as LostCommit
  121. , SUM(DN.GrossBookings ) as GrossBookings
  122. , SUM(DN.ActiveFlex) OVER (ORDER BY DN.Date_Key)
  123. - SUM(COALESCE(F.RenewedFlex, 0)) OVER (ORDER BY DN.Date_Key)
  124. + SUM(COALESCE(F.LostFlex, 0)) OVER (ORDER BY DN.Date_Key)
  125. - SUM(COALESCE(F.Winback, 0)) OVER (ORDER BY DN.Date_Key)
  126. as RunningFlex
  127. , SUM(DN.NewSeats) OVER (ORDER BY DN.Date_Key)
  128. + SUM(COALESCE(DN.RenewedCommit, 0)) OVER (ORDER BY DN.Date_Key)
  129. + SUM(COALESCE(-DN.LostCommit, 0)) OVER (ORDER BY DN.Date_Key)
  130. - (
  131. SUM(COALESCE(F.New, 0)) OVER (ORDER BY DN.Date_Key)
  132. + SUM(COALESCE(F.RenewedCommit, 0)) OVER (ORDER BY DN.Date_Key)
  133. + SUM(COALESCE(-F.LostCommit, 0)) OVER (ORDER BY DN.Date_Key)
  134. )
  135. as RunningCommit
  136. , SUM(DN.ActiveFlex) OVER (ORDER BY DN.Date_Key)
  137. - SUM(COALESCE(F.RenewedFlex, 0)) OVER (ORDER BY DN.Date_Key)
  138. + SUM(COALESCE(F.LostFlex, 0)) OVER (ORDER BY DN.Date_Key)
  139. - SUM(COALESCE(F.Winback, 0)) OVER (ORDER BY DN.Date_Key)
  140. + SUM(DN.NewSeats) OVER (ORDER BY DN.Date_Key)
  141. + SUM(COALESCE(DN.RenewedCommit, 0)) OVER (ORDER BY DN.Date_Key)
  142. + SUM(COALESCE(-DN.LostCommit, 0)) OVER (ORDER BY DN.Date_Key)
  143. - (
  144. SUM(COALESCE(F.New, 0)) OVER (ORDER BY DN.Date_Key)
  145. + SUM(COALESCE(F.RenewedCommit, 0)) OVER (ORDER BY DN.Date_Key)
  146. + SUM(COALESCE(-F.LostCommit, 0)) OVER (ORDER BY DN.Date_Key)
  147. )
  148. as RunningTotal
  149. FROM
  150. Daily_Numbers DN
  151. JOIN Date D on D.Date_Key = DN.Date_Key
  152. LEFT JOIN FlexInfo F on F.Date_Key = DN.Date_Key and F.SubscriptionType = DN.SubscriptionType and F.SubscriptionChannel = DN.SubscriptionChannel and F.CountryCode = DN.CountryCode --and F.ProductProductToName = DN.ProductName
  153. GROUP BY
  154. DN.Date_Key
  155. , DN.CountryCode
  156. , DN.SubscriptionChannel
  157. , DN.ProductName
  158. , DN.NewSeats
  159. , DN.ActiveFlex
  160. , DN.ActiveCommit
  161. , DN.ActiveSeats
  162. , DN.RenewedFlex
  163. , DN.RenewedCommit
  164. , DN.RenewedSeats
  165. , DN.LostFlex
  166. , DN.LostCommit
  167. , F.RenewedFlex
  168. , F.RenewedCommit
  169. , F.LostFlex
  170. , F.Winback
  171. , F.New
  172. , F.LostCommit
  173. )
  174. ​
  175. SELECT
  176. T.Date_Key
  177. , T.CountryCode
  178. , T.SubscriptionChannel
  179. , CAST('Seats' as nvarchar(100)) as ProductName
  180. , SUM(T.NewSeats ) as NewSeats
  181. , SUM(T.WinbackSeats ) as WinbackSeats
  182. , SUM(T.RenewedFlex ) as RenewedFlex
  183. , SUM(T.RenewedCommit ) as RenewedCommit
  184. , SUM(T.RenewedSeats ) as RenewedSeats
  185. , SUM(T.LostNonPayment ) as LostNonPayment
  186. , SUM(T.LostGraceInactive) as LostGraceInactive
  187. , SUM(T.LostOther ) as LostOther
  188. , SUM(T.LostSeats ) as LostSeats
  189. , SUM(T.LostFlex ) as LostFlex
  190. , SUM(T.LostCommit ) as LostCommit
  191. , SUM(T.GrossBookings ) as GrossBookings
  192. , T.RunningFlex
  193. , T.RunningCommit
  194. , T.RunningTotal
  195. FROM
  196. Totals T
  197. GROUP BY
  198. T.Date_Key
  199. , T.CountryCode
  200. , T.SubscriptionChannel
  201. , T.RunningFlex
  202. , T.RunningCommit
  203. , T.RunningTotal
  204. ORDER BY
  205. T.Date_Key
Advertisement
Add Comment
Please, Sign In to add comment