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 field from subtotal

hi all,

i have a problem in my report; some fields grouped by matrix and include a subtotal. In column group there is 2 field and one of them contain integer values. I want to sum all of the this fields and minus from a rowgroup field. Is there a way for to sum fields in coloumn group or getting total field from subtotal?

I'm having trouble understanding your problem and your English. Could you try to rephrase your question and add a little more detail?

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

Geting the last inserted row for each CLIENT. How?

Hi,

I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
where the conversation data is registered (and other columns).

Now I need to retrieve, for each client, the last N (for some clients,
eventually, less then N) conversations with one T-SQL statement.

Does anyone knows how to do this? Is it possible with T-SQL only?

Thanks.

Antonio:

Is one of this close to what you are looking for?

Dave

-- --
-- I have used a LEFT JOIN in this case because I think that it
-- is probably important that a client that has not been called
-- be listed.
--
-- I went ahead and added the "recordsToPull" column to provide
-- for the future change when you want to be able to vary the
-- number of conversations returned on a client-by-client
-- basis
-- --
set nocount on

declare @.client table
( client_id integer not null,
recordsToPull integer not null
)

declare @.conversations table
( client_id integer not null,
conv_id integer not null,
conversation_date datetime not null
)

insert into @.client values (1, 4)
insert into @.client values (2, 2)
insert into @.client values (3, 4)
insert into @.client values (4, 4)
--select * from @.client

insert into @.conversations values (1, 1, '1/14/2006')
insert into @.conversations values (1, 2, '10/31/2006')
insert into @.conversations values (1, 3, '3/17/6')

insert into @.conversations values (2, 4, '2/2/6')
insert into @.conversations values (2, 5, '2/2/6')
insert into @.conversations values (2, 6, '2/4/6')
insert into @.conversations values (2, 7, '2/5/6')
insert into @.conversations values (2, 8, '2/8/6')

insert into @.conversations values (3, 9, '7/1/5')
insert into @.conversations values (3, 10, '11/23/5')
insert into @.conversations values (3, 11, '1/17/6')
insert into @.conversations values (3, 12, '4/1/6')
insert into @.conversations values (3, 13, '10/31/6')
--select * from @.conversations

--
-- SQL Server 2005 Version
--
/*
select a.client_id,
a.recordsToPull,
b.conv_id,
b.conversation_date
from @.client a
left join
(
select client_id,
conv_id,
conversation_date,
row_number ()
over ( partition by client_id
order by conversation_date desc, conv_id desc
)
as seq
from @.conversations
) b
on a.client_id = b.client_id
and a.recordsToPull >= b.seq
order by a.client_id,
b.Seq
*/

--
-- SQL Server 2000 Version
--
select a.client_id,
a.recordsToPull,
b.conv_id,
b.conversation_date
from @.client a
left join
(
select x.client_id,
x.conv_id,
x.conversation_date,
count(*) as seq
from @.conversations x
inner join @.conversations y
on x.client_id = y.client_id
and ( x.conversation_date < y.conversation_date or
x.conversation_date = y.conversation_date and
x.conv_id <= y.conv_id
)
group by x.client_id,
x.conversation_date,
x.conv_id
-- order by x.client_id,
-- x.conversation_date desc,
-- x.conv_id desc
) b
on a.client_id = b.client_id
and a.recordsToPull >= b.seq
order by a.client_id,
b.Seq

--
-- Sample Output:
--

-- client_id recordsToPull conv_id conversation_date
-- -- - -- --
-- 1 4 2 2006-10-31 00:00:00.000
-- 1 4 3 2006-03-17 00:00:00.000
-- 1 4 1 2006-01-14 00:00:00.000
-- 2 2 8 2006-02-08 00:00:00.000
-- 2 2 7 2006-02-05 00:00:00.000
-- 3 4 13 2006-10-31 00:00:00.000
-- 3 4 12 2006-04-01 00:00:00.000
-- 3 4 11 2006-01-17 00:00:00.000
-- 3 4 10 2005-11-23 00:00:00.000
-- 4 4 NULL NULL

|||

It would help if you post the version of SQL Server you are using. In SQL Server 2005, you can do below:

select c.*, t.*

from CLIENTS as c

cross apply (

select top(@.n) *

from CONVERSATIONS as cn

where cn.CLIENT_ID = c.CLIENT_ID

order by cn.CONVERSATION_DATE desc

) as t

