Getting A Query Parameter from Elsewhere

I have a query on a table. This table has a datetime column.
I have another table. This table also has a datetime column. It has only one
row. It basically to store some simple configuration data.
There is no relation between the tables.
I want to be able to limit the entries returned by the query on the first
table by only returning rows that have a date that is less than the date in
the row in the second table.
I'm actually having trouble getting this to work properly. I've tried
subqueries, passing it through the report as a parameter, etc... None are
working.
How should i attack this?It would be possible to use a GROUP BY / HAVING in the SELECT if that was a
possibility for you. I am sure there are much better ways, but this is off
the cuff:
Select
MyDate, Field1, Field2 from table1
Group by
MyDate,Field1,Field2
HAVING MyDate < (Select LookUpDate from Table2)
You could always do it in a stored procedure and call in the value of Table2
into a variable @.LokUpDate that yo could compare. I assume that the
LookUpDate in my example to match your description changes on a regular
basis?
Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
http://www.apress.com
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
>I have a query on a table. This table has a datetime column.
> I have another table. This table also has a datetime column. It has only
> one
> row. It basically to store some simple configuration data.
> There is no relation between the tables.
> I want to be able to limit the entries returned by the query on the first
> table by only returning rows that have a date that is less than the date
> in
> the row in the second table.
> I'm actually having trouble getting this to work properly. I've tried
> subqueries, passing it through the report as a parameter, etc... None are
> working.
> How should i attack this?|||If I use GROUP BY, will that not require aggregation of the results of the
query?
Perhaps it would be helpful to see the existing query:
SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS QTY,
(SELECT SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE SALESTABLE.RECID =MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
MARKUPTRANS.MARKUPCODE ='Freight') AS FreightValue,
(SELECT SUM(INVENTSUM.POSTEDVALUE)
FROM INVENTSUM
WHERE SALESLINE.INVENTDIMID =INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
FROM SALESTABLE INNER JOIN
SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID = 'acm')
AND (SALESTABLE.SALESSTATUS = 1)
That is table one. I want to limit on a column called CREATEDDATE.
The other query looks like:
SELECT CUTOFFDATE from CONFIGDATA
"Rodney Landrum" wrote:
> It would be possible to use a GROUP BY / HAVING in the SELECT if that was a
> possibility for you. I am sure there are much better ways, but this is off
> the cuff:
> Select
> MyDate, Field1, Field2 from table1
> Group by
> MyDate,Field1,Field2
> HAVING MyDate < (Select LookUpDate from Table2)
> You could always do it in a stored procedure and call in the value of Table2
> into a variable @.LokUpDate that yo could compare. I assume that the
> LookUpDate in my example to match your description changes on a regular
> basis?
> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
> http://www.apress.com
> "Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
> message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
> >I have a query on a table. This table has a datetime column.
> >
> > I have another table. This table also has a datetime column. It has only
> > one
> > row. It basically to store some simple configuration data.
> >
> > There is no relation between the tables.
> >
> > I want to be able to limit the entries returned by the query on the first
> > table by only returning rows that have a date that is less than the date
> > in
> > the row in the second table.
> >
> > I'm actually having trouble getting this to work properly. I've tried
> > subqueries, passing it through the report as a parameter, etc... None are
> > working.
> >
> > How should i attack this?
>
>|||If I use GROUP BY, will that not require aggregation of the results of the
query?
Perhaps it would be helpful to see the existing query:
SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS QTY,
(SELECT SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE SALESTABLE.RECID =MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
MARKUPTRANS.MARKUPCODE ='Freight') AS FreightValue,
(SELECT SUM(INVENTSUM.POSTEDVALUE)
FROM INVENTSUM
WHERE SALESLINE.INVENTDIMID =INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
FROM SALESTABLE INNER JOIN
SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID = 'acm')
AND (SALESTABLE.SALESSTATUS = 1)
That is table one. I want to limit on a column called CREATEDDATE.
The other query looks like:
SELECT CUTOFFDATE from CONFIGDATA
"Rodney Landrum" wrote:
> It would be possible to use a GROUP BY / HAVING in the SELECT if that was a
> possibility for you. I am sure there are much better ways, but this is off
> the cuff:
> Select
> MyDate, Field1, Field2 from table1
> Group by
> MyDate,Field1,Field2
> HAVING MyDate < (Select LookUpDate from Table2)
> You could always do it in a stored procedure and call in the value of Table2
> into a variable @.LokUpDate that yo could compare. I assume that the
> LookUpDate in my example to match your description changes on a regular
> basis?
> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
> http://www.apress.com
> "Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
> message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
> >I have a query on a table. This table has a datetime column.
> >
> > I have another table. This table also has a datetime column. It has only
> > one
> > row. It basically to store some simple configuration data.
> >
> > There is no relation between the tables.
> >
> > I want to be able to limit the entries returned by the query on the first
> > table by only returning rows that have a date that is less than the date
> > in
> > the row in the second table.
> >
> > I'm actually having trouble getting this to work properly. I've tried
> > subqueries, passing it through the report as a parameter, etc... None are
> > working.
> >
> > How should i attack this?
>
>|||You really do not have to add an aggregate function. You can always set a
variable and use that in the Where clause if you do not want to use the
GROUP BY. Something like ( and I added CREATEDATE to the WHERE clause also)
:
Declare @.CUTOFFDATE as DATETIME
SELECT @.CUTOFFDATE=CUTOFFDATE from CONFIGDATA
SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS
QTY,
(SELECT SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE SALESTABLE.RECID = MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
MARKUPTRANS.MARKUPCODE = 'Freight') AS FreightValue,
(SELECT SUM(INVENTSUM.POSTEDVALUE)
FROM INVENTSUM
WHERE SALESLINE.INVENTDIMID = INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
FROM SALESTABLE INNER JOIN
SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID ='acm')
AND (SALESTABLE.SALESSTATUS = 1) AND CREATEDATE < @.CUTOFFDATE
You may have to make this a stored procedure if it will not work on the IDE
for reporting Services.
Rodney Landrum -Author, "Pro SQL Server Reporting Services" (Apress)
http://www.apress.com
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:211E0BCB-A461-485A-B888-333A33E33162@.microsoft.com...
> If I use GROUP BY, will that not require aggregation of the results of the
> query?
> Perhaps it would be helpful to see the existing query:
> SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS
> QTY,
> (SELECT SUM(MARKUPTRANS.VALUE)
> FROM MARKUPTRANS
> WHERE SALESTABLE.RECID => MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
> MARKUPTRANS.MARKUPCODE => 'Freight') AS FreightValue,
> (SELECT SUM(INVENTSUM.POSTEDVALUE)
> FROM INVENTSUM
> WHERE SALESLINE.INVENTDIMID => INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
> FROM SALESTABLE INNER JOIN
> SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
> WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID => 'acm')
> AND (SALESTABLE.SALESSTATUS = 1) >
> That is table one. I want to limit on a column called CREATEDDATE.
> The other query looks like:
> SELECT CUTOFFDATE from CONFIGDATA
> "Rodney Landrum" wrote:
>> It would be possible to use a GROUP BY / HAVING in the SELECT if that was
>> a
>> possibility for you. I am sure there are much better ways, but this is
>> off
>> the cuff:
>> Select
>> MyDate, Field1, Field2 from table1
>> Group by
>> MyDate,Field1,Field2
>> HAVING MyDate < (Select LookUpDate from Table2)
>> You could always do it in a stored procedure and call in the value of
>> Table2
>> into a variable @.LokUpDate that yo could compare. I assume that the
>> LookUpDate in my example to match your description changes on a regular
>> basis?
>> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
>> http://www.apress.com
>> "Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
>> message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
>> >I have a query on a table. This table has a datetime column.
>> >
>> > I have another table. This table also has a datetime column. It has
>> > only
>> > one
>> > row. It basically to store some simple configuration data.
>> >
>> > There is no relation between the tables.
>> >
>> > I want to be able to limit the entries returned by the query on the
>> > first
>> > table by only returning rows that have a date that is less than the
>> > date
>> > in
>> > the row in the second table.
>> >
>> > I'm actually having trouble getting this to work properly. I've tried
>> > subqueries, passing it through the report as a parameter, etc... None
>> > are
>> > working.
>> >
>> > How should i attack this?
>>|||I might be missing something but this looks like the following to me:
select a.* from maintable a, configtable b where a.datetimecolumn <
b.datetimecolumn
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
> I have a query on a table. This table has a datetime column.
> I have another table. This table also has a datetime column. It has only
one
> row. It basically to store some simple configuration data.
> There is no relation between the tables.
> I want to be able to limit the entries returned by the query on the first
> table by only returning rows that have a date that is less than the date
in
> the row in the second table.
> I'm actually having trouble getting this to work properly. I've tried
> subqueries, passing it through the report as a parameter, etc... None are
> working.
> How should i attack this?

