Help with query

Let me preface this by saying that am relatively new to SQL.
I have a database that is updated every10 to 15 seconds with broadcasts
from our customer. These broadcasts come from 2 distinct points in
their operation. The first point - 39 - tells me what to build and
ship to them. The second point - 105 - tells me when they have used my
product on the line. Every thing is controlled by a serial number.
So, for example, at 8 AM they will send a broadcast saying that serial
number 1234 is at point 39 (my cue to build and ship the part), then
about 3 hours later I will get a broadcast from point 105 that serial
number 1234 has been built (my cue that my parts have been consumed).
In the intervening 3 hours, there will have been a bunch of broadcasts
through each point. Each broadcast is writted to a single table with
the following fields:
ProcessDate - The date/time stamp that the broadcast was received
SerialNumber - The serial number referenced by the broadcast
ReportingPoint - The point that generated the broadcast (either 39 or
105)
OK, so here is the query I want to build:
Whenever the query is run, it should find the serial number of most
current broadcast from 105. Then it should use that serial number to
find out the date/time that unit passed through 39. Then it should
return a list of everything that has passed through 39 between that
time and the current time.
If you need more info, or if this isn't clear, please ask. I don't
even know where to get started on this one.
Thanks!
TimThe way to approach these is to write it a little bit at a time,
building it up.

>Whenever the query is run, it should find the serial number of most
>current broadcast from 105.
SELECT TOP 1 ProcessDate, SerialNumber
FROM Whatever
WHERE ReportingPoint = 105
ORDER BY ProcessDate

>Then it should use that serial number to
>find out the date/time that unit passed through 39.
SELECT ProcessDate
FROM Whatever as W
JOIN (select TOP 1 ProcessDate, SerialNumber
from Whatever
where ReportingPoint = 105
order by ProcessDate) as X
ON W.SerialNumber = X.SerialNumber
WHERE W.ReportingPoint = 39

>Then it should
>return a list of everything that has passed through 39 between that
>time and the current time.
SELECT *
FROM Whatever as A
WHERE ReportingPoint = 39
AND ProcessDate >=
(select ProcessDate
from Whatever as W
join (select TOP 1 ProcessDate, SerialNumber
from Whatever
where ReportingPoint = 105
order by ProcessDate) as X
on W.SerialNumber = X.SerialNumber)
All untested, of course.
Roy Harvey
Beacon Falls, CT
On 25 Jul 2006 08:21:33 -0700, Timothy.Rybak@.gmail.com wrote:

>Let me preface this by saying that am relatively new to SQL.
>I have a database that is updated every10 to 15 seconds with broadcasts
>from our customer. These broadcasts come from 2 distinct points in
>their operation. The first point - 39 - tells me what to build and
>ship to them. The second point - 105 - tells me when they have used my
>product on the line. Every thing is controlled by a serial number.
>So, for example, at 8 AM they will send a broadcast saying that serial
>number 1234 is at point 39 (my cue to build and ship the part), then
>about 3 hours later I will get a broadcast from point 105 that serial
>number 1234 has been built (my cue that my parts have been consumed).
>In the intervening 3 hours, there will have been a bunch of broadcasts
>through each point. Each broadcast is writted to a single table with
>the following fields:
>ProcessDate - The date/time stamp that the broadcast was received
>SerialNumber - The serial number referenced by the broadcast
>ReportingPoint - The point that generated the broadcast (either 39 or
>105)
>OK, so here is the query I want to build:
>Whenever the query is run, it should find the serial number of most
>current broadcast from 105. Then it should use that serial number to
>find out the date/time that unit passed through 39. Then it should
>return a list of everything that has passed through 39 between that
>time and the current time.
>If you need more info, or if this isn't clear, please ask. I don't
>even know where to get started on this one.
>Thanks!
>Tim|||Please excuse my ignorance.
When I run the first part of the query, the TOP 1 is returning the
earliest instance of the 105 broadcast point in the table, instead of
the most recent entry. Is this correct, or am I doing something wrong
already?
The ProcessDate column is in the standard 'mm/dd/yyyy hh:mm:ss AM/PM'
format.
Roy Harvey wrote:[vbcol=seagreen]
> The way to approach these is to write it a little bit at a time,
> building it up.
>
> SELECT TOP 1 ProcessDate, SerialNumber
> FROM Whatever
> WHERE ReportingPoint = 105
> ORDER BY ProcessDate
>
> SELECT ProcessDate
> FROM Whatever as W
> JOIN (select TOP 1 ProcessDate, SerialNumber
> from Whatever
> where ReportingPoint = 105
> order by ProcessDate) as X
> ON W.SerialNumber = X.SerialNumber
> WHERE W.ReportingPoint = 39
>
>
> SELECT *
> FROM Whatever as A
> WHERE ReportingPoint = 39
> AND ProcessDate >=
> (select ProcessDate
> from Whatever as W
> join (select TOP 1 ProcessDate, SerialNumber
> from Whatever
> where ReportingPoint = 105
> order by ProcessDate) as X
> on W.SerialNumber = X.SerialNumber)
> All untested, of course.
> Roy Harvey
> Beacon Falls, CT
> On 25 Jul 2006 08:21:33 -0700, Timothy.Rybak@.gmail.com wrote:
>|||On 25 Jul 2006 09:45:42 -0700, Timothy.Rybak@.gmail.com wrote:

