Solved

Pivot Table Help

Posted on 2009-05-19
22
1,021 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
  • 10
  • 10
  • 2
22 Comments
 
LVL 59

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 59

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

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 59

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 59

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 59

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 59

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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 59

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 26

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 59

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 59

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 59

Expert Comment

by:Kevin Cross
ID: 24441988
Good deal!

Best regards,
Kevin
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now