• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1037
  • Last Modified:

Pivot Table Help

Hey Experts!

I need some assistance with creating a pivot table.

I have a recordset that is showing time postings against a job (or work order#) from our ERP system

The basic column layout for what I am looking for is as follows

Job, WorkCentre, PostYear,PostMonth, Hours.

What I would like to do is create a pivot table so that I can have one record per Job/WorkCentre with a column for each year/month combination.


0
Auric1983
Asked:
Auric1983
  • 10
  • 10
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
You can write a query that generates your data and save as a view then use Dynamic SQL statement to PIVOT.  Mark talks about it here some:

http:/articles/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Dynamic-Pivot-Procedure-for-SQL-Server.html

In your original query for view, you can do something like this to get data the way you need.

SELECT Job, WorkCentre, Hours
, CAST(PostYear AS NVARCHAR(4) + '-' + RIGHT('00'+CAST(PostMonth AS NVARCHAR(2)), 2)
FROM Your_WO_Table
0
 
Kevin CrossChief Technology OfficerCommented:
If you have just one date column, you can use CONVERT or other date function like this:
http://msdn.microsoft.com/en-us/library/ms187928.aspx

CONVERT(VARCHAR(7), your_date_column, 120)

If you have multiple entries for the same date, you would do something like this:

CREATE VIEW dbo.vw_WorkOrderByPeriod AS
SELECT Job, WorkCentre
, CONVERT(VARCHAR(7), your_date_column, 120)
, SUM(ReportedTime) AS Hours
FROM Your_WO_Table
GROUP BY Job, WorkCentre
, CONVERT(VARCHAR(7), your_date_column, 120)

Then follow Mark's example on PIVOTing the view.

Hope that helps.

Best regards,
Kevin
0
 
Auric1983Author Commented:
I do have multiple entries per date, getting the data into a tabular format is easy.  

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Chris LuttrellSenior Database ArchitectCommented:
Are you wanting the query to pivot the data or are you wanting a matrix report in Reporting Services?
0
 
Kevin CrossChief Technology OfficerCommented:
Ok, then since that step is easy, procedure to the article which demonstrates how you PIVOT.  Since the data can change based on year/month combinations, you have to use dynamic sql to execute the PIVOT statement which will require hardcoded column names/values.
0
 
Auric1983Author Commented:
CGuttrell,

Ultimately my goal is to be able to present the client w/ the Work Centre info, the total time posted since the job was raised, and current + 2 previous periods postings.

I'm not sure which method is the best for this? using a PivotTable or a Matrix report.  

0
 
Kevin CrossChief Technology OfficerCommented:
You can do this easily within the SQL statement itself if you don't care what the headings of the periods are.  In other words, if you need it to say 2009-05, 2009-04, and 2009-03 exactly then not as easy; however, if can be CurrentMonth, LastMonth, TwoMonthsAgo for example, then I have a method I use.  Please post the SQL that you use to get the data in tabular format.
0
 
Auric1983Author Commented:

I guess on another note: I should mention I know i can do this using straight TSQL i'm just trying to learn a new way of doing it :P

0
 
Kevin CrossChief Technology OfficerCommented:
So you already know how to do this using PIVOT?  Then you must want CGuttrell's help with Matrix report then.
0
 
Auric1983Author Commented:
No i've never used a PIVOT before, most of my SQL experience comes from SQL 2000 trying to learn how some new features ( like pivot) work as I go.
0
 
Kevin CrossChief Technology OfficerCommented:
Ok, then please post your query as I requested.  I will demonstrate for you using PIVOT in normal SQL without having to use dynamic SQL statement as shown in Mark's article.  However, he does show the basics of using PIVOT for you to learn from as well, so still good reading when you get a chance.
0
 
Auric1983Author Commented:
select WorkCentre,PostYear,PostMonth,sum(RunTimeHours)as TotalTime from JobPost where Job='00123456' and TrnType='Labor' group by PostYear,PostMonth,WorkCentre
0
 
Kevin CrossChief Technology OfficerCommented:
If you want current month, plus pass two months then this would work.

select WorkCentre
, isnull([2], 0) as TotalTime_TwoMonthsAgo
, isnull([1], 0) as TotalTime_OneMonthAgo
, isnull([0], 0) as TotalTime_CurrentMonthToDate
from (
	select WorkCentre
	, (Year(GetDate())-PostYear) * 12 + (Month(GetDate())-PostMonth) AS MonthsAgo
	,sum(RunTimeHours)as TotalTime 
	from JobPost 
	where Job='00123456' and TrnType='Labor'
	group by PostYear, PostMonth, WorkCentre
) t
pivot (sum(TotalTime) for MonthsAgo in ([2],[1],[0])) pvt
 
-- here it is with all jobs
select Job, WorkCentre
, isnull([2], 0) as TotalTime_TwoMonthsAgo
, isnull([1], 0) as TotalTime_OneMonthAgo
, isnull([0], 0) as TotalTime_CurrentMonthToDate
from (
	select Job, WorkCentre
	, (Year(GetDate())-PostYear) * 12 + (Month(GetDate())-PostMonth) AS MonthsAgo
	,sum(RunTimeHours)as TotalTime 
	from JobPost 
	where TrnType='Labor'
	group by PostYear, PostMonth, Job, WorkCentre
) t
pivot (sum(TotalTime) for MonthsAgo in ([2],[1],[0])) pvt

Open in new window

0
 
Auric1983Author Commented:
Thanks for your help Kevin! This will help me out a lot.
0
 
Chris LuttrellSenior Database ArchitectCommented:
That does not give him the "the total time posted since the job was raised" that he said he needed.  Neat way to get the last 3 months with a pivot though.
0
 
Kevin CrossChief Technology OfficerCommented:
What I am doing:
+taking tabular data and take out any dynamic (changing values from the column we will pivot) -- in other words, current year/month values change each month, but if reflected as months from today it is always consistent for report as 0 = current, 1 = last month, 2 = 2 months ago.

Month(GetDate())-PostMonth ==> handles simple 5-4=1, so April is 1 month ago
Year(GetDate())-PostYear) * 12 ==> handles offset for change in years as going backwards, so 5-11=-6 but now take 2009-2008=1*12=12 + (-6) and you get November 2008 as 6 months ago.

+now save that as a view or use derived table as shown.
+use pivot function to sum TotalTime for a given value of MonthsAgo which we can now easily hardcode into pivot statement without having to change query next month as in June - 2, 1, 0 are still valid values for months ago.

Hopefully that makes sense.

Regards,
Kevin
0
 
Auric1983Author Commented:
That's OK CG, I can work with this to get to the end result.

There is a method to my madness.  Basically, I wanted to take a simple requirement and learn to use a pivot table, I have a few additional fields to add as well.  

I probably could have figured out how to do this on my own but I'm a little behind the eight ball on this deliverable (EOB today) and my todo list is still a mile long.
0
 
Auric1983Author Commented:
Kevin, your method of handling the changeover for years is marvelous!


0
 
Auric1983Author Commented:
Hey  Guys, well upon looking back at this solution it's going to take a bit of retooling.  Kevin your idea for the time periods is great, however I can't use calendar dates, PostYear+PostMonth are Fiscal Periods.

It's OK i have a UDF function to convert a calendar date to fiscal year/period so i should be able to apply the same logic just not use "getdate"
0
 
Kevin CrossChief Technology OfficerCommented:
Are these based on Quarters?  
This was not published yet when I was helping you, so I was afraid to reference it.  This is an article I wrote on DateSerial in MS SQL.  You can use it to convert a year, month back to a calendar date that you may be able to analyze differently like quarter ago etc.

http:/articles/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/DateSerial-Function-for-Microsoft-SQL-Server.html

But sounds like you may have another method.

Good luck.
0
 
Auric1983Author Commented:
Kevin,

I was able to use my "DatetoFiscal" function, which just takes the calendar date and returns another date that fits in our fiscal calendar so the math works out.

Works just great
0
 
Kevin CrossChief Technology OfficerCommented:
Good deal!

Best regards,
Kevin
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 10
  • 10
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now