CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
SqlLearner
SqlLearner

Reputation: 793

Looping in Sql to insert data in to table

I have a SQL statement:

select 
    tms.Team, Count(tms.MSApprovedNPC) as JulyNPCResults 
from 
    tmsorder2 tms
where 
    Msapprovednpc not in ('N/A','No') and Month(Month) = 11 and Year(month) = '2013'
group by 
    tms.Team

these results are for a specific month of July 2013.

Now I want to load the results in a table NPRData for other months in to one table which has columns like 

TEAM, JulyNPRResults, AugNPRResults, SepNPRResults, OctNPRResults

How can I loop the above query to insert data for other months?

Thanks in advance

Upvotes: 0

Views: 61

Answers (1)

Hart CO
Hart CO

Reputation: 34774

I would advise against storing your data in a denormalized format. It would be better to have a table like:

NPRData (Team,YearMonth,Results)

But you can do this without looping via a conditional SUM() or COUNT():

SELECT tms.Team
     ,SUM(CASE WHEN MONTH(Month)=11 and YEAR(month) ='2013' THEN 1 END) As NovNPRResults
     ,SUM(CASE WHEN MONTH(Month)=12 and YEAR(month) ='2013' THEN 1 END) As DecNPRResults
FROM tmsorder2 tms
WHERE Msapprovednpc NOT IN ('N/A','No') 
GROUP BY tms.Team

Upvotes: 2

Related Questions