We help IT Professionals succeed at work.

Need Help With SQL Script for "Pivot Table"

266 Views
Last Modified: 2014-08-24
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.
Comment
Watch Question

I can't find your word doc attachment?

Author

Commented:
Oops, forgot to upload it. Here it is.

thanks
Work-Order-Status-Table.docx
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

Author

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?
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Perfect answer. It's just what I needed. I never would have been able to get that on my own.
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.