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!


 Into dbo.mls_2011and2012 
     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],
   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
   FOR [Month] IN (
 ) PivotTable

more ▼

asked Aug 21, 2012 at 06:59 PM in Default

avatar image

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, 2012 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, 2012 at 12:33 PM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Aug 21, 2012 at 06:59 PM

Seen: 872 times

Last Updated: Aug 22, 2012 at 02:40 PM

Copyright 2018 Redgate Software. Privacy Policy