Getting a file name from Query Analyzer

Hi all,

im trying to write a stored procedure that will basically browse a folder and get me the first file that it sees. Is there any way that I can do this in TSQL or using CLR in C#? I was thinking something along the lines of using the dos dir command and triyng to pipe it into a variable, not sure how to go about doing this. Any suggestions?

dir /b ...gives me the bare file names, but it lists all the files in the folder, any way that i can just get the first file ( i dont really care what file).

create table #filelist
(
files varchar(500)
)

truncate table #filelist

insert #filelist
EXEC xp_cmdshell 'dir c:*.* /b'

select top 1 * from #filelist

|||

This might help out:

http://stevekass.com/blog/wp-content/Folders/sql/TextDriver.htm

You can use TOP 1 to get just one file name.

Steve Kass

Drew University

http://www.stevekass.com

YoungEngineer@.discussions.microsoft.com wrote:

> Hi all,

>

> im trying to write a stored procedure that will basically browse a

> folder and get me the first file that it sees. Is there any way that I

> can do this in TSQL or using CLR in C#? I was thinking something along

> the lines of using the dos dir command and triyng to pipe it into a

> variable, not sure how to go about doing this. Any suggestions?

>

> dir /b ...gives me the bare file names, but it lists all the files in

> the folder, any way that i can just get the first file ( i dont really

> care what file).

>

>

>

>

getting a constraint value from query in a trigger?

say i have a query like:

UPDATE table SET status = 1 WHERE id = 1000

if i have a trigger on that table, is there anyway i can get the id ?

i know i can get the status by SELECT status FROM Inserted, but anyway to get what the id is? or would i just have to update the id as well?

thankshmm, looks like i can't even get the id if i try to update since it's an identity column|||How about:SELECT id FROM inserted-PatP|||inserted AND deleted are full blown copies of the table that's affected for the rows that are be modified or added.

You [Id] should be there

What are you trying to do?|||oops, actually i just realized SELECT id from inserted works fine even though id isn't updated

thanks

getting 100 rows with values from 1 - 100

I am trying to right a query that will return 100 rows, of one column,
and the data being 1 to 100
i can do this with a cursor ok, i can also do it with a select INTO a
tempoary table with IDENTITY
however is there any way i can do this without a temporary table or
cursor
KarlCheck out:
http://msdn.microsoft.com/library/d...r />
p03k1.asp
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<klumsy@.xtra.co.nz> wrote in message
news:1115860445.064283.61420@.f14g2000cwb.googlegroups.com...
I am trying to right a query that will return 100 rows, of one column,
and the data being 1 to 100
i can do this with a cursor ok, i can also do it with a select INTO a
tempoary table with IDENTITY
however is there any way i can do this without a temporary table or
cursor
Karl

Gettin data fromdifferent database

Hi