>Please excuse my ignorance.
>When I run the first part of the query, the TOP 1 is returning the
>earliest instance of the 105 broadcast point in the table, instead of
>the most recent entry. Is this correct, or am I doing something wrong
>already?
Simple explanation: I left off the DESC on the ORDER BY.
SELECT TOP 1 ProcessDate, SerialNumber
FROM Whatever
WHERE ReportingPoint = 105
ORDER BY ProcessDate DESC

>Then it should use that serial number to
>find out the date/time that unit passed through 39.
SELECT ProcessDate
FROM Whatever as W
JOIN (select TOP 1 ProcessDate, SerialNumber
from Whatever
where ReportingPoint = 105
order by ProcessDate DESC) as X
ON W.SerialNumber = X.SerialNumber
WHERE W.ReportingPoint = 39

>Then it should
>return a list of everything that has passed through 39 between that
>time and the current time.
SELECT *
FROM Whatever as A
WHERE ReportingPoint = 39
AND ProcessDate >=
(select ProcessDate
from Whatever as W
join (select TOP 1 ProcessDate, SerialNumber
from Whatever
where ReportingPoint = 105
order by ProcessDate DESC) as X
on W.SerialNumber = X.SerialNumber)
Roy|||This has been a great help! I have gotten the first two parts to work.
The last part is still giving me problems. Here is how SQL is
interpreting the code:
SELECT *
FROM [Whatever]
WHERE (ReportingPoint = '39') AND (ProcessDate >=
(SELECT W.ProcessDate, W.SerialNumber
FROM [Whatever] W INNER JOIN
(SELECT TOP 1 ProcessDate, SerialNumber
FROM [Whatever]
WHERE (ReportingPoint = 'D05')
ORDER BY ProcessDate DESC) X ON W.SerialNumber =
X.SerialNumber))
It is giving me the following error:
"Only one expression can be specified in the select list when the
subquery is not introducted with EXISTS"
While I understand the theory of what you wrote, I don't have the
knowledge to fix the issue.
Can you help?
Tim
Roy Harvey wrote:
> On 25 Jul 2006 09:45:42 -0700, Timothy.Rybak@.gmail.com wrote:
>
> Simple explanation: I left off the DESC on the ORDER BY.
> SELECT TOP 1 ProcessDate, SerialNumber
> FROM Whatever
> WHERE ReportingPoint = 105
> ORDER BY ProcessDate DESC
>
> SELECT ProcessDate
> FROM Whatever as W
> JOIN (select TOP 1 ProcessDate, SerialNumber
> from Whatever
> where ReportingPoint = 105
> order by ProcessDate DESC) as X
> ON W.SerialNumber = X.SerialNumber
> WHERE W.ReportingPoint = 39
>
>
> SELECT *
> FROM Whatever as A
> WHERE ReportingPoint = 39
> AND ProcessDate >=
> (select ProcessDate
> from Whatever as W
> join (select TOP 1 ProcessDate, SerialNumber
> from Whatever
> where ReportingPoint = 105
> order by ProcessDate DESC) as X
> on W.SerialNumber = X.SerialNumber)
> Roy|||On 25 Jul 2006 10:43:27 -0700, Timothy.Rybak@.gmail.com wrote:

