|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Query to reconcile dataProcessdate 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 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) Show quote "Timothy.Ry***@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 > > 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: Show quote > 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.Ry***@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 > > > > 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. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous <Timothy.Ry***@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: >> 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.Ry***@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.Ry***@gmail.com wrote: Show quote > 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. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous <Timothy.Ry***@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 >  |
|||||||||||||||||||||||
Other interesting topics