My query syntax is such

select 'INSERT INTO UserCourse(Start_Date, User_ID) values
(Course_Date + ''', ''' + rtrim(convert(varchar(20),AppUser.ID)) + ''');'
FROM DB1..EMPLOYEE_TRAINING, DB2..User
WHERE DB2..User.Employee_Number = DB1..EMPLOYEE_TRAINING.EMPLOYEE_NO

I run this query in the Query Analyser on the database DB1 and get the following error

The column prefix 'User' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'DB1..User' does not match with a table name or alias name used in the query.

What I need is to get the ID and Date from differnt tables in different databases
Any clues or suggestions
NimishaTry changing:

FROM DB1..EMPLOYEE_TRAINING, DB2..User

WHERE DB2..User.Employee_Number = DB1..EMPLOYEE_TRAINING.EMPLOYEE_NO

to

FROM [DB1].dbo.EMPLOYEE_TRAINING, DB2..User

WHERE [DB2].dbo.User.Employee_Number = [DB1].dbo.EMPLOYEE_TRAINING.EMPLOYEE_NO

HTH

Ken|||Hi,
Try this
In clause replace the DB2..User
with servername.databasename.userid.tablename
and in where clause simply use the column name.

e.g

select *
from a, ndokech1.echi.dbo.tx_echi c
where a.myid = c.myid

i think it will work..

Cheers
Gola munjal

Originally posted by Nimisha
Hi

My query syntax is such

select 'INSERT INTO UserCourse(Start_Date, User_ID) values
(Course_Date + ''', ''' + rtrim(convert(varchar(20),AppUser.ID)) + ''');'
FROM DB1..EMPLOYEE_TRAINING, DB2..User
WHERE DB2..User.Employee_Number = DB1..EMPLOYEE_TRAINING.EMPLOYEE_NO

I run this query in the Query Analyser on the database DB1 and get the following error

The column prefix 'User' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'DB1..User' does not match with a table name or alias name used in the query.

What I need is to get the ID and Date from differnt tables in different databases
Any clues or suggestions
Nimisha|||Sorry i thought u asked for getting data from different servers.
i think the problem is with table name user, bcause user is a keyword in sql.
give user table name with in [] this.
select *
from fscms..[user] ,fscms_backup..[user]
where fscms..[user].Employee_Number = fscms_backup..[user].Employee_Number

i tried this ..its working ..

Cheers
gola

Originally posted by GA_KEN
Try changing:

FROM DB1..EMPLOYEE_TRAINING, DB2..User

WHERE DB2..User.Employee_Number = DB1..EMPLOYEE_TRAINING.EMPLOYEE_NO

to

FROM [DB1].dbo.EMPLOYEE_TRAINING, DB2..User

WHERE [DB2].dbo.User.Employee_Number = [DB1].dbo.EMPLOYEE_TRAINING.EMPLOYEE_NO

HTH

Ken|||Thanx guys for all the input apprecaite it.
Nimisha

GetEnvironmentVariable

Hello,
I could not use this in the criteria filed of my query.
= System.Environment.GetEnvironmentVariable(MYID)
It gives invalid object name
System.Environment.GetEnvironmentVariable
Couldn't I use it in where clause?
Thanks,
Jim.You'll need to use it in a query expression as follows:
="select * From sysservers where srvname = '" &
System.Environment.GetEnvironmentVariable("COMPUTERNAME") & "'"
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
news:2cb3a01c46905$6b8d1ea0$a301280a@.phx.gbl...
> Hello,
> I could not use this in the criteria filed of my query.
> = System.Environment.GetEnvironmentVariable(MYID)
> It gives invalid object name
> System.Environment.GetEnvironmentVariable
> Couldn't I use it in where clause?
> Thanks,
> Jim.
>|||Thanks Ravi,
I am developening the query in the Data tab of report
design. Can you tell me exactly where I type this kind of
expression? I am quite new in this.
Thanks,
Jim.
>--Original Message--
>You'll need to use it in a query expression as follows:
>="select * From sysservers where srvname = '" &
>System.Environment.GetEnvironmentVariable("COMPUTERNAME")
& "'"
>--
>Ravi Mumulla (Microsoft)
>SQL Server Reporting Services
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
message
>news:2cb3a01c46905$6b8d1ea0$a301280a@.phx.gbl...
>> Hello,
>> I could not use this in the criteria filed of my query.
>> = System.Environment.GetEnvironmentVariable(MYID)
>> It gives invalid object name
>> System.Environment.GetEnvironmentVariable
>> Couldn't I use it in where clause?
>> Thanks,
>> Jim.
>
>.
>|||Sorry I do not get picture in here, if you are talking
abouy typing it in SQL pan in Data tab, I am doing that
but it does not seem it is working.
>--Original Message--
>You'll need to type this expression in the Data pane in
Report Designer UI.
>Please see the attached image.
>--
>Ravi Mumulla (Microsoft)
>SQL Server Reporting Services
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
message
>news:2cd0601c4691f$3cccbd90$a301280a@.phx.gbl...
>> Thanks Ravi,
>> I am developening the query in the Data tab of report
>> design. Can you tell me exactly where I type this kind
of
>> expression? I am quite new in this.
>> Thanks,
>> Jim.
>> >--Original Message--
>> >You'll need to use it in a query expression as follows:
>> >
>> >="select * From sysservers where srvname = '" &
>> >System.Environment.GetEnvironmentVariable
("COMPUTERNAME")
>> & "'"
>> >
>> >--
>> >Ravi Mumulla (Microsoft)
>> >SQL Server Reporting Services
>> >
>> >This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2cb3a01c46905$6b8d1ea0$a301280a@.phx.gbl...
>> >> Hello,
>> >> I could not use this in the criteria filed of my
query.
>> >> = System.Environment.GetEnvironmentVariable(MYID)
>> >> It gives invalid object name
>> >> System.Environment.GetEnvironmentVariable
>> >> Couldn't I use it in where clause?
>> >> Thanks,
>> >> Jim.
>> >>
>> >
>> >
>> >.
>> >
>
>|||You will not get the fields in the fields window automatically. So you'd
first have to type in static SQL and run it (which will generate the fields
list in the fields window) and then replace the static SQL with the query
expression.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
news:2c4e501c4692f$36241410$a401280a@.phx.gbl...
> Sorry I do not get picture in here, if you are talking
> abouy typing it in SQL pan in Data tab, I am doing that
> but it does not seem it is working.
> >--Original Message--
> >You'll need to type this expression in the Data pane in
> Report Designer UI.
> >Please see the attached image.
> >
> >--
> >Ravi Mumulla (Microsoft)
> >SQL Server Reporting Services
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2cd0601c4691f$3cccbd90$a301280a@.phx.gbl...
> >> Thanks Ravi,
> >> I am developening the query in the Data tab of report
> >> design. Can you tell me exactly where I type this kind
> of
> >> expression? I am quite new in this.
> >> Thanks,
> >> Jim.
> >>
> >> >--Original Message--
> >> >You'll need to use it in a query expression as follows:
> >> >
> >> >="select * From sysservers where srvname = '" &
> >> >System.Environment.GetEnvironmentVariable
> ("COMPUTERNAME")
> >> & "'"
> >> >
> >> >--
> >> >Ravi Mumulla (Microsoft)
> >> >SQL Server Reporting Services
> >> >
> >> >This posting is provided "AS IS" with no warranties,
> and
> >> confers no rights.
> >> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:2cb3a01c46905$6b8d1ea0$a301280a@.phx.gbl...
> >> >> Hello,
> >> >> I could not use this in the criteria filed of my
> query.
> >> >> = System.Environment.GetEnvironmentVariable(MYID)
> >> >> It gives invalid object name
> >> >> System.Environment.GetEnvironmentVariable
> >> >> Couldn't I use it in where clause?
> >> >> Thanks,
> >> >> Jim.
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >sql

