I am really bad at SQL Queries and seem to be stuck at a very odd problem. Maybe there's an alternative of doing this, but I simply can't figure it out. Maybe you guys can help!
So, here's my objective : I need to show the trending graph for the last 12 months and I need to generate a view out of my existing table having two columns : Month+Year and Count. So it will be like "Jul-2014 97" ish records.
I tried the following approach where I am first creating a scalar table with last 12 months Month+Year combination like "Jul-2014", etc. Second I am creating another scalar table with "Month+Year" and "Count" combination like "Jul-2014 97". The thing is the second table will not have data for all the last 12 months. So I am doing a LEFT OUTER JOIN to get all Month+Year data.
My query is as follows:
DECLARE @MonthYears TABLE (LNo INT IDENTITY(1,1), Name Varchar(50)) DECLARE @MonthYearsData TABLE (Yr INT, Mon INT, MonYr varchar(50), CrCount INT) SET LANGUAGE English; WITH R(N) AS ( SELECT 0 UNION ALL SELECT N+1 FROM R WHERE N < 12 ) INSERT INTO @MonthYears (Name) SELECT LEFT(DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())),3) + '-' + CAST(DATEPART(YEAR,DATEADD(MONTH,-N,GETDATE())) as varchar(10)) AS Last12Mon FROM R INSERT INTO @MonthYearsData(Yr, Mon, MonYr, CrCount) SELECT YEAR(RequestedOn), MONTH(RequestedOn), SUBSTRING(DateName( month , DateAdd( month , MONTH(RequestedOn) , 0 ) - 1 ),1,3) + '-' + CAST(YEAR(RequestedOn) as varchar(10)) as MonYr, count(*) FROM TempTableTest WHERE RequestedOn >= DATEADD(MONTH, -13, GETDATE()) GROUP BY YEAR(RequestedOn), MONTH(RequestedOn) SELECT M.Name, ISNULL(N.CrCount,0) as CRCount FROM @MonthYears M left outer join @MonthYearsData N ON M.Name = N.MonYr GROUP BY M.Name, N.CrCount, M.LNo ORDER BY M.LNo DESC
So, I am getting response as follows:
Name CRCount Dec-2013 0 Jan-2014 0 Feb-2014 0 Mar-2014 0 Apr-2014 0 May-2014 0 Jun-2014 0 Jul-2014 0 Aug-2014 1 Sep-2014 0 Oct-2014 0 Nov-2014 0 Dec-2014 2
Now I need this in a View, so I can use it against my reporting tool. However, I am unable to create one using this scalar table approach. Even temporary table fails. I am really in a deadlock. Can someone please point out an approach?
P.S. : I tried dynamically creating the scalar tables inside the last SELECT query, but haven't had any luck.
Thanks in advance!