|||

Thanks

this is exactly what I needed, worked perfectly 2005 version, didn't try 2000, but I'll need it too...

|||

Hi,

I don't know if you tryed it but didn't work for me. Thanks anyway.

Geting the last inserted row for each CLIENT. How?

Hi,
I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
where the conversation data is registered (and other columns).
Now I need to retrieve, for each client, the last N (for some clients,
eventually, less then N) conversations with one T-SQL statement.
Does anyone knows how to do this? Is it possible with T-SQL only?
Thanks.
See if this may work. If you also need to see clients without a
conversation, change the join to a left join.
select client.client_id, Conversations.conv_id
from client
inner join conversations on conversations.client_id = client.client_id
and conversations.conv_id in
(
select top 15 conv_id
from conversations
where client_id = client.client_id
order by conversation_date desc
)
A.Neves wrote:
> Hi,
> I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
> CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
> where the conversation data is registered (and other columns).
> Now I need to retrieve, for each client, the last N (for some clients,
> eventually, less then N) conversations with one T-SQL statement.
> Does anyone knows how to do this? Is it possible with T-SQL only?
> Thanks.
|||Didn't work,
but look here:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=917505&SiteID=17&mode=1
<Jochen.Markert@.fnf.com> escreveu na mensagem
news:1163189244.535260.305810@.m73g2000cwd.googlegr oups.com...
> See if this may work. If you also need to see clients without a
> conversation, change the join to a left join.
> select client.client_id, Conversations.conv_id
> from client
> inner join conversations on conversations.client_id = client.client_id
> and conversations.conv_id in
> (
> select top 15 conv_id
> from conversations
> where client_id = client.client_id
> order by conversation_date desc
> )
> A.Neves wrote:
>

Geting the last inserted row for each CLIENT. How?

Hi,
I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
where the conversation data is registered (and other columns).
Now I need to retrieve, for each client, the last N (for some clients,
eventually, less then N) conversations with one T-SQL statement.
Does anyone knows how to do this? Is it possible with T-SQL only?
Thanks.See if this may work. If you also need to see clients without a
conversation, change the join to a left join.
select client.client_id, Conversations.conv_id
from client
inner join conversations on conversations.client_id = client.client_id
and conversations.conv_id in
(
select top 15 conv_id
from conversations
where client_id = client.client_id
order by conversation_date desc
)
A.Neves wrote:
> Hi,
> I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
> CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
> where the conversation data is registered (and other columns).
> Now I need to retrieve, for each client, the last N (for some clients,
> eventually, less then N) conversations with one T-SQL statement.
> Does anyone knows how to do this? Is it possible with T-SQL only?
> Thanks.|||Didn't work,
but look here:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=917505&SiteID=17&mode=1
<Jochen.Markert@.fnf.com> escreveu na mensagem
news:1163189244.535260.305810@.m73g2000cwd.googlegroups.com...
> See if this may work. If you also need to see clients without a
> conversation, change the join to a left join.
> select client.client_id, Conversations.conv_id
> from client
> inner join conversations on conversations.client_id = client.client_id
> and conversations.conv_id in
> (
> select top 15 conv_id
> from conversations
> where client_id = client.client_id
> order by conversation_date desc
> )
> A.Neves wrote:
>> Hi,
>> I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table
>> where
>> CONV_ID and CLIENT_ID form the pk, there is another column
>> CONVERSATION_DATE
>> where the conversation data is registered (and other columns).
>> Now I need to retrieve, for each client, the last N (for some clients,
>> eventually, less then N) conversations with one T-SQL statement.
>> Does anyone knows how to do this? Is it possible with T-SQL only?
>> Thanks.
>sql

Geting the last inserted row for each CLIENT. How?

