Solved

SQL query design ideas

Posted on 2014-11-05
7
162 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
  • 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 12

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 12

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now