?
Solved

Need Help With SQL Script for "Pivot Table"

Posted on 2014-08-24
7
Medium Priority
?
246 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.
0
Comment
Question by:cbridgman
  • 4
  • 3
7 Comments
 
LVL 3

Expert Comment

by:prequel_server
ID: 40281713
I can't find your word doc attachment?
0
 

Author Comment

by:cbridgman
ID: 40281718
Oops, forgot to upload it. Here it is.

thanks
Work-Order-Status-Table.docx
0
 
LVL 3

Expert Comment

by:prequel_server
ID: 40281794
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:cbridgman
ID: 40282172
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
 
LVL 3

Accepted Solution

by:
prequel_server earned 2000 total points
ID: 40282292
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
 

Author Closing Comment

by:cbridgman
ID: 40282333
Perfect answer. It's just what I needed. I never would have been able to get that on my own.
0
 
LVL 3

Expert Comment

by:prequel_server
ID: 40282348
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

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

840 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