1

I have the following data plus some other additional tables that I am working on. 

id ClinicId   MonthsInOperation Budget Revenue  
1  1232       12                  2000
2  1534       8                   3500
3  1767       10                  2500
4  1232       13                  3000
5  1534       2                   4000

What I ultimately need to generate is(Table Structure):

ClinicInfo(Static Columns) + Selected Month------------->> End of Year(Dynamic Columns)

Dynamic Columns will have the budget information for specific month (For example Table structure + Data):

ClinicInfo(Static Columns) + Feb 2013-------------------->> December 2013
--------------------------   2000+-------+-------------------+ 4500

I haven't used PIVOT before, not sure if that is what I need here.

andand
  • 17,134
  • 11
  • 53
  • 79
user3681350
  • 63
  • 1
  • 7
  • What's the relationship between MonthsInOperation, and a normal calendar? I see 13 in there, so that obviously represents year 2, month 1, but what is that in a real date? Is it querable, or will we have to rely on hardcode? – Jaaz Cole Jul 15 '14 at 18:13
  • MonthsInOperation is basically the number of months the clinic has operated since the date it was open. so If clinic opened on Jan 15,2014: the MIO will be 6, if we compare it to today. – user3681350 Jul 15 '14 at 18:36
  • As implied by the column name, but I'm getting the implication from your post that you want that translated to a real (i.e. Dec 2013) date, so the question is, how? – Jaaz Cole Jul 15 '14 at 19:36
  • @JaazCole I know the opening date of the clinic, so I can figure out the date in the form of Dec 2013. – user3681350 Jul 15 '14 at 20:36

1 Answers1

2

Producing a report like this, directly from SQL Server is difficult at best. The problem with PIVOT is that it only works with a static column set. If you want your output columns to be dynamic, PIVOT (in SQL) will not help you there.

I am also assuming that you have some other table that has the budget info, broken down per month, for each clinic.

Honestly, doing this in a reporting solution of some type (Active Reports, SSRS, Crystal) using a header/detail type layout is probably the easiest way to skin it. Or building it in some non-SQL middle tier (Java, .NET, etc.) because then you could piece it together from the different pieces of data (basic clinic info, months in range, data for those months). You can do it in raw TSQL, but repeatable performance is sketchy at best in my experience.

Probably not the answer you were looking for, but hopefully gave you some ideas.

Earl G Elliott III
  • 425
  • 2
  • 3
  • One could even connect Excel to the SQL Server data source, then run a Pivot Table off the dataset. – Matt Jul 15 '14 at 20:21
  • Otherwise, if SQL is the only way, build the PIVOT with all 12 months, regardless of whether they are needed or not. – Matt Jul 15 '14 at 20:23
  • You could also build your SQL dynamically, but performance will suffer. – Earl G Elliott III Jul 15 '14 at 20:27
  • @EarlGElliottIII I am using LogiXML as the reporting too,but It doesn't have any such capability. Any idea how should i proceed if want to go ahead with Dynamic SQL? – user3681350 Jul 15 '14 at 20:43
  • Ah, ok. You will need to do it in SQL direct then. You can do dynamic sql in sql, its just a little funky. Here is an example from elsewhere in stackoverflow http://stackoverflow.com/questions/12430512/dynamic-pivot-table-in-sql-server – Earl G Elliott III Jul 15 '14 at 20:52