GETDATE() Query Giving Me Trouble

Hello all,

I'm trying to put together a query that will give me all records in my db where the date in the "NextDate" column equals the date that the query is run. Seems easy...so I put together the following query:

SELECT EventNo, NextDate, TrainersLastName, ItemSerialNo, ManufacturerName, ItemModel, ScheduledMaintenance, RBDate, Daily, Weekly, Monthly, Yearly
FROM Maintenance
WHERE (NextDate = GETDATE()) AND (RBDate = 'true') OR
(Daily = 'true') OR
(Weekly = 'true') OR
(Monthly = 'true') OR
(Yearly = 'true')
ORDER BY EventNo

I'm not getting any records returning even though there are records in the db that match the criteria. Any ideas on how I can solve this?

Thanks in advance for any help!

Tony

Date comparison's take the full date and time into consideration.

If you're looking to match only on MDY, then try

Code Snippet

datediff(dd, NextDate, GETDATE())=0

|||

Yes, datediff will work; however, it will not hit any potential indexes because of the operator on the nextDate column. Again, I admit that in this case indexes might not be relevant. Nonetheless, I will still prefer to at least have a chance at hitting an index. I would prefer something more like:

Code Snippet

where nextDate >= dateadd(day, datediff (day, 0, getdate()), 0)
and nextDate < dateadd(day, datediff (day, 0, getdate()), 0) + 1

I went back to grab a bottle of water and I realized that with all of those ORs it is probably not going to hit an index anyway. Please ignore my previous baloney.

( Thanks, Dale; yes, the water is ice cold. It is hot here too. )

|||

LOL

But, you do have a good point Kent.

However, without more info the point might be moot.

datediff(...) is the simplest solution; other solutions would need to take indexes, table size, etc. into consideration.

Hope it's ice cold water....it's 95 here today

|||

Thanks very much guys for the replies!

DaleJ,

The DATEDIFF solution worked great. I didn't realize that GETDATE() compared time also. No wonder nothing was matching. For my own clarification/education, could you explain a little bit regarding the DATEDIFF statement. Am I correct that the statement is specifying the formatted date (dd) difference between "NextDate" and GETDATE() is = 0 (therefore being the same date)?

Thanks again very much for the help!

Tony

|||

Hey Tony

datediff gets the number of units (operand 1, dd) between date1 (NextDate) and date2 (getdate()).

The =0 checks that that difference is 0, meaning that it's the same date

|||Got it. Thanks again very much!

GETDATE() Query Giving Me Trouble

Hello all,

I'm trying to put together a query that will give me all records in my db where the date in the "NextDate" column equals the date that the query is run. Seems easy...so I put together the following query:

SELECT EventNo, NextDate, TrainersLastName, ItemSerialNo, ManufacturerName, ItemModel, ScheduledMaintenance, RBDate, Daily, Weekly, Monthly, Yearly
FROM Maintenance
WHERE (NextDate = GETDATE()) AND (RBDate = 'true') OR
(Daily = 'true') OR
(Weekly = 'true') OR
(Monthly = 'true') OR
(Yearly = 'true')
ORDER BY EventNo

I'm not getting any records returning even though there are records in the db that match the criteria. Any ideas on how I can solve this?

Thanks in advance for any help!

Tony

Date comparison's take the full date and time into consideration.

If you're looking to match only on MDY, then try

Code Snippet

datediff(dd, NextDate, GETDATE())=0

|||

Yes, datediff will work; however, it will not hit any potential indexes because of the operator on the nextDate column. Again, I admit that in this case indexes might not be relevant. Nonetheless, I will still prefer to at least have a chance at hitting an index. I would prefer something more like:

Code Snippet

where nextDate >= dateadd(day, datediff (day, 0, getdate()), 0)
and nextDate < dateadd(day, datediff (day, 0, getdate()), 0) + 1

