Query to reconcile data

I have a table with 3 columns:
Processdate ReportingPoint SerialNumber
When my customer starts construction on a part, they send a broadcast
to me that contains a date/time stamp, the serial number of the part,
and the reporting point "39".
Several hours, possibly days later, when the part is completed, they
send another broadcast containing a date/time stamp, the serial number
of the part, and the reporting point "111".
I need to create a query that can perform a basic reconciliation
between these two points. In other words, show me everything, by
serial number, that has a reporting point of "39", but no reporting
point of "111".
It seems that this would be easy, but I don't even know where to get
started.
Thank you,
Timselect field1,field2 from table
where reportingpoint = 39 and not exists
(select serial number from table as table2 where reportingpoint = 111
and table2.serialnumber =table.serialnumber)
"Timothy.Rybak@.gmail.com" wrote:

> I have a table with 3 columns:
> Processdate ReportingPoint SerialNumber
> When my customer starts construction on a part, they send a broadcast
> to me that contains a date/time stamp, the serial number of the part,
> and the reporting point "39".
> Several hours, possibly days later, when the part is completed, they
> send another broadcast containing a date/time stamp, the serial number
> of the part, and the reporting point "111".
> I need to create a query that can perform a basic reconciliation
> between these two points. In other words, show me everything, by
> serial number, that has a reporting point of "39", but no reporting
> point of "111".
> It seems that this would be easy, but I don't even know where to get
> started.
> Thank you,
> Tim
>|||Hey this is fun... Here is one way, I'm sure there are others:
select * from ConstructionStatus CS1 left join ConstructionStatus CS2
on CS1.SerNo = CS2.SerNo AND CS1.ReportingPoint = 39 AND
CS2.ReportingPoint != 39
where
CS2.ReportingPoint IS NULL
So, join two versions of the table: one with ReportingPoint 39 and the
other without ReportingPoint 39, and then look for the NULLS in the one
without ReportingPoint 39.
Hope that makes sense.
Timothy.Rybak@.gmail.com wrote:
> I have a table with 3 columns:
> Processdate ReportingPoint SerialNumber
> When my customer starts construction on a part, they send a broadcast
> to me that contains a date/time stamp, the serial number of the part,
> and the reporting point "39".
> Several hours, possibly days later, when the part is completed, they
> send another broadcast containing a date/time stamp, the serial number
> of the part, and the reporting point "111".
> I need to create a query that can perform a basic reconciliation
> between these two points. In other words, show me everything, by
> serial number, that has a reporting point of "39", but no reporting
> point of "111".
> It seems that this would be easy, but I don't even know where to get
> started.
> Thank you,
> Tim|||Isn't the virtually the same question that was asked and answered in the
programming group on 7/25, 8:21 AM, subject: Help with a Query?
Perhaps that same information still applies.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Timothy.Rybak@.gmail.com> wrote in message
news:1154103522.720502.274630@.m73g2000cwd.googlegroups.com...
>I have a table with 3 columns:
> Processdate ReportingPoint SerialNumber
> When my customer starts construction on a part, they send a broadcast
> to me that contains a date/time stamp, the serial number of the part,
> and the reporting point "39".
> Several hours, possibly days later, when the part is completed, they
> send another broadcast containing a date/time stamp, the serial number
> of the part, and the reporting point "111".
> I need to create a query that can perform a basic reconciliation
> between these two points. In other words, show me everything, by
> serial number, that has a reporting point of "39", but no reporting
> point of "111".
> It seems that this would be easy, but I don't even know where to get
> started.
> Thank you,
> Tim
>|||MB and Jeff - Dead on accurate! Exactly what I needed!
Arnie - I guess it was close, but it was this issue of looking for a
serial that had one reporting point but not the other that was
troubling me. I never would have though to use the same table defined
two ways!
Also, as I have stated before, I am pretty new to SQL, but I am
learning every day. People like you make it fun and enjoyable!
Tim
Jeff Ericson wrote:[vbcol=seagreen]
> select field1,field2 from table
> where reportingpoint = 39 and not exists
> (select serial number from table as table2 where reportingpoint = 111
> and table2.serialnumber =table.serialnumber)
> "Timothy.Rybak@.gmail.com" wrote:
>|||I'm glad that you are getting the help you need here -that what this
resource is about.
My point was to help you realize the commonality between the two situations
so that you could begin to extrapolate the things that you are learning.
Let us know if/when we can help you again. And don't forget to occasionally
stop in and share your growing knowledge and experience. After all, there is
always someone that knows less and needs help.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Timothy.Rybak@.gmail.com> wrote in message
news:1154110530.612500.243460@.p79g2000cwp.googlegroups.com...
> MB and Jeff - Dead on accurate! Exactly what I needed!
> Arnie - I guess it was close, but it was this issue of looking for a
> serial that had one reporting point but not the other that was
> troubling me. I never would have though to use the same table defined
> two ways!
> Also, as I have stated before, I am pretty new to SQL, but I am
> learning every day. People like you make it fun and enjoyable!
> Tim
> Jeff Ericson wrote:
>