Create view to restrict data based on dates in two tables

[elsenorjose]

Hello everyone,

I am a relative novice when it comes to SQL but can usually write queries that retrieve data as needed. However, I have a problem that my limited skillset can't solve.

I need to create a view that restricts data by eliminating records where a date exists in two tables. The area is drug manufacturing lot samples. I have two tables LOT and LOT_SAMPLING_POINT. In LOT, there exists a DATE_CREATED which is when a drum sample arrives. In LOT_SAMPLING_POINT, there exists DISPOSITION_DATE which is when that drug sample is approved.

I can have multiple rows in LOT_SAMPLING_POINT for one lot but typically only one row in LOT. My challenge is to exclude records from both tables when a date exists for DATE_CREATED and DISPOSITION_DATE, as well as when NULLs exist. Here is some sample data based on a LEFT OUTER JOIN of both tables.

date_created	disposition_date	lot_number
3/4/2009	3/10/2009	        003079AX10
3/4/2009	NULL	                003079AX10
10/13/2010	11/12/2010	        BN1002B
10/13/2010	NULL	                BN1002B
10/13/2010	NULL	                BN1002B

In my view, I would not want any of these records to be included since there is at least one instance of a date_created and disposition_date for each lot.

I wrote the following but it is only eliminating the record that has both a date_created and a disposition_date:

select 
  lims.lot.date_created
, lims.lot_sampling_point.disposition_date
, lims.lot.lot_name
from lims.lot left outer join lims.lot_sampling_point
on lims.lot.lot_name = lims.lot_sampling_point.lot_name
where not (date_created is not null and disposition_date is not null)

Like I mentioned, my SQL skills are limited to pretty much just querying for data retrieval so I'm not very well versed in cursors or stored procedures but it looks like that might be the approach to take? Can anyone help with a solution as well as some teaching?

Thank you

 

[elsenorjose]

Sorry, updating with SQL version (SQL Server 2005)

 

[elsenorjose]

Any thoughts?

 

[sparkbyte]

where (NOT date_created is not null)
	  OR (disposition_date is not null)

Try changing your AND to OR.



Thanks

John Fuhrman

 

[elsenorjose]

Thanks John, but this still retrieves data for the lots in my example. What I want is for a query to exclude those records from the dataset.

 

[gmmastros]

elsenorjose,

Maybe it's just me, but I think your question is confusing (sorry).

My challenge is to [!]exclude[/!] records from both tables [!]when a date exists[/!] for DATE_CREATED and DISPOSITION_DATE, as well as [!]when NULLs exist[/!].

My confusion is.... If you are using a DateTime data type, then there must be a valid date or a NULL. Those are the only choices. I would encourage you to clarify your question a bit more as well as showing sample data from both tables. Show data that should be included as well as excluded. I suspect that this will help you get a better response to your question.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[elsenorjose]

Sorry for the confusion.

Let me deconstruct the data in my example above.

In the LOT table I have, among other things, the date_created and lot_number

date_created lot_number
3/4/2009 003079AX10
10/13/2010 BN1002B
10/13/2010 BN1002C

In the LOT_SAMPLING_POINT table I have, among other things, the disposition_date and lot_number

disposition_date lot_number
3/10/2009 003079AX10
NULL 003079AX10
11/12/2010 BN1002B
NULL BN1002B
NULL BN1002B
NULL BN1002C

What I am trying to do is exclude ALL instances of a lot_number if it has both a created_date and a disposition_date and any NULL values for disposition_date as well. So, for the above example, I would not want to see 003079AX10 or BN1002B at all but I would want BN1002C since it does not have a disposition_date.

The business rule is that when a lot is received, a date_created value is entered. Once the lot is approved, a disposition_date value is entered. So, if the lot has been received and approved, I don't want to see it at all. If it has only been received but not approved, I want to see it.

Thanks,
Jose

 

[gmmastros]

That makes more sense to me now. I can't respond now, bit if you don't have an acceptable answer in the morning, I'll post a query and completely explain it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[elsenorjose]

Thank you George. I appreciate the help.

 

[PDreyer]

select * from lot 
where lot_number in 
(SELECT lot_number
 FROM lot_sampeling_point 
 group by lot_number
 having min(disposition_date) is null)

 

[gmmastros]

The query posted by PDreyer appears to work well. It's not how I would have written the query though.

My suggested query:

Select Lot.lot_number, Lot.date_created
From   Lot
       Inner Join Lot_Sampling_Point
         On Lot.lot_number = Lot_Sampling_Point.lot_number
Group By Lot.lot_number, Lot.date_created
Having Count(disposition_date) = 0

Now, for the explanation. Seems like the important part of this query is controlled by the lot point sampling table. Specifically, we want the rows from this table where all of the disposition dates are NULL. As such, I first created a query that satisfies this criteria.

[tt][blue]
Select lot_number
From Lot_Sampling_Point
Group By lot_number
Having Count(disposition_date) = 0
[/blue][/tt]

This query is pretty straight forward, with one minor tricky point. Notice this part: Count(disposition_date). All of the aggregate functions (min, max, count, sum, etc) ignore null values. So, if all of the disposition_dates (for a lot_number) are null, count will return 0. PDreyer used something very similar but chose to use Min instead. Min will only return NULL if ALL of the values are NULL, otherwise it would return an actual date.

Next, I wanted to return the date_created from the lot table, so I join to that table and group by the date_created column and the lot_number column.

Note that this works well for returning all of the data from the lot table, but not the lot_sampling_point table. If you need additional data from the table, the query would be different.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

George, very clean solution. One question... just so I get a better understanding of query execution.

Select lot_number
From Lot_Sampling_Point
Group By lot_number
Having Count(disposition_date) = 0

Gets all rows where disposition_date is Null you then add then JOIN

Inner Join Lot_Sampling_Point On Lot.lot_number = Lot_Sampling_Point.lot_number

which allows us to show the rows from the table Lot_Sampling_Point

Now for the question.

Does the query engine perform the join on the tables 1st then do the filter "Having Count(disposition_date) = 0" ?

From 1st glance it would look like the logical execution order would be like.

1)
Select Lot.lot_number, Lot.date_created
From Lot
Inner Join Lot_Sampling_Point
On Lot.lot_number = Lot_Sampling_Point.lot_number
2)
Having Count(disposition_date) = 0
3)
Group By Lot.lot_number, Lot.date_created

Where as PDreyer's solution filters the data then gets the rows from the result. Correct?

Thanks

John Fuhrman

 

[gmmastros]

John, I suspect you may be correct. I also wouldn't be surprised if pdreyer's advice executes faster than mine.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[elsenorjose]

Thank you all for your help. These were very clean and simple solutions. My manager came up with a cursor based solution which worked, but even he was impressed by the simplicity of your solutions.

Thanks again!
Jose