Select results based on field value of 1 or NULL

[jpiscit1]

Hi.

I have a table:

CREATE TABLE [dbo].[prod_data_test] (
[ID] [numeric](18, 0) NULL ,
[date_time] [datetime] NOT NULL ,
[work_order_no] [numeric](18, 0) NOT NULL ,
[item_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[date_qc_inspected] [datetime] NULL ,
[auto_inspect] [numeric](18, 0) NULL ,
[auto_insp_timestamp] [datetime] NULL ,
[auto_insp_start_time] [datetime] NULL
) ON [PRIMARY]
GO


With the following values:


Record 1

INSERT INTO [dbo].[prod_data_test]([date_time], [work_order_no],
[item_no], [date_qc_inspected], [auto_inspect],
[auto_insp_timestamp], [auto_insp_start_time])
VALUES(GetDate(), 12345, 'ABCD123', NULL, 1, '10/12/2004 10:13:38',
'10/12/2004 8:11:30')

Record 2

INSERT INTO [dbo].[prod_data_test]([date_time], [work_order_no],
[item_no], [date_qc_inspected], [auto_inspect],
[auto_insp_timestamp], [auto_insp_start_time])
VALUES(GetDate(), 12345, 'ABCD123', '10/18/2004 10:13:38',NULL, NULL, NULL)


The fields [date_qc_inspected] and [auto_insp_start_time] serve the same purpose. Both record the initial time an inspection was started. The only difference is that one is manual provided by an operator ([date_qc_inspected]) and the other is provided by a robot ([auto_insp_start_time]). Two inspection systems, manual and automatic.

I am looking to write a select statement that provides one result set but can differentiate between what was run manually and what was run automatically.

Case 1
If the column [auto_inspect] = 1, then I would expect the [auto_insp_start_time] to have a timestamp and the [date_qc_inspected] to be NULL.
Case 2
If the column [auto_inspect] = NULL, then I would expect the [date_qc_inspected] to have a timestamp and the [auto_insp_start_time] to be NULL.

Here is a sample select statement:

Declare @Startdate as datetime
Declare @EndDate as datetime
Set @Startdate = '10/01/04'
Set @Enddate = '10/25/04'

SELECT
Min(p.date_qc_inspected) As [First inspec Date],
Max(p.date_qc_inspected) as [Last Inspec Date],
Convert(numeric (6,2), (DateDiff(mi, Min(p.date_time), Max(p.date_qc_inspected))/60.0)/24.0) as [Duration],
p.item_no as [Part Number],
(case when p.auto_inspect = 1 then 'AUTO' else 'MANUAL' end)As [Inspection Type]

FROM prod_data_test p

Where p.date_time Between @Startdate AND @Enddate
GROUP BY p.item_no, p.work_order_no, p.auto_inspect

Order by [First inspec Date] DESC


I want to modify this Select statement so that it can differentiate between which record was run AUTO and which record was run MANUALLY and associate the corresponding timestamp to the alias names [First inspec Date] and [Last Inspec Date].

So in Case 1 from above (auto inspected):
Min(p.auto_insp_start_time) would = [First inspec Date]
Max(p.auto_insp_timestamp) would = [Last inspec Date]

and in Case 2 from above (manually inspected):
Min(p.date_qc_inspected) would = [First inspec Date]
Max(p.date_qc_inspected) would = [Last inspec Date]

Note: The same field is used in manual inspections for start and stop times because there would be multiple records for one inspection. The automated timestamp has a start (p.auto_insp_start_time) and stop (p.auto_insp_timestamp) timestamp which would all be on one record.

Here would be the expected results of what I want to see:
First Inspec Date Last Inspec Date Duration Part Number Inpection Type
2004-10-18 10:13:38.000 2004-10-18 10:13:38.000 -1.87 ABCD123 MANUAL
2004-10-12 8:11:30.000 2004-10-12 10:13:38 diff... ABCD123 AUTO

I think I need to do some kind of SELECT/CASE for this but am not quite sure how I go about testing the value auto_inspect AND changing the result set all in the same step.

Can someone help?

Thanks in advance.

John



Your limits are only as far as you set your boundries......

 

[jpiscit1]

Here is what I was looking for:

Declare @Startdate as datetime
Declare @EndDate as datetime
Set @Startdate = '10/1/03'
Set @Enddate = '10/19/04'

Select Distinct Min(p.date_qc_inspected) As [First inspec Date],
Max(p.date_qc_inspected) as [Last inspec Date],
(case when auto_inspect = 1 then 'AUTO' else 'MANUAL' end)As [Inspection Type]

FROM prod_data_test p

Where (p.date_qc_inspected >= @Startdate AND p.date_qc_inspected <= @Enddate)
AND auto_inspect is NULL

GROUP BY p.auto_inspect


UNION

Select Distinct Min(p.auto_insp_start_time) As [First inspec Date],
Max(p.auto_insp_timestamp) as [Last inspec Date],
(case when auto_inspect = 1 then 'AUTO' else 'MANUAL' end)As [Inspection Type]


FROM prod_data_test p


Where (auto_insp_start_time >= @Startdate AND p.auto_insp_timestamp <= @Enddate)
AND auto_inspect = 1

GROUP BY p.auto_inspect
Order by [First inspec Date] DESC

Your limits are only as far as you set your boundries......