Solved

SQL query design ideas

Posted on 2014-11-05
7
167 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 66

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
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

724 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