USE [MyOnlineBandProd]
GO

/****** Object: SqlProcedure [dbo].[u_downloadsPerMonth] Script Date: 10/21/2016 1:02:37 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[u_downloadsPerMonth]
	(@UserId Uniqueidentifier)
	AS
	SET NOCOUNT ON
	select listenMonth, listenYear,downloadsCount from
  (
select ROW_NUMBER() OVER
(PARTITION By a.userId order by YEAR([ListenDate]) desc ,MONTH([ListenDate])  desc)
AS RowNum,
MONTH([ListenDate]) as listenMonth,
YEAR([ListenDate]) as listenYear,
a.UserId,
count(downloads) as downloadsCount
FROM [DownloadLog] d
  inner join media m
  on m.MediaID=d.MediaId
  inner join vw_Artist a
  on a.UserId=m.UserId
  group by MONTH([ListenDate]), YEAR([ListenDate]) , a.UserId
  ) as query
  where query.RowNum <=12  and
  UserId = @UserId
  order by listenYear  ,listenMonth

  SET NOCOUNT OFF