SQL query design ideas

I am creating a report that will have a date range the user will specify. Let's say 1/1/2014 to 12/31/2014. There needs to be a column for each month within the date range specified. We have existing reports that have a static date range which makes things easier. In other words, the user can only select a year. In which case the query hardcodes the 12 months like:
SELECT pfl.accountnumber
,isnull(sum(case Month(pfl.createdon) when 1  then pfl.TotalPremium end),0)  as JANTP            
,isnull(sum(case Month(pfl.createdon) when 2  then pfl.TotalPremium end),0)  as FEBTP            
,isnull(sum(case Month(pfl.createdon) when 3  then pfl.TotalPremium end),0)  as MARTP          
,isnull(sum(case Month(pfl.createdon) when 4  then pfl.TotalPremium end),0)  as APRTP            
,isnull(sum(case Month(pfl.createdon) when 5  then pfl.TotalPremium end),0)  as MAYTP            
,isnull(sum(case Month(pfl.createdon) when 6  then pfl.TotalPremium end),0)  as JUNTP            
,isnull(sum(case Month(pfl.createdon) when 7  then pfl.TotalPremium end),0)  as JULTP            
,isnull(sum(case Month(pfl.createdon) when 8  then pfl.TotalPremium end),0)  as AUGTP            
,isnull(sum(case Month(pfl.createdon) when 9  then pfl.TotalPremium end),0)  as SEPTP            
,isnull(sum(case Month(pfl.createdon) when 10 then pfl.TotalPremium end),0)  as OCTTP            
,isnull(sum(case Month(pfl.createdon) when 11 then pfl.TotalPremium end),0)  as NOVTP            
,isnull(sum(case Month(pfl.createdon) when 12 then pfl.TotalPremium end),0)  as DECTP 

Open in new window

However, for this new report, the user can select any date range across multiple years. So I don't think I will be able to hardcode the months into CASE statements.

Desired Output: for date range: 11/1/2013 - 12/31/2014
AccountNumber  Nov13 Dec13 Jan14 Feb14 Mar14 Apr14 May14 Jun14 Jul14 Aug14 Sep14 Oct14 Nov14 Dec14
94867                        25          5        45       78       64          7       48         25       71    67          53     37        91          14
94868                        13          7        35       18       63          8       38         95       77    64          73     17        81          34

Requirement: 1 row per account number

Any ideas how to accomplish this?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>the user can only select a year. In which case the query hardcodes the 12 months like:
Instead of column names that contain the month/year, you'll need to pull this off with column names like current_month, current_month_minus_one, current_month_minus_two, etc. based on the user input.   That way the column names stay static and it doesn't break the report.

To display the column names in the report, use a report expression that keys off of the current month, and do DATEADD() stuff to work backwards from that date.

Of course, you'll have to deal with page width issues if the user enters a date range that goes beyond the width of paper.
pzozulkaAuthor Commented:
I'm not seeing how to achieve that. Let's say the user selects a wide date range from 11/1/2012 to 12/31/2014. How would I create 26 months (columns)? Can you give me a code sample of how to generate X amount of months?

Are there any other ways of doing this? Pivot table in SQL, Dynamic SQL? Neither of which I am familiar with, but read that it might be able to accomplish this.
pzozulkaAuthor Commented:
Here's another idea, the code below generates the number of rows, 1 row per month. Is there a way to convert that into columns, using pivot?

DECLARE @startDate DATETIME = '11/1/2012'
DECLARE @endDate DATETIME = '12/31/2014'

cteTally10 AS (                    
cteTally100 AS (                    
SELECT *                    
FROM (                    
SELECT ROW_NUMBER() OVER (ORDER BY c1.tally) - 1 AS tally                    
FROM cteTally10 c1 CROSS JOIN cteTally10 c2                    
) x                    
WHERE tally BETWEEN 0 AND DATEDIFF(MONTH, @startDate, @endDate)                    

select * from cteTally100

Open in new window

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Koen Van WielinkBusiness Intelligence SpecialistCommented:
I've done a similar thing with pivots once, but it gets complicated because your column headers are not fixed. So you need to use some code inside the report to retrieve the column header names correctly. You don't mention what kind of reporting tool you're using. I was forced to use pivot in SQL because the SSRS 2005 version we used did not support all the formatting features I required in a matrix report. If your reporting tool supports it, use a matrix report instead which will do the pivoting after the SQL has been executed.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pzozulkaAuthor Commented:
Koen: I think thats what I'm going to end up doing. We are using active reports, and this could be a nightmare. I'll post the solution if I figure one out.
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Let me know if you want more details on how we built it. From the top of my head it was dynamic SQL to create the pivot statements, The result of which was then inserted in a temp table with the required column headers. A bit of VB code in the report was then used to generate the column names in the report and link the data to the report layout. It works, but it's a pain to maintain in case something has to be modified.
pzozulkaAuthor Commented:
My Solution:

Summary: Output the data as rows instead of columns. So in other words, instead of having 12 columns representing 12 dynamic month columns, I returned 12 rows instead and let the client (ActiveReports) handle the presentation portion.

Created a stored procedure to return two tables instead of one.

The first table only contained the unique ID field, and other fields (all unique).
The second table only contained the ID field, and the month field. Inside the month field there were 12 dynamic month rows.

In the client (ActiveReports):
foreach ( INT ID in Table1 )
    foreach ( DataRow row in table2.Rows )
         WHILE ( table1.ID == table2.ID )
               stringbuilder.append (table2.monthField + "\t") // for tab delimitted output files
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.