I went back to grab a bottle of water and I realized that with all of those ORs it is probably not going to hit an index anyway. Please ignore my previous baloney.

( Thanks, Dale; yes, the water is ice cold. It is hot here too. )

|||

LOL

But, you do have a good point Kent.

However, without more info the point might be moot.

datediff(...) is the simplest solution; other solutions would need to take indexes, table size, etc. into consideration.

Hope it's ice cold water....it's 95 here today

|||

Thanks very much guys for the replies!

DaleJ,

The DATEDIFF solution worked great. I didn't realize that GETDATE() compared time also. No wonder nothing was matching. For my own clarification/education, could you explain a little bit regarding the DATEDIFF statement. Am I correct that the statement is specifying the formatted date (dd) difference between "NextDate" and GETDATE() is = 0 (therefore being the same date)?

Thanks again very much for the help!

Tony

|||

Hey Tony

datediff gets the number of units (operand 1, dd) between date1 (NextDate) and date2 (getdate()).

The =0 checks that that difference is 0, meaning that it's the same date

|||Got it. Thanks again very much!

GETDATE() Query Giving Me Trouble

Hello all,

I'm trying to put together a query that will give me all records in my db where the date in the "NextDate" column equals the date that the query is run. Seems easy...so I put together the following query:

SELECT EventNo, NextDate, TrainersLastName, ItemSerialNo, ManufacturerName, ItemModel, ScheduledMaintenance, RBDate, Daily, Weekly, Monthly, Yearly
FROM Maintenance
WHERE (NextDate = GETDATE()) AND (RBDate = 'true') OR
(Daily = 'true') OR
(Weekly = 'true') OR
(Monthly = 'true') OR
(Yearly = 'true')
ORDER BY EventNo

I'm not getting any records returning even though there are records in the db that match the criteria. Any ideas on how I can solve this?

Thanks in advance for any help!

Tony

Date comparison's take the full date and time into consideration.

If you're looking to match only on MDY, then try

Code Snippet

datediff(dd, NextDate, GETDATE())=0

|||

Yes, datediff will work; however, it will not hit any potential indexes because of the operator on the nextDate column. Again, I admit that in this case indexes might not be relevant. Nonetheless, I will still prefer to at least have a chance at hitting an index. I would prefer something more like:

Code Snippet

where nextDate >= dateadd(day, datediff (day, 0, getdate()), 0)
and nextDate < dateadd(day, datediff (day, 0, getdate()), 0) + 1

I went back to grab a bottle of water and I realized that with all of those ORs it is probably not going to hit an index anyway. Please ignore my previous baloney.

( Thanks, Dale; yes, the water is ice cold. It is hot here too. )

|||

LOL

But, you do have a good point Kent.

However, without more info the point might be moot.

datediff(...) is the simplest solution; other solutions would need to take indexes, table size, etc. into consideration.

Hope it's ice cold water....it's 95 here today

|||

Thanks very much guys for the replies!

DaleJ,

The DATEDIFF solution worked great. I didn't realize that GETDATE() compared time also. No wonder nothing was matching. For my own clarification/education, could you explain a little bit regarding the DATEDIFF statement. Am I correct that the statement is specifying the formatted date (dd) difference between "NextDate" and GETDATE() is = 0 (therefore being the same date)?

Thanks again very much for the help!

Tony

|||

Hey Tony

datediff gets the number of units (operand 1, dd) between date1 (NextDate) and date2 (getdate()).

The =0 checks that that difference is 0, meaning that it's the same date

|||Got it. Thanks again very much!

GETDATE() Query Giving Me Trouble

Hello all,

I'm trying to put together a query that will give me all records in my db where the date in the "NextDate" column equals the date that the query is run. Seems easy...so I put together the following query:

SELECT EventNo, NextDate, TrainersLastName, ItemSerialNo, ManufacturerName, ItemModel, ScheduledMaintenance, RBDate, Daily, Weekly, Monthly, Yearly
FROM Maintenance
WHERE (NextDate = GETDATE()) AND (RBDate = 'true') OR
(Daily = 'true') OR
(Weekly = 'true') OR
(Monthly = 'true') OR
(Yearly = 'true')
ORDER BY EventNo

I'm not getting any records returning even though there are records in the db that match the criteria. Any ideas on how I can solve this?

Thanks in advance for any help!

Tony

Date comparison's take the full date and time into consideration.

If you're looking to match only on MDY, then try

Code Snippet

datediff(dd, NextDate, GETDATE())=0

|||

Yes, datediff will work; however, it will not hit any potential indexes because of the operator on the nextDate column. Again, I admit that in this case indexes might not be relevant. Nonetheless, I will still prefer to at least have a chance at hitting an index. I would prefer something more like:

Code Snippet

where nextDate >= dateadd(day, datediff (day, 0, getdate()), 0)
and nextDate < dateadd(day, datediff (day, 0, getdate()), 0) + 1

I went back to grab a bottle of water and I realized that with all of those ORs it is probably not going to hit an index anyway. Please ignore my previous baloney.

( Thanks, Dale; yes, the water is ice cold. It is hot here too. )

|||

LOL

But, you do have a good point Kent.

However, without more info the point might be moot.

datediff(...) is the simplest solution; other solutions would need to take indexes, table size, etc. into consideration.

Hope it's ice cold water....it's 95 here today

|||

Thanks very much guys for the replies!

DaleJ,

The DATEDIFF solution worked great. I didn't realize that GETDATE() compared time also. No wonder nothing was matching. For my own clarification/education, could you explain a little bit regarding the DATEDIFF statement. Am I correct that the statement is specifying the formatted date (dd) difference between "NextDate" and GETDATE() is = 0 (therefore being the same date)?

Thanks again very much for the help!

Tony

|||

Hey Tony

datediff gets the number of units (operand 1, dd) between date1 (NextDate) and date2 (getdate()).

