Need to perform a loop

Posted on 2009-05-26
Medium Priority
Last Modified: 2012-05-07
I have a query that I need to be able to run for an entire year (once a month) and for each of my business areas (18 of them). So I'm looking for a way to generate results like the attached file (our regions are on the left - there are 18 of them) and each month of the year appears to the top of the page beginning from July (or Financial Year start) and ending in June.

Preferrably I'd like to be able to pass a parameter of the year to the query so this basically built the stats for the requested year (from July to June obviously).

I've attached the code I'm currently using to manually check each month of the year so far so this gives you an idea of what I'm looking to do. However, one of the stumbling blocks I see is that the query will return only the regions that have data. I need to be able to show if a region had 0 entries for that month (the data doesn't contain this obviously). So I'm wondering if every field needs to be 0 unless overwritten by a value from the query but as I say I'm not sure about this part.

Any advice/assistance that you can offer is appreciated.
SELECT     COUNT(FilteredAccount.new_region) AS Awards
FROM         FilteredAccount INNER JOIN
                      FilteredOpportunity ON FilteredAccount.accountid = FilteredOpportunity.accountid
WHERE     ((FilteredOpportunity.new_fundsreleaseddate >= '01/01/2009') AND (FilteredOpportunity.new_fundsreleaseddate <= '01/31/2009')) AND ((FilteredOpportunity.new_producttype <> 6) OR ((FilteredOpportunity.new_startupflag = 1) AND ((FilteredOpportunity.new_startupdate >= '01/01/2009') AND (FilteredOpportunity.new_startupdate <='01/31/2009'))) )
GROUP BY FilteredAccount.new_region
ORDER BY FilteredAccount.new_region

Open in new window

Question by:Steven O'Neill
  • 2
LVL 31

Accepted Solution

RiteshShah earned 2000 total points
ID: 24471995
well, you are looking for PIVOT, have a look at my blog at below link.


first article will be difficult so you can skip it and can look at other articles.
LVL 31

Expert Comment

ID: 24471997

Author Closing Comment

by:Steven O'Neill
ID: 31585243
Thanx for this. PIVOT is the way to go to do this but I'm having a few difficulties getting this sorted but at least I'm onto the right direction now.

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question