Solved

Pivot Table Help

Posted on 2009-05-19
22
1,031 Views
Last Modified: 2012-05-07
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
Comment
Question by:Auric1983
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 10
  • 2
22 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24424239
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24424307
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
 
LVL 16

Author Comment

by:Auric1983
ID: 24424325
I do have multiple entries per date, getting the data into a tabular format is easy.  

0
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24424366
Are you wanting the query to pivot the data or are you wanting a matrix report in Reporting Services?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24424406
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
 
LVL 16

Author Comment

by:Auric1983
ID: 24424426
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24424493
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
 
LVL 16

Author Comment

by:Auric1983
ID: 24424518

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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24424538
So you already know how to do this using PIVOT?  Then you must want CGuttrell's help with Matrix report then.
0
 
LVL 16

Author Comment

by:Auric1983
ID: 24424558
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24424582
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
 
LVL 16

Author Comment

by:Auric1983
ID: 24424600
select WorkCentre,PostYear,PostMonth,sum(RunTimeHours)as TotalTime from JobPost where Job='00123456' and TrnType='Labor' group by PostYear,PostMonth,WorkCentre
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 24424757
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
 
LVL 16

Author Closing Comment

by:Auric1983
ID: 31583109
Thanks for your help Kevin! This will help me out a lot.
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24424797
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24424830
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
 
LVL 16

Author Comment

by:Auric1983
ID: 24424836
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
 
LVL 16

Author Comment

by:Auric1983
ID: 24424864
Kevin, your method of handling the changeover for years is marvelous!


0
 
LVL 16

Author Comment

by:Auric1983
ID: 24441333
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24441913
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
 
LVL 16

Author Comment

by:Auric1983
ID: 24441934
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24441988
Good deal!

Best regards,
Kevin
0

Featured Post

Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

689 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