Solved

SQL query design ideas

Posted on 2014-11-05
7
166 Views
Last Modified: 2014-11-21
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?
0
Comment
Question by:pzozulka
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40424673
>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.
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40424753
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.
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40424801
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'

;WITH                    
cteTally10 AS (                    
SELECT 0 AS tally UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL                    
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9                    
),                    
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

0
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
LVL 13

Accepted Solution

by:
Koen Van Wielink earned 500 total points
ID: 40425401
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.
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40426191
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.
0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40426211
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.
0
 
LVL 8

Author Closing Comment

by:pzozulka
ID: 40458510
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.

Details:
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
    }
}
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

733 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