The =0 checks that that difference is 0, meaning that it's the same date

|||Got it. Thanks again very much!sql

GetDate not working

Hi,
I have a very simple query as follows. When I run, it returns no records. I
know there are records that should be in th result. If I insert today's
date instead of getdate() I get all the records for today's date. Getdate()
works if I use > or < instead of =. Any idea why is this behavior? Or is
there another way of accomplishing this.
Select COURSE_NBR as ItemValue
FROM ED_COURSE_CL_1
Where CLASS_DATE = GETDATE()Did you look at SELECT GETDATE() ? It is "working" just fine. Does it look
like just a date? Notice how it has HH:MM:SS.mmm as well. How many rows do
you think match the exact point in time when you run the query? The way to
do this is to use a range query, as you have already discovered. >= {date}
AND < {date + 1} ...
A
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:C0202406-5C2E-481C-B605-A3206B776161@.microsoft.com...
> Hi,
> I have a very simple query as follows. When I run, it returns no records.
> I
> know there are records that should be in th result. If I insert today's
> date instead of getdate() I get all the records for today's date.
> Getdate()
> works if I use > or < instead of =. Any idea why is this behavior? Or is
> there another way of accomplishing this.
> Select COURSE_NBR as ItemValue
> FROM ED_COURSE_CL_1
> Where CLASS_DATE = GETDATE()
>|||It is because datetime include both a date and a time portion. See
http://www.karaszi.com/SQLServer/info_datetime.asp
http://www.karaszi.com/SQLServer/info_datetime.asp#Searching
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:C0202406-5C2E-481C-B605-A3206B776161@.microsoft.com...
> Hi,
> I have a very simple query as follows. When I run, it returns no records. I
> know there are records that should be in th result. If I insert today's
> date instead of getdate() I get all the records for today's date. Getdate()
> works if I use > or < instead of =. Any idea why is this behavior? Or is
> there another way of accomplishing this.
> Select COURSE_NBR as ItemValue
> FROM ED_COURSE_CL_1
> Where CLASS_DATE = GETDATE()
>|||Aaron,
Select getdate() is working good and it returns todays date. For today's
date in my query should return one record, which I can verify it by inserting
today's date instead of getdate(). How can I build a range query to only get
records with today's date?
Thanks
"Aaron Bertrand [SQL Server MVP]" wrote:
> Did you look at SELECT GETDATE() ? It is "working" just fine. Does it look
> like just a date? Notice how it has HH:MM:SS.mmm as well. How many rows do
> you think match the exact point in time when you run the query? The way to
> do this is to use a range query, as you have already discovered. >= {date}
> AND < {date + 1} ...
> A
>
> "Shan" <Shan@.discussions.microsoft.com> wrote in message
> news:C0202406-5C2E-481C-B605-A3206B776161@.microsoft.com...
> > Hi,
> > I have a very simple query as follows. When I run, it returns no records.
> > I
> > know there are records that should be in th result. If I insert today's
> > date instead of getdate() I get all the records for today's date.
> > Getdate()
> > works if I use > or < instead of =. Any idea why is this behavior? Or is
> > there another way of accomplishing this.
> >
> > Select COURSE_NBR as ItemValue
> > FROM ED_COURSE_CL_1
> > Where CLASS_DATE = GETDATE()
> >
> >
>
>|||> Select getdate() is working good and it returns todays date.
Correction : it returns today's date AND TIME.
> How can I build a range query to only get
> records with today's date?
DECLARE @.today SMALLDATETIME;
SET @.today = DATEDIFF(DAY, 0, GETDATE());
SELECT
...
WHERE DateColumn >= @.today
AND DateColumn < (@.today + 1);|||Thanks Aaron it's working great.
Cheers!!!
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Select getdate() is working good and it returns todays date.
> Correction : it returns today's date AND TIME.
> > How can I build a range query to only get
> > records with today's date?
> DECLARE @.today SMALLDATETIME;
> SET @.today = DATEDIFF(DAY, 0, GETDATE());
> SELECT
> ...
> WHERE DateColumn >= @.today
> AND DateColumn < (@.today + 1);
>
>
>

getdate - only want the date

when I am doing a query I want to display just the date.
when I use the getdate function it returns the date and
time from each of the fields...is there a way to just
display the date from the fields and leave out the time?Use CONVERT or CAST. You can find some examples in books online.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:0b7601c39e63$2293d350$a001280a@.phx.gbl...
> when I am doing a query I want to display just the date.
> when I use the getdate function it returns the date and
> time from each of the fields...is there a way to just
> display the date from the fields and leave out the time?|||You can use following to display only date.
select convert(char(11),getdate())
If you want to display in different format , then extract
dd, mm, yy from getdate and concatinate it.
Suryakant
>--Original Message--
>when I am doing a query I want to display just the date.
>when I use the getdate function it returns the date and
>time from each of the fields...is there a way to just
>display the date from the fields and leave out the time?
>.
>|||The only way is to convert it to a string. There are several formats so you
should check out CONVERT() in BooksOnLine for the one your after.
--
Andrew J. Kelly
SQL Server MVP
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:0b7601c39e63$2293d350$a001280a@.phx.gbl...
> when I am doing a query I want to display just the date.
> when I use the getdate function it returns the date and
> time from each of the fields...is there a way to just
> display the date from the fields and leave out the time?|||worked fine thank you

Get values from database query

I have written the following lines

myConnection =New MySqlConnection("server=" + dbServer +"; user id=" + dbUserID +"; password=" + dbPassword +"; database=" + dbName +"; pooling=false;")strSQL ="SELECT * FROM user where type=1;"

user table has name, tel, addr, id, type fields

I would like to know how to use a string array to store the name in the result of strSQL?

Thank you

Hi thtang

You can use fllowing code:

SqlCommand cmd = new SqlCommand(myConnection ,strSQL );

