Need Help With SQL Script for "Pivot Table"

I'm using SQL Server 2008.

We have a table called WOSTATUS (Work Order Status). Each row in the table shows the date and time that a Work Order "moved" from one status to the next. For example, a work order might be awaiting approval (WAPPR) and then a few days later get approved (APPR). Each row also contains the identity of the work order that changed (Work Order #) and the status code that it changed to.

Using the WOSTATUS table as the base, I want to run a script that will show me the same work orders that are in the WOSTATUS table, the date and time that they got a WAPPR status, the date and time that the received an APPR status, and the elapsed time between the two.

I want to run another script that will show me the same work orders that are in the WOSTATUS table, the date and time that they got a WAPPR status, the date and time that the received an COMP status, and the elapsed time between the two.

I've attached a word document that shows and example.

Can anyone help with the SQL that I'd need to develop?

Thanks.
cbridgmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

prequel_serverCommented:
I can't find your word doc attachment?
0
cbridgmanAuthor Commented:
Oops, forgot to upload it. Here it is.

thanks
Work-Order-Status-Table.docx
0
prequel_serverCommented:
This should get you the first table you wanted. You can modify the status in the Case statements to get the second table. I calculated the elapsed time in minutes but you can convert this easily.

SELECT
    Workorder,

    MAX(CASE WHEN Status = 'WAPPR' THEN StatusDate ELSE NULL END) AS 'WAPPRDATE',
    MAX(CASE WHEN Status = 'APPR' THEN StatusDate ELSE NULL END) AS 'APPRDATE',

	DATEDIFF(minute, MAX(CASE WHEN Status = 'WAPPR' THEN StatusDate ELSE NULL END), 
  MAX(CASE WHEN Status = 'APPR' THEN StatusDate ELSE NULL END)) AS ElapsedTime


FROM
    WOSTATUS
GROUP BY
    Workorder

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

cbridgmanAuthor Commented:
Thanks very much for your help on this. Your code works perfectly. I have an add on question. Would you like me to submit the following question as a new one or can you answer this one here as well?

The question -->

Some workorders never have a status of WAPPR and others never have a status of APPR. The SQL that you supplied does not exclude those. Instead the result set shows workorders with null values in the respective date fields. I would like the query to exclude any work orders that do not have both WAPPR and APPR rows.

Is that also possible?
0
prequel_serverCommented:
You're welcome. Yes that's possible. I would use nesting like so:

SELECT WO.Workorder, WO.WAPPRDATE,WO.APPRDATE, DATEDIFF(minute,WO.WAPPRDATE,WO.APPRDATE) AS ElapsedTime

FROM

(
  SELECT
    Workorder,

    MAX(CASE WHEN Status = 'WAPPR' THEN StatusDate ELSE NULL END) AS 'WAPPRDATE',
    MAX(CASE WHEN Status = 'APPR' THEN StatusDate ELSE NULL END) AS 'APPRDATE'

FROM
    WOSTATUS

GROUP BY
    Workorder
	) as WO

	WHERE isNULL(DATEDIFF(minute,WO.WAPPRDATE,WO.APPRDATE),0)<>0

Open in new window


-The inner table WO contains all unique work orders regardless if they are missing one of the dates.

-The outer select calculates the elapsed time from the two dates and I added a WHERE clause to filter out any work orders that are missing a WAPPR or APPR

-I used the elapsed time calculation as filter since if either one of the dates are missing then the value will be NULL.

hope that helps.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cbridgmanAuthor Commented:
Perfect answer. It's just what I needed. I never would have been able to get that on my own.
0
prequel_serverCommented:
great, glad it helped. FYI Sql server has a PIVOT operator that can produce the same results with probably less script and more performance.  I would look at using it if the number of rows that need to be converted to columns is large resulting in many case statements.  

Here's the help article for it:
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.