API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Apr 19th, 2016
204
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.66 KB | None | 0 0
raw download clone embed print report
  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
Advertisement
Public Pastes
Advertisement
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!