SqlDataReader dr = cmd.ExecuteReader();

while(dr.Read())

{

stringList.Add(dr.GetString(0));

}

Get value from query to update another another table

I have a query that simply slelcts the min value of a specified field
from one table, I want to take that value to update a field in
annother table, just can not figure it out.(rzito@.si.rr.com) writes:

Quote:

Originally Posted by

I have a query that simply slelcts the min value of a specified field
from one table, I want to take that value to update a field in
annother table, just can not figure it out.

UPDATE tbl
SET col = (SELECT MIN(somecol) FROM othertbl)
WHERE ...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

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

Get uniqueness of a column from the system tables or information_schema

I'm trying to write a query which, from a given table name, will
produce a list of column names with an indicator as to whether it is
unique. By unique, I mean it a) is the column in a single-column
primary key, b) is the column in a single-column unique constraint, or
c) is the column in single-column unique index.
So, for this DDL,
-- CODE BEGINS
create table t1 (
c1 int not null primary key,
c2 int not null unique,
c3 int not null,
c4 int not null
)
create unique index ix1 on t1 (c3)
-- drop table t1
-- CODE ENDS
I'd like a query that will produce something like this output
c1 yes
c2 yes
c3 yes
c4 no
I've spent a few hours with sysobjects, sysindexes, sysconstraints, and
information_schema, but I'm getting nowhere. Anyone have any hints?
Thomas BergI forgot to say: I'm using SQL Server 2000 SP4.|||Hello, Thomas
This query returns the desired result:
SELECT name,
CASE WHEN EXISTS (
SELECT * FROM sysindexkeys k
INNER JOIN sysindexes i
ON k.id=i.id AND k.indid=i.indid
WHERE k.id=c.id AND k.colid=c.colid
AND INDEXPROPERTY(i.id,i.name,'IsUnique')=1
AND NOT EXISTS (
SELECT * FROM sysindexkeys k2
WHERE k.id=k2.id AND k.indid=k2.indid
AND k.keyno<>k2.keyno
)
) THEN 'yes' ELSE 'no' END AS IsUnique
FROM syscolumns c WHERE id=OBJECT_ID('t1')
Note that it's sufficient to search only for unique indexes, because
primary keys and unique keys are always enforced by creating a unique
index with the same name on the specified columns.
For a more thorough testing of the query, I added the following:
create unique index ix2 on t1 (c4,c3)
create index ix3 on t1 (c4)
Razvan|||tbergNoSpamPlease@.insight-system.co.jp a crit :
> I'm trying to write a query which, from a given table name, will
> produce a list of column names with an indicator as to whether it is
> unique. By unique, I mean it a) is the column in a single-column
> primary key, b) is the column in a single-column unique constraint, or
> c) is the column in single-column unique index.
> So, for this DDL,
> -- CODE BEGINS
> create table t1 (
> c1 int not null primary key,
> c2 int not null unique,
> c3 int not null,
> c4 int not null
> )
> create unique index ix1 on t1 (c3)
> -- drop table t1
> -- CODE ENDS
> I'd like a query that will produce something like this output
> c1 yes
> c2 yes
> c3 yes
> c4 no
> I've spent a few hours with sysobjects, sysindexes, sysconstraints, and
> information_schema, but I'm getting nowhere. Anyone have any hints?
> Thomas Berg
>
Here is a very general query wich give you all informations about
indexes with columns and uniqueness
SELECT
u.name AS IXD_SCHEMA_NAME,
o.name AS IXD_TABLE_NAME,
i.name AS IXD_INDEX_NAME,
CONSTRAINT_TYPE AS IXD_CONSTRAINT_TYPE,
CASE
WHEN i.indid = 0 THEN 'TABLE'
WHEN i.indid = 1 THEN 'CLUSTER'
WHEN i.indid BETWEEN 2 AND 254 THEN 'HEAP'
WHEN i.indid = 255 THEN 'TXTEIMAGE'
END AS IXD_INDEX_TYPE,
INDEXPROPERTY(o.id, i.name, 'IsUnique') AS IXD_IS_UNIQUE,
INDEXPROPERTY(o.id, i.name, 'IndexFillFactor') AS IXD_FILL_FACTOR,
c.name AS IXD_COL_NAME,
DATA_TYPE + '('+
CAST(COALESCE(CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION) AS
VARCHAR(16))
+ COALESCE(', '+CAST(NULLIF(NUMERIC_SCALE, 0) AS
VARCHAR(16)) , '') +')' AS IXD_COL_TYPE,
k.keyno AS IXD_COL_IDX_ORDER,
CASE
WHEN INDEXKEY_PROPERTY (o.id , i.indid , k.colid ,
N'isdescending' ) = 0 THEN 'ASC'
WHEN INDEXKEY_PROPERTY (o.id , i.indid , k.colid ,
N'isdescending' ) = 1 THEN 'DESC'
WHEN INDEXKEY_PROPERTY (o.id , i.indid , k.colid ,
N'isdescending' ) IS NULL THEN ''
END AS IXD_COL_DATA_ORDER,
INDEXPROPERTY(o.id, i.name, 'IsRowLockDisallowed') AS
IXD_ROW_LOCK_DISALLOWED,
INDEXPROPERTY(o.id, i.name, 'IsPageLockDisallowed') AS
IXD_PAGE_LOCK_DISALLOWED
FROM dbo.sysindexes i
INNER JOIN dbo.sysobjects o
ON i.id = o.id
INNER JOIN dbo.sysusers u
ON o.uid = u.uid
INNER JOIN dbo.sysindexkeys k
ON o.id = k.id
and i.indid = k.indid
INNER JOIN dbo.syscolumns c
ON k.colid = c.colid
and o.id = c.id
INNER JOIN INFORMATION_SCHEMA.COLUMNS ISC
ON u.name = ISC.TABLE_SCHEMA
AND o.name = ISC.TABLE_NAME
AND c.name = ISC.COLUMN_NAME
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TCT
ON u.name = TCT.CONSTRAINT_SCHEMA
AND i.name = TCT.CONSTRAINT_NAME
WHERE i.status & 64 <> 64 -- sauf les index "stat"
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||You guys are brilliant. Thanks.