Hi,
I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
where the conversation data is registered (and other columns).
Now I need to retrieve, for each client, the last N (for some clients,
eventually, less then N) conversations with one T-SQL statement.
Does anyone knows how to do this? Is it possible with T-SQL only?
Thanks.See if this may work. If you also need to see clients without a
conversation, change the join to a left join.
select client.client_id, Conversations.conv_id
from client
inner join conversations on conversations.client_id = client.client_id
and conversations.conv_id in
(
select top 15 conv_id
from conversations
where client_id = client.client_id
order by conversation_date desc
)
A.Neves wrote:
> Hi,
> I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
> CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DA
TE
> where the conversation data is registered (and other columns).
> Now I need to retrieve, for each client, the last N (for some clients,
> eventually, less then N) conversations with one T-SQL statement.
> Does anyone knows how to do this? Is it possible with T-SQL only?
> Thanks.|||Didn't work,
but look here:
[url]http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=917505&SiteID=17&mode=1[/
url]
<Jochen.Markert@.fnf.com> escreveu na mensagem
news:1163189244.535260.305810@.m73g2000cwd.googlegroups.com...
> See if this may work. If you also need to see clients without a
> conversation, change the join to a left join.
> select client.client_id, Conversations.conv_id
> from client
> inner join conversations on conversations.client_id = client.client_id
> and conversations.conv_id in
> (
> select top 15 conv_id
> from conversations
> where client_id = client.client_id
> order by conversation_date desc
> )
> A.Neves wrote:
>

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() in UDF column workaround

