Grouping problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Grouping problems

Author

Topic

Josh93945
Starting Member

2 Posts

Posted - 2007-11-26 : 13:19:38

I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.

I'm trying to get information like this in to a report:

WO#
-WO Line #
--(Details:)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts:)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Detail #etc
WO#
-WO Line #
--(Details:)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts:)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Parts #etc

I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?

There are 4 tables:

Work Order Header
Work Order Line
Work Order Line Details
Work Order Line Requisitions

The Header has a unique PK.
The Line uses the Header and a Line # as foreign keys that together are unique.
The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:


WO      WOL     WOLR    WOLD
226952  10000   10000   10000
226952  10000   10000   20000
226952  10000   10000   30000
226952  10000   10000   40000
226952  10000   20000   10000
226952  10000   20000   20000
226952  10000   20000   30000
226952  10000   20000   40000
399999  10000   NULL    10000
375654  10000   10000   NULL
etc

Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR

It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:

SELECT     [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No, 
           [Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No
FROM       [Work Order Header] LEFT OUTER JOIN
             [Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN
             [Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND 
             [Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN
             [Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND 
             [Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]

tm
Posting Yak Master

160 Posts

Posted - 2007-11-26 : 15:48:12

You can use "Union ALL" to get your data the way you want ..

[I have not tested the script below]

SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No,
[Work Order Line Detail].[Line No_] AS DetailLine, 1 AS LineType
FROM [Work Order Header] LEFT OUTER JOIN
[Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN
[Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_]
UNION ALL
SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No,
[Work Order Requisition].[Line No_] AS DetailLine, 2 AS LineType
FROM [Work Order Header] LEFT OUTER JOIN
[Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN
[Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]

--=====================
The above script will structure your data something like this ..

WO WOL DetailLine LineType
226952 10000 10000 1
226952 10000 20000 1
226952 10000 30000 1
226952 10000 40000 1
226952 10000 10000 2
226952 10000 10000 2
226952 10000 10000 2
226952 10000 10000 2
226952 10000 10000 1
226952 10000 20000 1
226952 10000 30000 1
226952 10000 40000 1
226952 10000 20000 2
226952 10000 20000 2
226952 10000 20000 2
226952 10000 20000 2
399999 10000 10000 1
399999 10000 NULL 2
375654 10000 NULL 1
375654 10000 10000 2

In your report you use a table item and group by WO, WOL and order by LineType, DetailLine.

If you need to display "(Details:)" or "(Parts:)" as labels in your table field you should be able to use LineType field to display based on WO field.

Josh93945
Starting Member

2 Posts

Posted - 2007-11-26 : 16:40:04

Thank you! I'll try this right away.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources