sql server reporting services:
I have two tables (definitions follow) one called Product, the other called
Traveler_Step. The PK-FK link is the Traveler_Step_#. Each product can
only be at one step at a time. If the product is done processing the
Traveler_Step_# is 0. Not all steps have a traveler at them. What I need
is a query that will return all the steps from the Traveler_Step table that
have a value of -1 in the WIPStep field. The query needs to order the steps
by WIP_Status_Order. Also, there are many different Traveler_Step_# numbers
for different travelers that have the same WIPStep for them. Running the
following query against the two tables:
SELECT
Product.Ingot_Number,
Product.Traveler_Number,
MIN(Traveler_Step.Step) AS Step,
Traveler_Step.WIPStep,
Traveler_Step.WIP_Status_Order,
Traveler_Step.Step_Description,
COUNT(Product.Product_#) AS PieceCount
FROM
Product
RIGHT OUTER JOIN
Traveler_Step ON Product.Traveler_Step_# = Traveler_Step.Traveler_Step_#
GROUP BY
Product.Ingot_Number,
Product.Traveler_Number,
Traveler_Step.WIPStep,
Traveler_Step.WIP_Status_Order,
Traveler_Step.Step_Description
HAVING
Traveler_Step.WIPStep < 0
ORDER BY
Product.Traveler_Number,
Traveler_Step.WIP_Status_Order
I get the following results:
NULL NULL 10 -1 1 Weigh/Record 0
NULL NULL 23 -1 3 Hard Pickle 0
NULL NULL 55 -1 4 Hot Roll Intermediate 0
NULL NULL 15 -1 5 UT Test 0
NULL NULL 20 -1 6 Beta Quench 0
NULL NULL 25 -1 7 Grit Blast 0
NULL NULL 30 -1 8 Pickle 0
NULL NULL 40 -1 9 Air Anneal 0
NULL NULL 45 -1 10 Grit Blast 0
NULL NULL 50 -1 11 Pickle 0
NULL NULL 60 -1 12 Cold Roll Intermediate 0
NULL NULL 80 -1 13 Vacuum Anneal Hang 0
NULL NULL 90 -1 14 Cold Roll Final 0
NULL NULL 115 -1 15 Vacuum Anneal F/P 0
NULL NULL 130 -1 16 Plane Width 0
NULL NULL 140 -1 17 Machine Shape 0
NULL NULL 150 -1 18 Machine Grooves 0
NULL NULL 165 -1 19 Shear Final Length 0
U06436L 53086A 15 -1 5 UT Test 5
U06436L 53086B 15 -1 5 UT Test 4
U06450L 53223J 140 -1 17 Machine Shape 2
U06450L 53223L 140 -1 17 Machine Shape 16
U06460L 53236A 140 -1 17 Machine Shape 5
U06460L 53237K 40 -1 9 Air Anneal 4
U06460L 53237M 40 -1 9 Air Anneal 4
U06460L 53237N 40 -1 9 Air Anneal 3
U06494L 53248G 20 -1 2 Grit Blast 1 *
U06494L 53307A 20 -1 6 Beta Quench 4
U06494L 53307B 20 -1 6 Beta Quench 3
I noticed that step 2 does not appear in the first fields with null values.
But one operation is at step 2 (marked with a star (*)). I need to get all
the steps to appear on the left of the matrix, then list all the items in
the corresponding row of the matrix depending on the
Traveler_Step.WIP_Status_Order field. I can get the steps to appear but as
you can see in the query results the right join is not returning all the
traveler_steps with null values. So I would like to see
Traveler 12345 Traveler 67899 Traveler
23343 Traveler 223344
Step 1
Step 2
x
Step 3
Step 4
Step 5 x
x
Step 6 x
Step 7
.....
Step 19
Where the traveler numbers are the columns and the X corresponds to the
step in the rows. Any help?
john
CREATE TABLE [dbo].[Product](
[Product_#] [int] IDENTITY(1,1) NOT NULL,
[Traveler_#] [int] NOT NULL,
[Type_#] [int] NOT NULL,
[Part_Number] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Date_Shipped] [datetime] NULL,
[Operator_#] [int] NOT NULL,
[Create_Date] [datetime] NOT NULL,
[Shop_Order_#] [int] NOT NULL,
[Date_Closed] [datetime] NULL,
[Ingot_Number] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status_#] [int] NULL,
[Anneal_Number] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MRT_Number] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Double_Single_Length] [int] NULL,
[Carton_#] [int] NULL,
[Weight] [int] NULL,
[Sister_Piece_#] [int] NULL,
[Slab_Traveler_#] [int] NULL,
[Chemistry_#] [int] NULL,
[Traveler_Number] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Plate_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FinalID] [bit] NULL CONSTRAINT [DF_Product_FinalID] DEFAULT (0),
[Traveler_Step_#] [int] NULL,
[Marked] [int] NULL CONSTRAINT [DF_Product_Marked] DEFAULT (0),
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[Product_#] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Traveler_Step](
[Traveler_Step_#] [int] IDENTITY(1,1) NOT NULL,
[Traveler_#] [int] NOT NULL,
[Step] [int] NULL,
[Step_Description] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Station_#] [int] NOT NULL,
[Sub_Station_#] [int] NULL CONSTRAINT [DF_Traveler_Step_Sub_Station_#]
DEFAULT (0),
[Kanban_Point] [bit] NULL,
[Dependant_Traveler_Step_#] [int] NULL,
[Work_Standard_#] [int] NULL,
[Kanban_Notify_Station_#] [int] NULL,
[WIPStep] [int] NULL,
[Enabled] [bit] NULL,
[WIP_Status_Order] [int] NULL,
CONSTRAINT [PK_Traveler_Step] PRIMARY KEY CLUSTERED
(
[Traveler_Step_#] ASC
) ON [PRIMARY]
) ON [PRIMARY]