I have a column that needs to display the number of Status hours between
Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am usin
g
an Access project as my front end and SQL Server 2000 as my back end. I have
tried using the following as a row source in my function:
CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE +
STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
like that. How can I display the status time on my form? I was thinking mayb
e
the text box record source could be a select statement but not sure how to
write it, any ideas?DateDiff(HH, STATUS_START_DATE + STATUS_START_TIME,
COALESCE(STATUS_STOP_DATE + STATUS_STOP_TIME,
GETDATE())
Roy
On Sat, 4 Mar 2006 14:08:27 -0800, AkAlan
<AkAlan@.discussions.microsoft.com> wrote:

>I have a column that needs to display the number of Status hours between
>Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am usi
ng
>an Access project as my front end and SQL Server 2000 as my back end. I hav
e
>tried using the following as a row source in my function:
>CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE +
>STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
>STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
>I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
>like that. How can I display the status time on my form? I was thinking may
be
>the text box record source could be a select statement but not sure how to
>write it, any ideas?|||Hi
CREATE FUNCTION dbo.Get_Getdate
(@.dt DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN @.dt
END
SELECT dbo.Get_Getdate (GETDATE())
SELECT dbo.Get_Getdate ('20050101')
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:30B29109-1B8E-4716-A506-EEDB943F4B64@.microsoft.com...
>I have a column that needs to display the number of Status hours between
> Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am
> using
> an Access project as my front end and SQL Server 2000 as my back end. I
> have
> tried using the following as a row source in my function:
> CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE
> +
> STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
> STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
> I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
> like that. How can I display the status time on my form? I was thinking
> maybe
> the text box record source could be a select statement but not sure how
> to
> write it, any ideas?sql

GETDATE() Function Issue

Hi Everyone,
I'm running into an issue trying to update a column on a remote database.
The server is linked from the server I'm calling this update statement. The
syntax is as follows:
UPDATE Server1.Test.dbo.Testdata
SET dtUpdated = GETDATE()
WHERE intID = 111
The problem is that the update takes a very long time to complete. Now if I
declare a variable with a datetime and set that variable to GETDATE(), the
update takes seconds. Is there some known issue? Can anyone help with this
?
I have noticed that I can update small size tables, but it takes much longer
to update large size tables. The columns in the "Where" clause have indexes
on them and that can't be the issue since my test using the variable worked
quicker. I'm just wondering why I can't use the GETDATE() function directly
.
DarrenDarren
No, it works fine with GETDATE() function too. Please take a look at
execution plan of the query (with GETDATE() and without) and see what is
going on
"Darren" <Darren@.discussions.microsoft.com> wrote in message
news:8D7E1367-7B04-4F57-B671-F79AB6F6E60C@.microsoft.com...
> Hi Everyone,
> I'm running into an issue trying to update a column on a remote database.
> The server is linked from the server I'm calling this update statement.
> The
> syntax is as follows:
> UPDATE Server1.Test.dbo.Testdata
> SET dtUpdated = GETDATE()
> WHERE intID = 111
> The problem is that the update takes a very long time to complete. Now if
> I
> declare a variable with a datetime and set that variable to GETDATE(), the
> update takes seconds. Is there some known issue? Can anyone help with
> this?
> I have noticed that I can update small size tables, but it takes much
> longer
> to update large size tables. The columns in the "Where" clause have
> indexes
> on them and that can't be the issue since my test using the variable
> worked
> quicker. I'm just wondering why I can't use the GETDATE() function
> directly.
> Darren|||Run it again and see if there is any issues
Madhivanan|||I looked at the execution plan and it is doing what I thought it would. The
execution plan shows that it does a remote scan to look at the whole table
then it will filter by the where clause. This is the source of my problem.
For a large table that holds over 4 million records, it has to scan that
whole table before filtering on the primary key I'm already passing it. I
don't understand why on the remote server it must scan the whole table
instead of already filtering by the where clause. When I use a variable to
update the date column it shows a remote query for the update. Why is this
happening? Did some setting happen that I don't know about? How can I trac
k
down this issue?
"Uri Dimant" wrote:

> Darren
> No, it works fine with GETDATE() function too. Please take a look at
> execution plan of the query (with GETDATE() and without) and see what is
> going on
>
>
>
> "Darren" <Darren@.discussions.microsoft.com> wrote in message
> news:8D7E1367-7B04-4F57-B671-F79AB6F6E60C@.microsoft.com...
>
>

GetBlobData method fails

Hi,

I have s Script Component, that retrieves data from NTEXT column using this code:

Dim b As Byte()

If (Row.OutputXML.Length > 0) And (Not (Row.OutputXML_IsNull)) Then

b = Row.OutputXML.GetBlobData(0, CInt(Row.OutputXML.Length))

End If

I′m getting this error:

[Script Component 1 [838]] Error: System.Runtime.InteropServices.COMException (0x80004005): Error HRESULT E_FAIL has been returned from a call to a COM component. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)

OutputXML column is filled by Ole Db Command from an output parameter (nvarchar(max)) of a stored procedure. In management studio sp works fine, even Execute SQL Task returns correct (but truncated) data.

Please, help!

what is the SSIS data type (not the original SQL data type) assigned in OleDB Command to OutputXML column?|||data type is DT_NTEXT

get value of rowguidcol from last inserted row

How would I get the value of a ROWGUID column of the row I just inserted? (like using @.@.identity for an identity column.)

Thanks!I think that the only way to accomplish this is to use the NEWID() function before your INSERT statement to explicitly assign the value.

David Penton has a stored proceudre which explains this technique:A example of returning a guid as an "Identity" in ADO

Terri|||Why would you like this?

Why do you not create the GUID on the client side?

GUID's are defiend to be unique wherever you create them. There is - contrary to identity fields - no need to have the server define them.

This is the beauty of them - I know them when the object they mark (if they are the PK) is created, not once it is inserted into the database.|||I would like this because I know, like, and respect David Penton.

Yes, having the client create the GUID would be another way to go. I don't see a real advantage of creating the GUID on the client side, however.

To me, a row identifier is something that SQL needs and cares about, and the client couldn't care less about. So why would the client have the job of creating it?

Maybe you can explain further.

Terri|||::Why would you like this?

::Why do you not create the GUID on the client side?

This is what I ended up doing.|||::To me, a row identifier is something that SQL needs and cares about, and the client couldn't
::care less about.

In this casse, why does teh client need to know at all?

If the ROWGUID is simply used as replication identifier, for example, the client can be "ignorant" and just ignore it.

Obviously, for some reason, this is not the case - the client needs to know.

And then, i f it needs to know, and inserts the row anyway, it can also determine it.

::So why would the client have the job of creating it?

Because the client inserts the row and obviously does something with the id, otherwise it would not requrey for it.

I assume it is some sort of primary key, too.

And here is is much easier to work with a guid the moment you create the row, instead of inserting it later.

get value of last item in column?

How can I get the value of the last Item in a coums?
SELECT COLUMN FROM DATABASE ORDER BY COLUMN DESC?
something with Fields(0).value?

Thank you in advanceYou can use TOP 1 to return only 1 row (based on the ORDER BY), something
like this:

SELECT TOP 1 COLUMN FROM TABLE ORDER BY COLUMN DESC

Also, MIN(COLUMN) and MAX(COLUMN) may work depending on how you define last
item, like:

SELECT MAX(COLUMN) FROM TABLE

Regards,

Plamen Ratchev
http://www.SQLStudio.com|||Thank you very much, but my question is more like how to get the exact
VALUE of the field (not the minimum or maximum)?

is there a command/query for it?

On 7 mar, 18:54, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

You can use TOP 1 to return only 1 row (based on the ORDER BY), something
like this:
>
SELECT TOP 1 COLUMN FROM TABLE ORDER BY COLUMN DESC
>
Also, MIN(COLUMN) and MAX(COLUMN) may work depending on how you define last
item, like:
>
SELECT MAX(COLUMN) FROM TABLE
>
Regards,
>
Plamen Ratchevhttp://www.SQLStudio.com

|||<andreas.hei@.googlemail.comwrote in message
news:1173689649.827708.218050@.n33g2000cwc.googlegr oups.com...

Quote:

Originally Posted by

Thank you very much, but my question is more like how to get the exact
VALUE of the field (not the minimum or maximum)?

Not sure what you mean by the exact value of the field. How do you define
last item in column.

Generally that means the min or max value.

If you can give us some example data perhaps we can give a better solution?

Quote:

Originally Posted by

>
is there a command/query for it?
>
>
>
>
On 7 mar, 18:54, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

>You can use TOP 1 to return only 1 row (based on the ORDER BY), something
>like this:
>>
>SELECT TOP 1 COLUMN FROM TABLE ORDER BY COLUMN DESC
>>
>Also, MIN(COLUMN) and MAX(COLUMN) may work depending on how you define
>last
>item, like:
>>
>SELECT MAX(COLUMN) FROM TABLE
>>
>Regards,
>>
>Plamen Ratchevhttp://www.SQLStudio.com

>
>

--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||In addition to Greg's comments, this is why you can use TOP 1 and then based
on the ORDER BY define what you need to be selected in the result set.

Or maybe you mean the last inserted row... But a table is unordered set. You
would have to use a column that can determine what the last inserted row is
(the latest ID, date/time stamp, etc.).

Plamen Ratchev
http://www.SQLStudio.com

Get Value of IDENTITY

Hi,
I need get value of IDENTITY column after a insert (of the inserted item),
hava way to do this automatic, or same function that do this?
ThanksCheck out SCOPE_IDENTITY() in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23vrhmlOxFHA.3864@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I need get value of IDENTITY column after a insert (of the inserted item),
> hava way to do this automatic, or same function that do this?
> Thanks
>|||3 ways
@.@.IDENTITY
IDENT_CURRENT
SCOPE_IDENTITY()
Read BOL
Rakesh
"ReTF" wrote:

> Hi,
> I need get value of IDENTITY column after a insert (of the inserted item),
> hava way to do this automatic, or same function that do this?
> Thanks
>
>|||@.@.Identity global variable should hold the value of the last generate
during an insert.
Martin
ReTF wrote:
> Hi,
> I need get value of IDENTITY column after a insert (of the inserted item),
> hava way to do this automatic, or same function that do this?
> Thanks
>|||You should use SCOPE_IDENTITY() because it is possible for a trigger to also
insert a row and generate an identity value. @.@.IDENTITY returns the last
IDENTITY value generated. IDENT_CURRENT returns the last generated IDENTITY
value for a table, but it's possible in a concurrent environment for
IDENT_CURRENT to change between the time that a row is inserted and the time
that IDENT_CURRENT is called. The best solution, therefore, is to use
SCOPE_IDENTITY() because it returns the last generated IDENTITY value within
the current scope, thus ignoring any IDENTITY values generated within
triggers.
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23vrhmlOxFHA.3864@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I need get value of IDENTITY column after a insert (of the inserted item),
> hava way to do this automatic, or same function that do this?
> Thanks
>

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 the smallest timespan between two entries

Hi all,
we have a table with a column of type "datetime"
We want to get the smallest timespan between two entries.
Now we get this timespan with the following query (it works but it's to
slow, it runs 5 secs with 250000 entries):
select min(Datediff(minute,a.rectime,b.rectime))
from dbo.value a, dbo.value b
where b.rectime = ( select min(rectime) from dbo.value
where rectime > a.rectime )
Any idea? Thanks in advance,
Mike
On Wed, 27 Oct 2004 03:49:07 -0700, mike wrote:

>Hi all,
>we have a table with a column of type "datetime"
>We want to get the smallest timespan between two entries.
>Now we get this timespan with the following query (it works but it's to
>slow, it runs 5 secs with 250000 entries):
>select min(Datediff(minute,a.rectime,b.rectime))
>from dbo.value a, dbo.value b
>where b.rectime = ( select min(rectime) from dbo.value
> where rectime > a.rectime )
>Any idea? Thanks in advance,
>Mike
Hi Mike,
Try changing the query to
SELECT MIN(DATEDIFF(minute, a.rectime, b.rectime))
FROM dbo.value AS a, dbo.value b
WHERE b.rectime > a.rectime
You might also add something like
AND b.rectime < DATEADD(minute, a.rectime, 200)
where you change the 200 to a value that you know to be higher that the
timespan you are looking for, but low enough to greatly reduce the number
of matches between the a and b version of the value table.
If that doesn't work, look at your indexes. This query would greatly
benefit from an index on rectime (or rectime plus extra columns). If the
rate of change of this table is not too high and a small performance hit
on inserts, updates and deletes is acceptable, create a nonclustered index
on only rectime - that should yield the best possible performance.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||This might be more efficient:
select top 1
-- add WITH TIES if you select additional columns and want duplicates
datediff (minute, T2.rectime, min(T1.rectime)) as timeDiffMinutes
from yourTable T1 join yourTable T2
on T1.rectime > T2.rectime
group by T2.rectime
order by min(T1.rectime) - T2.rectime
Steve Kass
Drew University
Hugo Kornelis wrote:

>On Wed, 27 Oct 2004 03:49:07 -0700, mike wrote:
>
>
>
>Hi Mike,
>Try changing the query to
>SELECT MIN(DATEDIFF(minute, a.rectime, b.rectime))
>FROM dbo.value AS a, dbo.value b
>WHERE b.rectime > a.rectime
>You might also add something like
>AND b.rectime < DATEADD(minute, a.rectime, 200)
>where you change the 200 to a value that you know to be higher that the
>timespan you are looking for, but low enough to greatly reduce the number
>of matches between the a and b version of the value table.
>
>If that doesn't work, look at your indexes. This query would greatly
>benefit from an index on rectime (or rectime plus extra columns). If the
>rate of change of this table is not too high and a small performance hit
>on inserts, updates and deletes is acceptable, create a nonclustered index
>on only rectime - that should yield the best possible performance.
>Best, Hugo
>
|||Oops - the suggestion I gave doesn't give a good query plan. This is
probably much better:
select top 1
datediff(minute,rectime, Nextrectime) as TimeDiff
from (
select
T1.rectime,
(select top 1 T2.rectime
from yourTable T2
where T2.rectime> T1.rectime
order by T2.rectime) as Nextrectime
from yourTable T1
) T
where Nextrectime is not null
order by Nextrectime - rectime
[and I shouldn't have replied to your post specifically - sorry]
SK
Hugo Kornelis wrote:

>On Wed, 27 Oct 2004 03:49:07 -0700, mike wrote:
>
>
>
>Hi Mike,
>Try changing the query to
>SELECT MIN(DATEDIFF(minute, a.rectime, b.rectime))
>FROM dbo.value AS a, dbo.value b
>WHERE b.rectime > a.rectime
>You might also add something like
>AND b.rectime < DATEADD(minute, a.rectime, 200)
>where you change the 200 to a value that you know to be higher that the
>timespan you are looking for, but low enough to greatly reduce the number
>of matches between the a and b version of the value table.
>
>If that doesn't work, look at your indexes. This query would greatly
>benefit from an index on rectime (or rectime plus extra columns). If the
>rate of change of this table is not too high and a small performance hit
>on inserts, updates and deletes is acceptable, create a nonclustered index
>on only rectime - that should yield the best possible performance.
>Best, Hugo
>
|||On Thu, 28 Oct 2004 20:31:00 -0400, Steve Kass wrote:

>and I shouldn't have replied to your post specifically - sorry
Hi Steve,
De nada. As long as the original poster sees it, all's well.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Get the Highest value.

Hi! i have a column named number(int),ref_number(char),date(date),creator(ch ar)...what would be my query string, if posible, to get the highest value for clumn 'number'? by the way im using SQL Desktop Engine... any inputs will be greatly appreciated...thanks in advanace!select max(number) as m from daTable|||thanks a bunch!!!!|||select max(number) as m from daTable
Truly, an elegant piece of coding. Sheer genious for its blend of brevity and functionality. I shall have to use this in my next project.