Creating a View with results from join of scalar tables

selecttableview

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!

Best Answer

Did you try chaining your WITH statements together?

N.B. This may not be the most-optimal method of solving this problem, but should work based on your existing approach

CREATE VIEW dbo.MyViewName
AS
WITH R(N) AS
(
   SELECT 0
   UNION ALL
   SELECT N+1 
   FROM R
   WHERE N < 12
), MonthYears AS
(
  SELECT LEFT(DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())),3) + '-' + 
       CAST(DATEPART(YEAR,DATEADD(MONTH,-N,GETDATE())) as varchar(10)) AS Name
  FROM R
), MonthYearsData AS
(
   SELECT 
     YEAR(RequestedOn) Yr, 
     MONTH(RequestedOn) Mon, 
     SUBSTRING(DateName( month , DateAdd( month , MONTH(RequestedOn) , 0 ) - 1 ),1,3) + '-' + CAST(YEAR(RequestedOn) as varchar(10)) as MonYr, 
     count(*) CrCount
   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;
Related Question