Get user data in sub 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 prompt the user for a date and
time, then find the serial number of the closest broadcast from 105 to
that date and time. 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.
I have a query that does all of this, but it doesn't ask the user for a
date and time, it just finds the most recent serial number broadcast
and goes from there. I tried putting an @.UserDate parameter in the sub
query, but it generates an error saying you can't do that. Here is
that existing query:
SELECT SerialNumber
FROM [Broadcast] A
WHERE (ReportingPoint = '39') AND (ProcessDate >= (SELECT W.ProcessDate
FROM [Broadcast] AS W JOIN
(SELECT TOP
1 ProcessDate, SerialNumber
FROM
[Broadcast]
WHERE
ReportingPoint = '105'
ORDER BY
ProcessDate DESC) AS X ON W.SerialNumber = X.SerialNumber AND
W.ProcessDate < X.ProcessDate
WHERE W.ReportingPoint = '39'))
GROUP BY SerialNumber
ORDER BY SerialNumber
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!
TimTry this:
declare @.ProcessDate datetime
,@.SerialNumber varchar(50)
select @.ProcessDate = '8/7/2006 12:54:00 PM'
,@.SerialNumber = 'fsa679fsda679fdsa'
SELECT SerialNumber
FROM [Broadcast] A
WHERE (ReportingPoint = '39')
AND (ProcessDate >= (
SELECT W.ProcessDate
FROM [Broadcast] AS W
JOIN (SELECT @.ProcessDate as ProcessDate, @.SerialNumber as
SerialNumber) AS X
ON W.SerialNumber = X.SerialNumber
AND W.ProcessDate < X.ProcessDate
))
GROUP BY SerialNumber
ORDER BY SerialNumber
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 prompt the user for a date and
> time, then find the serial number of the closest broadcast from 105 to
> that date and time. 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.
> I have a query that does all of this, but it doesn't ask the user for a
> date and time, it just finds the most recent serial number broadcast
> and goes from there. I tried putting an @.UserDate parameter in the sub
> query, but it generates an error saying you can't do that. Here is
> that existing query:
> SELECT SerialNumber
> FROM [Broadcast] A
> WHERE (ReportingPoint = '39') AND (ProcessDate >=> (SELECT W.ProcessDate
> FROM [Broadcast] AS W JOIN
> (SELECT TOP
> 1 ProcessDate, SerialNumber
> FROM
> [Broadcast]
> WHERE
> ReportingPoint = '105'
> ORDER BY
> ProcessDate DESC) AS X ON W.SerialNumber = X.SerialNumber AND
> W.ProcessDate < X.ProcessDate
> WHERE W.ReportingPoint = '39'))
> GROUP BY SerialNumber
> ORDER BY SerialNumber
> 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|||I am not at a place where I can try this out, but I can't see where it
will take a user entered value to base the report. It looks like you
are hard coding a date/time. Is this the case?
TIm
paul.8.martin@.gmail.com wrote:
> Try this:
> declare @.ProcessDate datetime
> ,@.SerialNumber varchar(50)
> select @.ProcessDate = '8/7/2006 12:54:00 PM'
> ,@.SerialNumber = 'fsa679fsda679fdsa'
>
> SELECT SerialNumber
> FROM [Broadcast] A
> WHERE (ReportingPoint = '39')
> AND (ProcessDate >= (
> SELECT W.ProcessDate
> FROM [Broadcast] AS W
> JOIN (SELECT @.ProcessDate as ProcessDate, @.SerialNumber as
> SerialNumber) AS X
> ON W.SerialNumber = X.SerialNumber
> AND W.ProcessDate < X.ProcessDate
> ))
> GROUP BY SerialNumber
> ORDER BY SerialNumber
>
>
> 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 prompt the user for a date and
> > time, then find the serial number of the closest broadcast from 105 to
> > that date and time. 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.
> >
> > I have a query that does all of this, but it doesn't ask the user for a
> > date and time, it just finds the most recent serial number broadcast
> > and goes from there. I tried putting an @.UserDate parameter in the sub
> > query, but it generates an error saying you can't do that. Here is
> > that existing query:
> >
> > SELECT SerialNumber
> > FROM [Broadcast] A
> > WHERE (ReportingPoint = '39') AND (ProcessDate >=> > (SELECT W.ProcessDate
> > FROM [Broadcast] AS W JOIN
> > (SELECT TOP
> > 1 ProcessDate, SerialNumber
> > FROM
> > [Broadcast]
> > WHERE
> > ReportingPoint = '105'
> > ORDER BY
> > ProcessDate DESC) AS X ON W.SerialNumber = X.SerialNumber AND
> > W.ProcessDate < X.ProcessDate
> > WHERE W.ReportingPoint = '39'))
> > GROUP BY SerialNumber
> > ORDER BY SerialNumber
> >
> > 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|||Hi Timothy.
If i understand your concern you want to take current date as
Default Date and Current time as default time.It should prompt the user
to enter the date Every time when ever it is run.right?
Let me give some possible solutions you can achieve this but i am not
sure
1) Go to Report properties,Click Auto refresh for 15 secs of time
2) Go to Report parameters,Add Userdate and User time as Parameter
.Give Default values as
=Today for date and =format(Now(),"hh:mi") for time.use this
parameters in your query as @.userdate and @.usertime
If this also doesn't work for you ,You need to build a custom code
asking to take the values from the user
Regards
Raj Deep.A
Timothy.Rybak@.gmail.com wrote:
> I am not at a place where I can try this out, but I can't see where it
> will take a user entered value to base the report. It looks like you
> are hard coding a date/time. Is this the case?
> TIm
> paul.8.martin@.gmail.com wrote:
> > Try this:
> >
> > declare @.ProcessDate datetime
> > ,@.SerialNumber varchar(50)
> >
> > select @.ProcessDate = '8/7/2006 12:54:00 PM'
> > ,@.SerialNumber = 'fsa679fsda679fdsa'
> >
> >
> > SELECT SerialNumber
> > FROM [Broadcast] A
> > WHERE (ReportingPoint = '39')
> > AND (ProcessDate >= (
> > SELECT W.ProcessDate
> > FROM [Broadcast] AS W
> > JOIN (SELECT @.ProcessDate as ProcessDate, @.SerialNumber as
> > SerialNumber) AS X
> > ON W.SerialNumber = X.SerialNumber
> > AND W.ProcessDate < X.ProcessDate
> > ))
> > GROUP BY SerialNumber
> > ORDER BY SerialNumber
> >
> >
> >
> >
> >
> > 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 prompt the user for a date and
> > > time, then find the serial number of the closest broadcast from 105 to
> > > that date and time. 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.
> > >
> > > I have a query that does all of this, but it doesn't ask the user for a
> > > date and time, it just finds the most recent serial number broadcast
> > > and goes from there. I tried putting an @.UserDate parameter in the sub
> > > query, but it generates an error saying you can't do that. Here is
> > > that existing query:
> > >
> > > SELECT SerialNumber
> > > FROM [Broadcast] A
> > > WHERE (ReportingPoint = '39') AND (ProcessDate >=> > > (SELECT W.ProcessDate
> > > FROM [Broadcast] AS W JOIN
> > > (SELECT TOP
> > > 1 ProcessDate, SerialNumber
> > > FROM
> > > [Broadcast]
> > > WHERE
> > > ReportingPoint = '105'
> > > ORDER BY
> > > ProcessDate DESC) AS X ON W.SerialNumber = X.SerialNumber AND
> > > W.ProcessDate < X.ProcessDate
> > > WHERE W.ReportingPoint = '39'))
> > > GROUP BY SerialNumber
> > > ORDER BY SerialNumber
> > >
> > > 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|||No, I must not have explained it correctly.
The way the query works now is that it finds the most current serial
number from point 111, then finds the same serial number at point 39,
then returns a summary of what was broadcast from point 39 from that
time until the current time.
What I want it to do now is have the user enter a date/time. Use that
date/time to find the closest serial number from point 111. Find that
serial number at point 39, and return a summary of everything broadcast
between that time, and the time that the user entered.
Does this make more sense?
Tim
RajDeep wrote:
> Hi Timothy.
> If i understand your concern you want to take current date as
> Default Date and Current time as default time.It should prompt the user
> to enter the date Every time when ever it is run.right?
> Let me give some possible solutions you can achieve this but i am not
> sure
> 1) Go to Report properties,Click Auto refresh for 15 secs of time
> 2) Go to Report parameters,Add Userdate and User time as Parameter
> .Give Default values as
> =Today for date and =format(Now(),"hh:mi") for time.use this
> parameters in your query as @.userdate and @.usertime
> If this also doesn't work for you ,You need to build a custom code
> asking to take the values from the user
> Regards
> Raj Deep.A
>
> Timothy.Rybak@.gmail.com wrote:
> > I am not at a place where I can try this out, but I can't see where it
> > will take a user entered value to base the report. It looks like you
> > are hard coding a date/time. Is this the case?
> >
> > TIm
> >
> > paul.8.martin@.gmail.com wrote:
> > > Try this:
> > >
> > > declare @.ProcessDate datetime
> > > ,@.SerialNumber varchar(50)
> > >
> > > select @.ProcessDate = '8/7/2006 12:54:00 PM'
> > > ,@.SerialNumber = 'fsa679fsda679fdsa'
> > >
> > >
> > > SELECT SerialNumber
> > > FROM [Broadcast] A
> > > WHERE (ReportingPoint = '39')
> > > AND (ProcessDate >= (
> > > SELECT W.ProcessDate
> > > FROM [Broadcast] AS W
> > > JOIN (SELECT @.ProcessDate as ProcessDate, @.SerialNumber as
> > > SerialNumber) AS X
> > > ON W.SerialNumber = X.SerialNumber
> > > AND W.ProcessDate < X.ProcessDate
> > > ))
> > > GROUP BY SerialNumber
> > > ORDER BY SerialNumber
> > >
> > >
> > >
> > >
> > >
> > > 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 prompt the user for a date and
> > > > time, then find the serial number of the closest broadcast from 105 to
> > > > that date and time. 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.
> > > >
> > > > I have a query that does all of this, but it doesn't ask the user for a
> > > > date and time, it just finds the most recent serial number broadcast
> > > > and goes from there. I tried putting an @.UserDate parameter in the sub
> > > > query, but it generates an error saying you can't do that. Here is
> > > > that existing query:
> > > >
> > > > SELECT SerialNumber
> > > > FROM [Broadcast] A
> > > > WHERE (ReportingPoint = '39') AND (ProcessDate >=> > > > (SELECT W.ProcessDate
> > > > FROM [Broadcast] AS W JOIN
> > > > (SELECT TOP
> > > > 1 ProcessDate, SerialNumber
> > > > FROM
> > > > [Broadcast]
> > > > WHERE
> > > > ReportingPoint = '105'
> > > > ORDER BY
> > > > ProcessDate DESC) AS X ON W.SerialNumber = X.SerialNumber AND
> > > > W.ProcessDate < X.ProcessDate
> > > > WHERE W.ReportingPoint = '39'))
> > > > GROUP BY SerialNumber
> > > > ORDER BY SerialNumber
> > > >
> > > > 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