Get Today's Records

Hello, I have a query that needs to run daily and only collect records for
that day's activity. The table has a date/time field called TranDateSold.
Could someone please advise the proper syntax for the following logic:
SELECT * FROM tablename
WHERE TranDateSold (is today only)
I experimented with GETDATE() but it seemed to want to match today's date
and time to the hour and minute. Any ideas would be most appreciated.Hi,
Strip the time portion from the date returned by getdate() function before
comparison. Something like this:
select *
from tablename
where trandatesold = convert(vachar(8), getdate(), 112)
This assumes that the time is already zero-ed in trandatesold column.
hth,
Dean
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:0BA6C334-70D4-4637-8528-1B838763F5DD@.microsoft.com...
> Hello, I have a query that needs to run daily and only collect records for
> that day's activity. The table has a date/time field called TranDateSold.
> Could someone please advise the proper syntax for the following logic:
> SELECT * FROM tablename
> WHERE TranDateSold (is today only)
> I experimented with GETDATE() but it seemed to want to match today's date
> and time to the hour and minute. Any ideas would be most appreciated.|||Try this
SELECT * FROM tablename
WHERE TranDateSold = DATEADD(d, DATEDIFF(d, 0, GETDATE())+0, 0)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||> Hello, I have a query that needs to run daily and only collect records for
> that day's activity. The table has a date/time field called TranDateSold.
If there can be nothing in the future:
DECLARE @.dt SMALLDATETIME
SET @.dt = 0 + DATEDIFF(DAY, 0, GETDATE());
SELECT <col_list> FROM tablename
WHERE TranDateSold >= @.dt;
If there may be future-dated rows:
SELECT <col_list> FROM tablename
WHERE TranDateSold >= @.dt
AND TranDateSold < @.dt + 1;
A|||Oops, I goofed the = should be >=|||This will only work if TranDateSold is intentionally stored with no time
value. Otherwise you will be asking for rows where '2006-04-21 13:26' =
'2006-04-21 00:00';
If you have to do the convert on the left-hand side to get rid of the time
component, you've just wiped out any chance of using an index.
http://www.aspfaq.com/2280
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145649232.038005.231380@.g10g2000cwb.googlegroups.com...
> Try this
> SELECT * FROM tablename
> WHERE TranDateSold = DATEADD(d, DATEDIFF(d, 0, GETDATE())+0, 0)
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||Thanks to everyone for your posts. The convert function is what I needed fo
r
my vendor, and the DECLARE stmt gave me all records after midnight today.
Have a nice wend, Pancho.
"Aaron Bertrand [SQL Server MVP]" wrote:

> If there can be nothing in the future:
> DECLARE @.dt SMALLDATETIME
> SET @.dt = 0 + DATEDIFF(DAY, 0, GETDATE());
> SELECT <col_list> FROM tablename
> WHERE TranDateSold >= @.dt;
> If there may be future-dated rows:
> SELECT <col_list> FROM tablename
> WHERE TranDateSold >= @.dt
> AND TranDateSold < @.dt + 1;
> A
>
>

Get the XML out of sql server 2005 in c#

Hi,
is there a way to get the result of select query which uses or xml
auto, elements to c# ?
for ex, i have a query like
"SELECT * from dbo.[user] where userid = @.UserID for xml auto,
elements"
and i want result of this query back to c# function, how can i do it?
Pls reply as soon as possible.
Cheers
Hi
You may find something at
http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp#5
http://sqlxml.org/faqs.aspx?1 or
http://support.microsoft.com/kb/q271620/
John
"steven" wrote:

> Hi,
> is there a way to get the result of select query which uses or xml
> auto, elements to c# ?
> for ex, i have a query like
> "SELECT * from dbo.[user] where userid = @.UserID for xml auto,
> elements"
> and i want result of this query back to c# function, how can i do it?
> Pls reply as soon as possible.
> Cheers
>

Get the XML out of sql server 2005 in c#

Hi,
is there a way to get the result of select query which uses or xml
auto, elements to c# ?
for ex, i have a query like
"SELECT * from dbo.[user] where userid = @.UserID for xml auto,
elements"
and i want result of this query back to c# function, how can i do it'
Pls reply as soon as possible.
CheersHi
You may find something at
http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp#5
http://sqlxml.org/faqs.aspx?1 or
http://support.microsoft.com/kb/q271620/
John
"steven" wrote:
> Hi,
> is there a way to get the result of select query which uses or xml
> auto, elements to c# ?
> for ex, i have a query like
> "SELECT * from dbo.[user] where userid = @.UserID for xml auto,
> elements"
> and i want result of this query back to c# function, how can i do it'
> Pls reply as soon as possible.
> Cheers
>

Get the XML out of sql server 2005 in c#

Hi,
is there a way to get the result of select query which uses or xml
auto, elements to c# ?
for ex, i have a query like
"SELECT * from dbo.[user] where userid = @.UserID for xml auto,
elements"
and i want result of this query back to c# function, how can i do it'
Pls reply as soon as possible.
CheersHi
You may find something at
http://www.perfectxml.com/Articles/...ortSQLXML.asp#5
http://sqlxml.org/faqs.aspx?1 or
http://support.microsoft.com/kb/q271620/
John
"steven" wrote:

> Hi,
> is there a way to get the result of select query which uses or xml
> auto, elements to c# ?
> for ex, i have a query like
> "SELECT * from dbo.[user] where userid = @.UserID for xml auto,
> elements"
> and i want result of this query back to c# function, how can i do it'
> Pls reply as soon as possible.
> Cheers
>