>This has been a great help! I have gotten the first two parts to work.
> The last part is still giving me problems.
Sorry, I just have been asleep. Lots of problems, which I think I
have fixed.
create table Whatever
(ProcessDate datetime, -- The date/time stamp that the broadcast was
received
SerialNumber int, -- The serial number referenced by the broadcast
ReportingPoint int) -- The point that generated the broadcast (either
39 or 105)
INSERT Whatever values('1 Jan 2002 00:00', 123, 39)
INSERT Whatever values('1 Jan 2002 01:00', 456, 39)
INSERT Whatever values('1 Jan 2002 02:00', 789, 39)
INSERT Whatever values('1 Jan 2002 03:00', 368, 39)
INSERT Whatever values('1 Jan 2002 04:00', 246, 39)
INSERT Whatever values('1 Jan 2002 05:00', 468, 39)
INSERT Whatever values('1 Jan 2002 04:30', 123, 105)
INSERT Whatever values('1 Jan 2002 06:00', 456, 105)
-->Whenever the query is run, it should find the serial number of most
-->current broadcast from 105.
SELECT TOP 1 ProcessDate, SerialNumber
FROM Whatever
WHERE ReportingPoint = 105
ORDER BY ProcessDate DESC
ProcessDate SerialNumber
--- --
2002-01-01 06:00:00.000 456
-->Then it should use that serial number to
-->find out the date/time that unit passed through 39.
SELECT max(W.ProcessDate)
FROM Whatever as W
JOIN (select TOP 1 ProcessDate, SerialNumber
from Whatever
where ReportingPoint = 105
order by ProcessDate DESC) as X
ON W.SerialNumber = X.SerialNumber
AND W.ProcessDate < X.ProcessDate
WHERE W.ReportingPoint = 39
---
2002-01-01 01:00:00.000
-->Then it should
-->return a list of everything that has passed through 39 between that
-->time and the current time.
SELECT *
FROM Whatever as A
WHERE ReportingPoint = 39
AND ProcessDate >=
(select W.ProcessDate
from Whatever as W
join (select TOP 1 ProcessDate, SerialNumber
from Whatever
where ReportingPoint = 105
order by ProcessDate DESC) as X
on W.SerialNumber = X.SerialNumber
and W.ProcessDate < X.ProcessDate
where W.ReportingPoint = 39)
ProcessDate SerialNumber
ReportingPoint
--- --
--
2002-01-01 01:00:00.000 456 39
2002-01-01 02:00:00.000 789 39
2002-01-01 03:00:00.000 368 39
2002-01-01 04:00:00.000 246 39
2002-01-01 05:00:00.000 468 39
Roy|||Ok, I figured out, I think, that I can't have two values in the second
Select statement, or else the compison won't work - ProcessDate>= must
return a date, and a date only.
So, I fixed that. Now, I have the following:
SELECT *
FROM [Whatever] A
WHERE (ReportingPoint = '39') AND (ProcessDate >=
(SELECT W.ProcessDate
FROM [Whatever] W INNER JOIN
(SELECT TOP 1 ProcessDate,
SerialNumber
FROM [Whatever]
WHERE ReportingPoint = '105'
ORDER BY ProcessDate DESC) X ON
W.SerialNumber = X.SerialNumber))
I am getting this error now:
Subquery returned more than one value. This is not permitted when the
subquery follows =, !=, <, <=, >, >= or when the subquery is used as an
expression.
Any help?
Thanks!
Tim|||We must have posted at the same time.
Your new query is PERFECT!
Thank you so much for your patience, time, and effort!
Tim|||Ok, I got this to work, and everyone was happy.
Now, they want to be able to do this, but they want to be able to pick
the moment in time, rather than just using the most up to date data.
So, I tried to modify the code as follows, but I get the error: Must
Declare the variable @.GetDate
I don't want to declare it, I want the user to have to enter it. Any
ideas?
SELECT *
FROM Whatever as A
WHERE ReportingPoint = 39
AND ProcessDate >=
(select W.ProcessDate
from Whatever as W
join (select ProcessDate, SerialNumber
from Whatever
where ReportingPoint = 105
and ProcessDate >= @.GetDate
order by ProcessDate DESC) as X
on W.SerialNumber = X.SerialNumber
and W.ProcessDate < X.ProcessDate
where W.ReportingPoint = 39)
INSERT Whatever values('1 Jan 2002 04:00', 246, 39)
> INSERT Whatever values('1 Jan 2002 05:00', 468, 39)
> INSERT Whatever values('1 Jan 2002 04:30', 123, 105)
> INSERT Whatever values('1 Jan 2002 06:00', 456, 105)
>
> -->Whenever the query is run, it should find the serial number of most
> -->current broadcast from 105.
> SELECT TOP 1 ProcessDate, SerialNumber
> FROM Whatever
> WHERE ReportingPoint = 105
> ORDER BY ProcessDate DESC
> ProcessDate SerialNumber
> --- --
> 2002-01-01 06:00:00.000 456
> -->Then it should use that serial number to
> -->find out the date/time that unit passed through 39.
> SELECT max(W.ProcessDate)
> FROM Whatever as W
> JOIN (select TOP 1 ProcessDate, SerialNumber
> from Whatever
> where ReportingPoint = 105
> order by ProcessDate DESC) as X
> ON W.SerialNumber = X.SerialNumber
> AND W.ProcessDate < X.ProcessDate
> WHERE W.ReportingPoint = 39
> ---
> 2002-01-01 01:00:00.000
> -->Then it should
> -->return a list of everything that has passed through 39 between that
> -->time and the current time.
>
> SELECT *
> FROM Whatever as A
> WHERE ReportingPoint = 39
> AND ProcessDate >=
> (select W.ProcessDate
> from Whatever as W
> join (select TOP 1 ProcessDate, SerialNumber
> from Whatever
> where ReportingPoint = 105
> order by ProcessDate DESC) as X
> on W.SerialNumber = X.SerialNumber
> and W.ProcessDate < X.ProcessDate
> where W.ReportingPoint = 39)
> ProcessDate SerialNumber
> ReportingPoint
> --- --
> --
> 2002-01-01 01:00:00.000 456 39
> 2002-01-01 02:00:00.000 789 39
> 2002-01-01 03:00:00.000 368 39
> 2002-01-01 04:00:00.000 246 39
> 2002-01-01 05:00:00.000 468 39
> Roy|||What are the users using to run the query? A front-end application?
Query Analyzer? SQL Server has NO provision for prompting for input.
Perhaps the answer is to put it all in a stored procedure and pass
@.DateLimit as a parameter. Then, whatever the front end is, EXEC the
proc with the date (in single quotes) as a parameter.
Roy Harvey
Beacon Falls, CT
On 3 Aug 2006 12:24:59 -0700, Timothy.Rybak@.gmail.com wrote:
[vbcol=seagreen]
>Ok, I got this to work, and everyone was happy.
>Now, they want to be able to do this, but they want to be able to pick
>the moment in time, rather than just using the most up to date data.
>So, I tried to modify the code as follows, but I get the error: Must
>Declare the variable @.GetDate
>I don't want to declare it, I want the user to have to enter it. Any
>ideas?
>SELECT *
> FROM Whatever as A
> WHERE ReportingPoint = 39
> AND ProcessDate >=
> (select W.ProcessDate
> from Whatever as W
> join (select ProcessDate, SerialNumber
> from Whatever
> where ReportingPoint = 105
> and ProcessDate >= @.GetDate
> order by ProcessDate DESC) as X
> on W.SerialNumber = X.SerialNumber
> and W.ProcessDate < X.ProcessDate
> where W.ReportingPoint = 39)
>
>
>INSERT Whatever values('1 Jan 2002 04:00', 246, 39)