x

Creating a Pivot in TQL - Totals aren't quite working

I have a large table with transaction data. It can be multiple transactions per month. I have 2 years of data and growing. It all needs to be reported out on. I'm trying to run this monthly, to create a table and allow users to drill through it using a web interface.

I'd really like to roll all of this into 1 table for both years but here are the problems I am running in to.

  • If I don't filter by year, it gives me monthly totals for both years.
  • The Caps_vs_Mailhouse column has 4 possible values. Its a transaction type identify. Some 1 mls_id(the unique customer identifier) can have multiple transactions across multipls caps_vs_mailhouse values in a month. The query below provides a line item for each caps_vs_mailhouse occurence in a a month, but the monthly totals aren't broken out, it shows the totals for all of the mls_id's transactions in the month and in that year for each one of those caps_vs_mailhouse values. It's not broken out.

Maybe I'm biting off more than I can chew, trying to do too much in one query. Any hell with be greatly appreciated!

Thanks!

SELECT *
Into dbo.mls_2011and2012 
FROM (
  SELECT
 Distinct(F.mls_id),
    YEAR(Process_Date) [Year],
     CASE MONTH(Process_Date) 
      WHEN 1 THEN 'January'
      WHEN 2 THEN 'February'
      WHEN 3 THEN 'March'
      WHEN 4 THEN 'April'
      WHEN 5 THEN 'May'
      WHEN 6 THEN 'June'
      WHEN 7 THEN 'July'
      WHEN 8 THEN 'August'
      WHEN 9 THEN 'September'
      WHEN 10 THEN 'October'
      WHEN 11 THEN 'November'
      WHEN 12 THEN 'December'
    END as [Month],
    Total_Amount,
 Company_Name,
 PBP_Account_Number,
 TSYS_Individual_ID,
 Date_Of_Activation,
 Caps_vs_Mailhouse,
 permit_specialist
  FROM dbo.mls_report_full F
 Left Join dbo.mls_report_master M 
 On F.mls_id = M.mls_id
 Where Year(process_date)>= '2011'
 Group by F.mls_id,Caps_vs_Mailhouse,process_Date,Total_Amount,Company_Name,PBP_Account_Number,TSYS_Individual_ID,Date_Of_Activation,permit_specialist
) TableDate
PIVOT (
  SUM(Total_Amount)
  FOR [Month] IN (
    [January],[February],[March],[April],
    [May],[June],[July],[August],
    [September],[October],[November],[December]
  )
) PivotTable
more ▼

asked Aug 21 '12 at 06:59 PM in Default

Groty gravatar image

Groty
0 1 1 2

I'm building this as a self serve application. The UI is going to be on the web, Coldfusion actually. I want to point the users to it and never touch it again. Let it update as a scheduled task.
Aug 22 '12 at 02:39 PM Groty
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I've never been a fan of PIVOT in TSQL. Generally I see it used where it should be the presentation layer that does the pivot as part of the formatting. What is your intended UI? If it's SSRS then return all the data efficiently via a procedure and then point a report at the procedure and use a Tablix to pivot the data you want it to.

You could add a year parameter for users to select a specific year or all data but the database does the data access and the SSRS report does the pretty.

To work on with the PIVOT however, you could make the Month column have the year value in it, so:

2011_01, 2011_02 ...

You'll need to have the format right for it to be sorted chronologically.
more ▼

answered Aug 22 '12 at 12:33 PM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x76

asked: Aug 21 '12 at 06:59 PM

Seen: 548 times

Last Updated: Aug 22 '12 at 02:40 PM