TSQL While Loop over months in year

Question

I have an output that I need to achieve and I am not too certain how to go about it.

I first need to start by looping over each month in the year and using that month in a select statement to check for data.

For example:

Select * from table where MONTH(A.[submissionDate]) = 1
Select * from table where MONTH(A.[submissionDate]) = 2
Select * from table where MONTH(A.[submissionDate]) = 3

My end result is to create this XML output to use with a chart plugin. It needs to include the months even if there is no data which is why I wanted to loop through each month to check for it.

    <root>
   <dataSet>
      <areaDesc>Area 1</areaDesc>
      <data>
         <month>January</month>
         <monthValue>1</monthValue>
         <submissions>0</submissions>
      </data>
      <data>
         <month>February</month>
         <monthValue>2</monthValue>
         <submissions>7</submissions>
      </data>
      <data>
         <month>March</month>
         <monthValue>3</monthValue>
         <submissions>5</submissions>
      </data>
   </dataSet>
   <dataSet>
      <areaDesc>Area 2</areaDesc>
      <data>
         <month>January</month>
         <monthValue>1</monthValue>
         <submissions>0</submissions>
      </data>
      <data>
         <month>February</month>
         <monthValue>2</monthValue>
         <submissions>7</submissions>
      </data>
      <data>
         <month>March</month>
         <monthValue>3</monthValue>
         <submissions>5</submissions>
      </data>
   </dataSet>
</root>

I may be way over thinking this but I'm hoping I talking it through may help me out a little.

Here is my current set up of how I get some other stats:

--Temp table     
DECLARE @areas TABLE (
    area VARCHAR (100));
IF @dept = 'global'
   OR @dept = ''
    BEGIN
        INSERT INTO @areas (area)
        SELECT DISTINCT(AreaDesc)
        FROM dbo.EmpTable;
    END
ELSE
    BEGIN
        INSERT INTO @areas
        SELECT @dept;
    END

IF (@action = 'compare')
        BEGIN
            SELECT   DATENAME(month, A.[submissionDate]) AS [month],
                     MONTH(A.[submissionDate]) AS [monthValue],
                     count(A.[submissionID]) AS submissions,
                     B.[AreaDesc]
            FROM     empowermentSubmissions AS A
            INNER JOIN empTable AS B
            ON A.[nomineeQID] = B.[QID]
            WHERE    YEAR(A.[submissionDate]) = @year
                     AND A.[statusID] = 3
                     AND A.[locationID] IN (SELECT location
                                            FROM   @table)
            GROUP BY DATENAME(month, A.[submissionDate]), MONTH(A.[submissionDate]), B.[AreaDesc]
            ORDER BY [monthValue] ASC
            FOR      XML PATH ('dataSet'), TYPE, ELEMENTS, ROOT ('root');
        END
    ELSE

Answer 1

You'll want to do a FOR XML structure to get the exact result set you're looking for in one go, I think. I put this together with what I could glean about your XML. Just change the name of the table variable here to your real table name and this should work.

EDIT: changed up the query to match the definition from the posted query. Updated the data element where clause to maintain month instantiation when zero counts were found in a month.

EDIT: Added Status requirement.

EDIT: Moved areaDesc criteria for constant month output.

declare @empowermentSubmissions table (submissionID int primary key identity(1,1), submissionDate datetime, nomineeQID INT, statusID INT)
declare @empTable table (QID int primary key identity(1,1), AreaDesc varchar(10))
declare @n int = 1

while @n < 50
begin
    insert into @empTable (AreaDesc) values ('Area ' + cast((@n % 2)+1 as varchar(1)))
    set @n = @n + 1
end
set @n = 1
while @n < 500
begin
    insert into @empowermentSubmissions (submissionDate, nomineeQID, StatusID) values (dateadd(dd,-(cast(rand()*600 as int)),getdate()), (select top 1 QID from @empTable order by newid()), 3 + (@n % 2) - (@n % 3) )
    set @n = @n + 1
end


declare @year int = 2014

select (
    select (
        select (
            select e1.areaDesc
            from @empTable e1
            where e1.areaDesc = e2.areaDesc
            group by e1.areaDesc
            for xml path(''),type
            )
            , (
                select [month], [monthValue], count(s1.submissionID) as submissions
                from (
                    select @year [Year]
                        , datename(month,dateadd(mm,RowID-1,@year-1900)) [Month]
                        , month(dateadd(mm,RowID-1,@year-1900)) [MonthValue]
                    from (
                        select *, row_number()over(order by name) as RowID
                        from master..spt_values
                        ) d
                    where d.RowID <= 12
                    ) t
                    left join (
                        select s3.submissionID, s3.submissionDate, e3.AreaDesc
                        from @empowermentSubmissions s3
                            inner join @empTable e3 on s3.nomineeQID = e3.QID
                        where s3.statusID = 3
                            and e3.areaDesc = e2.areaDesc
                        ) s1 on year(s1.submissionDate) = t.[Year]
                        and month(s1.submissionDate) = t.[MonthValue]
                group by [Month], [MonthValue]
                order by [MonthValue]
                for xml path('data'),type
            )
        for xml path(''),type
        ) dataset
    from @empowermentSubmissions s2
        inner join @empTable e2 on s2.nomineeQID = e2.QID
    group by e2.areaDesc
    for xml path(''), type
    ) root
for xml path (''), type

Answer 2

You should be able to use a tally table to get the months:

SELECT TOP 12 IDENTITY(INT,1,1) AS N
INTO #tally
FROM master.dbo.syscolumns sc1

SELECT DATENAME(MONTH,DATEADD(MONTH,t.N-1,'2014-01-01')) AS namemonth, t.N AS monthvalue, COUNT(tbl.submissionDate) AS submissions, tbl.Area
FROM #tally t
LEFT OUTER JOIN tbl ON MONTH(tbl.submissionDate) = t.N
GROUP BY t.n, tbl.Area

DROP TABLE #tally

Answer 3

This is a great application for a "Dates" table or view. Create a new table in your database with schema like:

CREATE TABLE dbo.Dates (
    Month INT,
    MonthName VARCHAR(20)
)

Populate this table with the years and months you may want to aggregate over. Then, you can make your query like:

SELECT 
    Area
    Dates.MonthName,
    COUNT(*) AS Count
FROM
    dbo.Dates
LEFT OUTER JOIN
    dbo.Submissions
        AND Dates.Month = MONTH(Submissions.SubmissionDate)
GROUP BY
    Dates.MonthName,
    Area

The LEFT OUTER JOIN will give you one row for every Year and Month in the dates table, and a count of any submissions on that month. You end up with output like:

Area    | MonthName | Count
Area 1  | Jan       | 0
Area 2  | Feb       | 2

&c.