Select results based on field value of 1 or NULL

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 2000 Forums

SQL Server Development (2000)

Select results based on field value of 1 or NULL

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-10-20 : 07:13:28

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

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-10-20 : 09:50:55

hmmmmm....finding hard to believe I have this group stumped. Did I not explain this well??

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-10-20 : 13:25:23

Ok I will try making this more simple. I know one of you has the answer...

I need a result set that provided different values in the [First inspec Date] And [Last inspec Date] aliases...depending on whether the field auto_inspect = 1 or NULL.

In other words
IF auto_inspect = 1 then
Select Min(p.auto_insp_start_time) AS [First inspec Date],
Max(p.auto_insp_timestamp) As [Last inspec Date]
FROM prod_data_test p
Where p.date_time Between @Startdate AND @Enddate

AND

IF auto_inspect = NULL then
SELECT Min(p.date_qc_inspected) As [First inspec Date],
Max(p.date_qc_inspected) As [Last inspec Date]
FROM prod_data_test p
Where p.date_time Between @Startdate AND @Enddate

Needs to return one result set

Min(p.date_qc_inspected) would = [First inspec Date]
Max(p.date_qc_inspected) would = [Last inspec Date]

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-10-20 : 13:57:52

It would appear I was going about the all wrong:

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources