March 21, 2008 at 2:06 pm
Ok, I'm entering a new arena ... I am a DBA, I know enough TSQL to get me by, I write reports regularly for my data ... but this, I'm not sure what to do.
Here is my query I'm working on ...
SELECT
[WO_NUM]AS [WorkOrderNo]
,[OPENDATE]AS [DateEntered]
,[Task]AS [Summary]
,[Request]AS [Requestor]
,[DUEDATE]AS [DateDue]
,[STATUS]AS [Status]
,[PRIORITY]AS [Priority]
,[TYPE]AS [Type]
,CASE
WHEN PARENTWOID = WOID THEN 1
ELSE 0
ENDAS [IsParent]
,CASE
WHEN PARENTWOID = WOID THEN 0
ELSE [PARENTWOID]
ENDAS [ParentWorkOrderNo]
,[CLSDDATE]AS [DateClosed]
,[ELAPSETIME]AS [TimeSpent]
FROM TASKS
ORDER BY 10
Basically, I want anything that has a ParentWorkOrderNo to be grouped appropriately with it's WO_NUM. More or less so that I can display all child orders underneath their parent. I don't know how I can do grouping in this situation so I'm assuming Pivot is the way to go? If so, anyone wanna give me a hand? Pivot is one of the few areas of TSQL that has me pretty baffled.
Any advice?
Thanks
March 21, 2008 at 2:19 pm
I am not sure what you mean by grouped? I do not see any aggregations, so I am guessing that you want the results ordered by parent/child. Is this accurate?
Do you have some sample data and the what the expected outcome should be?
March 21, 2008 at 2:26 pm
Right, which is why I knew I couldn't group ... but in essence, that is what I'm looking for ...
So for example ... this is currently what I'm displaying:
IsParentParentWorkOrderNo
10
10
10
10
10
0146385
0146385
0182639
0182639
0182639
0182639
0182639
Basically if it is a parent work order with no child, cool, display as is. Yet if it is a child work order, it should be displayed directly under the parent ...
Example of what I'm looking for ...:
OrderNum 1 - Parent
OrderNum 2 - Parent
OrderNum 3 - Child of 2
OrderNum 4 - Child of 2
OrderNum 5 - Parent
OrderNum 6 - Child of 5
Because of how the data can be inserted (people can create new "parent" orders at any time as well as childs) that I can't do an order by parent, child simply because the childs can and will be a later number and not fall directly under the parent in the output.
Make sense?
March 21, 2008 at 2:34 pm
Yep. It does but I still need more information. I take it that the WOID is what relates the work orders together. I need that piece of info to group the result set appropriately.
Can you post the WOID and Parent WOID with your sample data?
March 21, 2008 at 2:39 pm
Here is a small set of data ... honestly, I'm still not entirely sure I understand the full relation yet. This is a 3rd party vendor's application database and something still does not seem correct with the WOID relation to the PARENTWOID ... but still, here's a few entries:
WOIDWorkOrderNoStatusPriorityIsParentParentWorkOrderNo
182676182678PendingMedium0182675
182677182679CompletedMedium0182675
182678182680PendingMedium0182675
182679182681CompletedMedium0182675
182680182682PendingMedium0182675
182681182683CompletedMedium10
182682182684CompletedMedium10
182683182685PendingMedium10
182684182686CompletedMedium0182683
182685182687CompletedMedium10
182686182688CompletedMedium0182683
182687182689PendingMedium0182683
182688182690PendingMedium10
182689182691CompletedMedium10
182690182692CompletedMedium10
182691182693PendingMedium10
182692182694PendingMedium0182691
182693182695PendingMedium0182691
182694182696PendingProject10
182695182697CompletedMedium10
182697182699CompletedMedium10
182699182701CompletedMedium10
182700182702CompletedMedium10
182701182703PendingProject10
182702182704CompletedMedium10
182703182705CompletedMedium10
182704182706CompletedLow10
182705182707CompletedMedium10
182707182709PendingMedium10
182708182710PendingMedium10
182709182711CompletedMedium10
182710182712CompletedMedium10
182712182714PendingMedium0182708
182713182715PendingMedium0182708
182714182716CompletedMedium0182708
182715182717PendingMedium0182708
182716182718PendingMedium10
182717182719CompletedMedium10
182718182720PendingMedium10
182719182721CompletedMedium10
182720182722PendingMedium10
182721182723CompletedMedium10
182722182724PendingMedium0182720
182723182725CompletedMedium10
182724182726CompletedMedium0182720
182725182727PendingMedium0182720
182726182728PendingMedium0182720
182727182729PendingMedium0182720
182728182730CompletedMedium10
182729182731PendingMedium10
182730182732PendingMedium10
182731182733CompletedMedium10
182732182734PendingMedium10
182733182735CompletedMedium10
182734182736PendingMedium10
182735182737CompletedMedium10
182736182738PendingMedium10
182737182739CompletedMedium10
182738182740CompletedMedium10
182739182741PendingMedium10
182740182742PendingMedium10
182741182743CompletedMedium10
182742182744PendingMedium10
182743182745CompletedMedium10
182744182746PendingMedium10
182745182747PendingMedium0182744
182746182748CompletedMedium0182744
182747182749PendingMedium0182744
182748182750PendingMedium0182744
182749182751PendingMedium10
182750182752PendingMedium10
182751182753PendingMedium0182750
182752182754PendingMedium0182750
182753182755PendingMedium0182750
182754182756PendingMedium0182750
182755182757PendingMedium10
182756182758PendingMedium10
182757182759PendingProject10
182758182760CompletedMedium10
Thanks!
March 21, 2008 at 4:01 pm
This is a sample I came up with. It may not be 100%, but it should be enough to get you going. The logic is to get all the parent in the outer query and join them to a list of children then you simple order by parent work order id.
This is what it would look like.
select a.woid,b.woid, b.workorderno, b.status, b.priority
from @t a
inner join(
select a.*
from @t a
where isparent = 0
) as b
on a.woid = b.parentworkorderno
where a.isparent = 1
order by a.woid
March 21, 2008 at 6:19 pm
Since we are in the SS2K5 forum, I am going to assume you are using it and not SS2K.
IMO, the easiest way to build a recursive query is to use a CTE, so here is a CTE solution:
WITH WorkOrders (WONum, PWONum, Status, Priority, IsParent) AS
(
SELECT WorkOrderNo, WorkOrderNo AS ParentWorkOrderNo, Status, Priority, IsParent
FROM dbo.YourTable
WHERE IsParent = 1
UNION ALL
SELECT YT.WorkOrderNo, YT.ParentWorkOrderNo, YT.Status, YT.Priority, YT.IsParent
FROM dbo.YourTable YT
INNER JOIN WorkOrders WOG
ON YT.ParentWorkOrderNo = WOG.WONum
)
SELECT WONum, CASE PWONum WHEN WONum THEN 0 ELSE PWONum END AS Parent
, Status, Priority, IsParent
FROM WorkOrders
ORDER BY PWONum, WONum;
In the CTE, the first SELECT sets up your "Parent" records. With your example data, there seems to be two ways to determine this, by [IsParent] = 1 or by [ParentWorkOrderNo] = 0. You'll have to work with your data to determine which is more reliable. The [IsParent] column is certainly the obvious choice, but when working with 3rd party apps, things aren't always as they seem. I have been bitten by that in a situation very similar to what you are describing here. Also, you'll notice that I select [WorkOrderNo] twice, this is to allow us to get the order that you want.
The second select sets up your children.
In the final select, I added the CASE expression to change your ParentWorkOrderNo back to 0, as it is in your sample data with the side effect of easier readability. (To prove that the ordering was correct) I am sure you'll have to do some adjusting to meet your needs.
This code assumes that [IsParent] is accurate and that [ParentWorkOrderNo] is always 0 when [IsParent] is 1. If either is not true, you may need to make adjustments. Also note that as this is a join, if there are orphans they will not be in the result set. For example, your sample data has 9 orphans.
Good Luck!
March 25, 2008 at 6:09 am
Thanks John ... the explanation was great, but if you notice in my query, I'm casing 'IsParent' and 'ParentWorkOrderNo'. How would I go about adding this into the WITH?
Thanks
March 25, 2008 at 8:35 am
I was thinking there might be a disconnect between your first query and the dataset you posted...hmmm.
So if I am seeing this correctly, the sample data is not your source table layout, but rather the result of your current query?
Can you post a bit of the source table and layout? Then I can see how to adjust the CTE.
John
March 27, 2008 at 3:24 am
Why don't you return always return the parent id even if it is a parent record.
then you can order by parent id, is parent (desc) , workid.
you would get this kind of result:
parentid is parent workid
5 1 5
5 0 4
5 0 6
etc... (made up my data)
you can even multiply the abs(parentid-1) to the is parent for display purposes.
Ken
April 2, 2008 at 11:39 am
Adam,
Based on the sample data and other queries that you sent my way, this would be my solution.
I started by creating a view for the table, TASKS. The view, noted below, was needed to set an alternate value to PARENTWOID. This was done to avoid the infinite recursion caused by WOID = PARENTWOID. I chose -1 as it is highly unlikely for it to be used as a key value, but you can adjust that as needed for your environment. Just be sure to pick a value that will never be a WOID.
CREATE VIEW vw_Tasks
AS
SELECT [WO_NUM]
,[TASK]
,[TYPE]
,[REQUEST]
,[REQDATE]
,[OPENBY]
,[OPENDATE]
,[MODIBY]
,[MODIDATE]
,[CLSDBY]
,[CLSDDATE]
,[ELAPSETIME]
,[ELAPSEMIN]
,[PRIORITY]
,[DUEDATE]
,[RESPONS]
,[ASSNDATE]
,[COMPLETED]
,[HOURS]
,[RATE]
,[CHARGE]
,[WS_NUM]
,[DEPT_NUM]
,[DEPT]
,[PHONE]
,[PHONE_EXT]
,[LOCATION]
,[DESCRIPT]
,[WO_TEXT1]
,[WO_TEXT2]
,[WO_TEXT3]
,[WO_TEXT4]
,[WO_TEXT5]
,[WO_TEXT6]
,[WO_DATE1]
,[WO_DATE2]
,[WO_NUM1]
,[WO_INT1]
,
,[FT]
,[COMPFLAG]
,[STATUS]
,[AGENTDATE]
,[AGENTLEVEL]
,[AWS_NUM]
,[LOOKUP1]
,[LOOKUP2]
,[GUIDO]
,[EMAILADDR]
,[USERID]
--,[PARENTWOID]
,CASE
WHEN [PARENTWOID] = [WOID] THEN -1
ELSE [PARENTWOID]
END
AS Alt_PARENTWOID
,[WOID]
,[WOTYPE2]
,[WOTYPE3]
,[ATTACHCOUNT]
,[AGENTLEVELREQ]
,[AGENTDATEREQ]
FROM [dbo].[Tasks]
Then I pointed the cte query at the view:
WITH GroupedTasks AS
(
SELECT WO_NUM, OPENDATE, TASK, REQUEST, DUEDATE, STATUS, TYPE, CLSDDATE
, WO_NUM AS ParentWO_NUM, WOID, alt_PARENTWOID
FROM vw_Tasks
WHERE alt_PARENTWOID = -1
UNION ALL
SELECT T.WO_NUM, T.OPENDATE, T.TASK, T.REQUEST, T.DUEDATE, T.STATUS, T.TYPE, T.CLSDDATE
, GT.WO_NUM AS ParentWO_NUM, T.WOID, T.alt_PARENTWOID
FROM vw_Tasks T
INNER JOIN GroupedTasks GT
ON T.alt_PARENTWOID = GT.WOID
)
SELECT *
FROM GroupedTasks
ORDER BY ParentWO_NUM, WO_NUM
;
In the first select, I substituted WO_NUM for ParentWO_NUM. This was to help with the ordering you were looking for in earlier posts.
I left out the "IsParent" column you had derived earlier as well. With this solution I didn't think that it was needed. As well, with the way that you were using it, "IsParent" wasn't an accurate name, as it didn't truely indicate if a record was a parent, but rather if it had no parent.
If any of this doesn't make sense, let me know.
GL!
April 2, 2008 at 11:57 am
Awesome! I'm playing with it now, but oh my is this taking awhile ... probably gonna need an index or two to support this beast.
I'll let ya know, thanks again!
edit: Ouch, it just got deadlocked ...
April 2, 2008 at 12:36 pm
So far so good ... this definitely looks to be what I needed. Now I just have to determine what I want to do with the data ...
Thanks again for your help!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply