Comparing dates in one field

I have a table with an ID, Date and Comments and need to compare the ID and
the date and retrieve the latest comment for a filing based on the date. So
you could have many records with the same ID that refer to the same filing,
only with a number of different dates. I've tried various things, but do no
t
get the right result.
example:
ID Date Comment
01 6/1/2006 This is the first comment
01 6/2/2006 This is the second comment *
02 6/2/2006 This is a different comment
02 6/5/2006 This is a new comment for this filing *
What I need is to get the second row with the 01 and the last row with 02 ID
in this example indicated by *.
I'm using SQL Server 2000. Thanks for your help in advance.CREATE TABLE #Temp ([ID] VARCHAR(2),
[Date] DATETIME,
[Comment] VARCHAR(2000),
PRIMARY KEY ([ID], [Date]))
INSERT INTO #Temp ([ID], [Date], [Comment])
SELECT '01', '2006-06-01', 'This is the first comment'
UNION SELECT '01', '2006-06-02', 'This is the second comment*'
UNION SELECT '02', '2006-06-02', 'This is a different comment'
UNION SELECT '02', '2006-06-05', 'This is a new comment for this filing'
UNION SELECT '02', '2006-06-06', 'This is a newer comment'
UNION SELECT '03', '2006-06-01', 'New ID, New comment*'
UNION SELECT '02', '2006-07-01', 'The newest comment*'
SELECT t1.[ID], t1.[Date], t1.[Comment]
FROM #Temp t1
WHERE t1.[Date] =
(
SELECT MAX([Date])
FROM #Temp t2
WHERE t2.[ID] = t1.[ID]
)
GROUP BY t1.[ID], t1.[Date], t1.[Comment]
DROP TABLE #Temp
"SK" <SK@.discussions.microsoft.com> wrote in message
news:3B67DA49-A987-44A9-B403-8CA85D581817@.microsoft.com...
>I have a table with an ID, Date and Comments and need to compare the ID and
> the date and retrieve the latest comment for a filing based on the date.
> So
> you could have many records with the same ID that refer to the same
> filing,
> only with a number of different dates. I've tried various things, but do
> not
> get the right result.
> example:
> ID Date Comment
> 01 6/1/2006 This is the first comment
> 01 6/2/2006 This is the second comment *
> 02 6/2/2006 This is a different comment
> 02 6/5/2006 This is a new comment for this filing *
> What I need is to get the second row with the 01 and the last row with 02
> ID
> in this example indicated by *.
> I'm using SQL Server 2000. Thanks for your help in advance.|||Try this..
SELECT ID,Date,Comment FROM
YourTable WHERE Date =
(SELECT MAX(Date) FROM YourTable yt1 WHERE YourTable.Id = yt1.Id)
- Sha Anand
"SK" wrote:

> I have a table with an ID, Date and Comments and need to compare the ID an
d
> the date and retrieve the latest comment for a filing based on the date.
So
> you could have many records with the same ID that refer to the same filing
,
> only with a number of different dates. I've tried various things, but do
not
> get the right result.
> example:
> ID Date Comment
> 01 6/1/2006 This is the first comment
> 01 6/2/2006 This is the second comment *
> 02 6/2/2006 This is a different comment
> 02 6/5/2006 This is a new comment for this filing *
> What I need is to get the second row with the 01 and the last row with 02
ID
> in this example indicated by *.
> I'm using SQL Server 2000. Thanks for your help in advance.|||Hi there
One query that you can try is:
SELECT A.ID, A.Date, A.Comment
FROM dbo.[Comments] A
INNER JOIN (SELECT ID, MAX(Date) FROM dbo.[Comments] GROUP BY ID) B
ON A.ID = B.ID
Lucas
"SK" wrote:

> I have a table with an ID, Date and Comments and need to compare the ID an
d
> the date and retrieve the latest comment for a filing based on the date.
So
> you could have many records with the same ID that refer to the same filing
,
> only with a number of different dates. I've tried various things, but do
not
> get the right result.
> example:
> ID Date Comment
> 01 6/1/2006 This is the first comment
> 01 6/2/2006 This is the second comment *
> 02 6/2/2006 This is a different comment
> 02 6/5/2006 This is a new comment for this filing *
> What I need is to get the second row with the 01 and the last row with 02
ID
> in this example indicated by *.
> I'm using SQL Server 2000. Thanks for your help in advance.|||Thank you very much for your quick response! It seems to work perfectly!
I had the second Where clause in the wrong place!
"Sha Anand" wrote:
> Try this..
> SELECT ID,Date,Comment FROM
> YourTable WHERE Date =
> (SELECT MAX(Date) FROM YourTable yt1 WHERE YourTable.Id = yt1.Id)
> - Sha Anand
>
> "SK" wrote:
>|||Wow! This was quite fast and thorough!
I've never tried it this way before with Union Select. But it works
beautifully.
Thank you Mike for taking the time to go to such length! I Appreciate it!
Have a lovely day!
SK
"Mike C#" wrote:

> CREATE TABLE #Temp ([ID] VARCHAR(2),
> [Date] DATETIME,
> [Comment] VARCHAR(2000),
> PRIMARY KEY ([ID], [Date]))
> INSERT INTO #Temp ([ID], [Date], [Comment])
> SELECT '01', '2006-06-01', 'This is the first comment'
> UNION SELECT '01', '2006-06-02', 'This is the second comment*'
> UNION SELECT '02', '2006-06-02', 'This is a different comment'
> UNION SELECT '02', '2006-06-05', 'This is a new comment for this filing'
> UNION SELECT '02', '2006-06-06', 'This is a newer comment'
> UNION SELECT '03', '2006-06-01', 'New ID, New comment*'
> UNION SELECT '02', '2006-07-01', 'The newest comment*'
> SELECT t1.[ID], t1.[Date], t1.[Comment]
> FROM #Temp t1
> WHERE t1.[Date] =
> (
> SELECT MAX([Date])
> FROM #Temp t2
> WHERE t2.[ID] = t1.[ID]
> )
> GROUP BY t1.[ID], t1.[Date], t1.[Comment]
> DROP TABLE #Temp
> "SK" <SK@.discussions.microsoft.com> wrote in message
> news:3B67DA49-A987-44A9-B403-8CA85D581817@.microsoft.com...
>
>

Comparing dates

Hello,

I am trying to retrieve the data that are not more than 3 months. How do I do this? The closest thing that I can do is...

(dbo.CLASSIFIEDADS.PostDate > CONVERT(DATETIME, '2006-03-26 00:00:00', 102))

I want to be able to put 3 months in there somehow...

Thanks in Advance!

check out the DATEDIFF function.|||FYI, it didn't exactly work out the way I wanted so I found another function, DATEADD with a minus number(-90) for days. Thanks for your help.

Compare datetimes

Hi

I need to retrieve data from a table comparing datetimes. I mean I've a
table with a datetime data and I'll need to retrieve rows with date time
> now and date time < now + 5 minutes...

Is this posible ? Which is the best / easy way ?

Thanks in advance

J"Javier" <jleyba@.manresa.net> wrote in message
news:cu8frg$d8m$1@.news.ya.com...
> Hi
> I need to retrieve data from a table comparing datetimes. I mean I've a
> table with a datetime data and I'll need to retrieve rows with date time
> > now and date time < now + 5 minutes...
> Is this posible ? Which is the best / easy way ?
> Thanks in advance
> J

Check out DATEADD() in Books Online.

select col1, col2, ...
from dbo.MyTable
where dtcol > getdate()
and dtcol < dateadd(mi, 5, getdate())

Simon|||On Mon, 07 Feb 2005 20:34:05 +0100, Javier wrote:

>Hi
>I need to retrieve data from a table comparing datetimes. I mean I've a
>table with a datetime data and I'll need to retrieve rows with date time
> > now and date time < now + 5 minutes...
>Is this posible ? Which is the best / easy way ?

Hi Javier,

SELECT Column1, Column2, ...
FROM MyTable
WHERE MyDatetimeColumn > CURRENT_TIMESTAMP
AND MyDatetimeColumn < DATEADD(minute, 5, CURRENT_TIMESTAMP)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

compare

hi i doing a project like "IMesh", whatever ,i will take a sentence
from the user and want to retrieve all row from the table has similar
words
for example the user enter "programming with c#" so i retrieve all
fields that contain "programming" or "c#"
some thing like what a search engine do
thanxamir samir (amir_s_anwar@.yahoo.com) writes:
> hi i doing a project like "IMesh", whatever ,i will take a sentence
> from the user and want to retrieve all row from the table has similar
> words
> for example the user enter "programming with c#" so i retrieve all
> fields that contain "programming" or "c#"
> some thing like what a search engine do

You probably want to look at full-text indexing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Communication Link Failure Error

Hi,

I am using ADO in my application to connect and retrieve data from Microsoft SQL Server 2000 SP4. When I start my application, it establishes connection to the server successfully and I am able to retrieve data from the server. here is my test scenario:

1. unplug the network cable from the client machine and put the cable back in after some time. I disconnect from the database in my code when the client machine disconnects from the network

2. Once the client machine comes back on the network, I try to reconnect and retrieve data from the sql server but I get the following error message when I try to execute a sql command:

[Microsoft][ODBC SQL Server Driver]Communication Link failure

This problem occurs only with Window XP SP2. With XP SP1 it works fine. It seems to be problem on the client side as with Windows XP SP1 I do not get this error message at all.

Here are the various details:

1. The firewall is off on the client workstation.

2. the connection string is:

driver={SQL Server};server=AA;uid=BB;pwd=CC;database=DD

where AA = DNS name of server machine where sql server is running.

BB = user name

CC = password

DD= database name

3. The client is remote to the server.

4. I can ping the server machine when the client machine reconnects to the network.

5. I can still telnet the server when the client machine reconnects to the network.

6. client database provider is MDAC odbc.

7. the client and server machines are in the same domain.

8. Shared memory and TCPIP protocols are enabled and TCP is at the top of the list.

9. No alias is being used.

10. Viris scan is not installed.

11. force encriptioned is NOT checked.

Please reply.

It appears a connection pooling issue. The closed connections by the client app are returned to the pool and become invalid after you unplug the cable. Next time you try to connect, odbc reuse the invalid connectin and throw the error. Retry your connection or disable pooling should resolve your issue.|||

I am already doing this:

1. When the machine disconnects from the network, I close the database connection. when the machine reconnects to the network, I try to establish a new connection to the database.

2. The connection pooling is also disabled for SQL server odbc driver under Connection Pooling tab of ODBC Data Source Administrator.

Please reply.

|||

Good day,

Tahir posted this request in Oct this year, has there been any response yet?

We are experiencing exactely the same problem in completely different ODBC client and are desperately looking for a way around this.

regards

|||

I eventually managed to find a way around it. Here are the changes which I made to my code:

1. Changed the connection steing to use native OLE DB as follows:

Provider=sqloledb;Data Source=AAA;Initial Catalog=BBB;User Id=CCC;Password=DDD

where AAA = SQL Server name

BBB = database name

CCC = login user name

DDD= password

2. I declare a list which holds pending SQL request.

3. hook up ADO connection object's Connectioncomplete and Disconnection events.

4. when execution of a SQL query failes, an exception is raisedcatch this exception. check the list of pending requests. if it is empty then add this failed request into the list and disconnect from the database. (this will force to disconnect from an invalid connection if there is any). Do not attempt to disconnect from the database if there is already a pending request in the list.

5. I have a separate thread which attempts to connect to the database. once I have got connection to the database, i suspend that thread.

6. in disconnection event handler, I check if there is an error while I tried to disconnect from the database. If there is NO error then I resume the thread which attempts to connect to the database.

7. in connection event handler, I check if there is an error while I tried to connect to the database. If there is NO error then I start a timer which checks pending request list and if there is any pending request, send it to the SQL server and then empty the list.

Regards,

Tahir Sultan

|||

Tahir thanks for the response.

We still have a problem because we can only use ODBC?

regards

Amanda

|||

Can catch the error and retry the connection be one of your option?

Otherwise, please post your code here and we can try to repro it.

HTH

|||

At the moment we are diconneting and then re-connecting, but even this is not solving the problem. We need to stop the application to get rid of the error. This is only the case for the communication link failure error with all other errors diconnecting and re-connecting works fine.

regards

|||

I had the same problem when I was using ODBC. Even disconnecting and reconnecting did not work at all. I tried all possible ODBC driver configuration but none of them seemed to make any difference. But moving to native OLE DB driver solved the problem.

Tahir Sultan

|||We have a situation common to laptops. I'm convinced that it is the result of some power management setting (hibernate, hard drive shutdown, or standby), but can never log it to pinpoint it exactly. Any suggestions?

Communication Link Failure Error

Hi,

I am using ADO in my application to connect and retrieve data from Microsoft SQL Server 2000 SP4. When I start my application, it establishes connection to the server successfully and I am able to retrieve data from the server. here is my test scenario:

1. unplug the network cable from the client machine and put the cable back in after some time. I disconnect from the database in my code when the client machine disconnects from the network

2. Once the client machine comes back on the network, I try to reconnect and retrieve data from the sql server but I get the following error message when I try to execute a sql command:

[Microsoft][ODBC SQL Server Driver]Communication Link failure

This problem occurs only with Window XP SP2. With XP SP1 it works fine. It seems to be problem on the client side as with Windows XP SP1 I do not get this error message at all.

Here are the various details:

1. The firewall is off on the client workstation.

2. the connection string is:

driver={SQL Server};server=AA;uid=BB;pwd=CC;database=DD

where AA = DNS name of server machine where sql server is running.

BB = user name

CC = password

DD= database name

3. The client is remote to the server.

4. I can ping the server machine when the client machine reconnects to the network.

5. I can still telnet the server when the client machine reconnects to the network.

6. client database provider is MDAC odbc.

7. the client and server machines are in the same domain.

8. Shared memory and TCPIP protocols are enabled and TCP is at the top of the list.

9. No alias is being used.

10. Viris scan is not installed.

11. force encriptioned is NOT checked.

Please reply.

It appears a connection pooling issue. The closed connections by the client app are returned to the pool and become invalid after you unplug the cable. Next time you try to connect, odbc reuse the invalid connectin and throw the error. Retry your connection or disable pooling should resolve your issue.|||

I am already doing this:

1. When the machine disconnects from the network, I close the database connection. when the machine reconnects to the network, I try to establish a new connection to the database.

2. The connection pooling is also disabled for SQL server odbc driver under Connection Pooling tab of ODBC Data Source Administrator.

Please reply.

|||

Good day,

Tahir posted this request in Oct this year, has there been any response yet?

We are experiencing exactely the same problem in completely different ODBC client and are desperately looking for a way around this.

regards

|||

I eventually managed to find a way around it. Here are the changes which I made to my code:

1. Changed the connection steing to use native OLE DB as follows:

Provider=sqloledb;Data Source=AAA;Initial Catalog=BBB;User Id=CCC;Password=DDD

where AAA = SQL Server name

BBB = database name

CCC = login user name

DDD= password

2. I declare a list which holds pending SQL request.

3. hook up ADO connection object's Connectioncomplete and Disconnection events.

4. when execution of a SQL query failes, an exception is raisedcatch this exception. check the list of pending requests. if it is empty then add this failed request into the list and disconnect from the database. (this will force to disconnect from an invalid connection if there is any). Do not attempt to disconnect from the database if there is already a pending request in the list.

5. I have a separate thread which attempts to connect to the database. once I have got connection to the database, i suspend that thread.

6. in disconnection event handler, I check if there is an error while I tried to disconnect from the database. If there is NO error then I resume the thread which attempts to connect to the database.

7. in connection event handler, I check if there is an error while I tried to connect to the database. If there is NO error then I start a timer which checks pending request list and if there is any pending request, send it to the SQL server and then empty the list.

Regards,

Tahir Sultan

|||

Tahir thanks for the response.

We still have a problem because we can only use ODBC?

regards

Amanda

|||

Can catch the error and retry the connection be one of your option?

Otherwise, please post your code here and we can try to repro it.

HTH

|||

At the moment we are diconneting and then re-connecting, but even this is not solving the problem. We need to stop the application to get rid of the error. This is only the case for the communication link failure error with all other errors diconnecting and re-connecting works fine.

regards

|||

I had the same problem when I was using ODBC. Even disconnecting and reconnecting did not work at all. I tried all possible ODBC driver configuration but none of them seemed to make any difference. But moving to native OLE DB driver solved the problem.

Tahir Sultan

|||We have a situation common to laptops. I'm convinced that it is the result of some power management setting (hibernate, hard drive shutdown, or standby), but can never log it to pinpoint it exactly. Any suggestions?

Communication Link Failure Error

Hi,

I am using ADO in my application to connect and retrieve data from Microsoft SQL Server 2000 SP4. When I start my application, it establishes connection to the server successfully and I am able to retrieve data from the server. here is my test scenario:

1. unplug the network cable from the client machine and put the cable back in after some time. I disconnect from the database in my code when the client machine disconnects from the network

2. Once the client machine comes back on the network, I try to reconnect and retrieve data from the sql server but I get the following error message when I try to execute a sql command:

[Microsoft][ODBC SQL Server Driver]Communication Link failure

This problem occurs only with Window XP SP2. With XP SP1 it works fine. It seems to be problem on the client side as with Windows XP SP1 I do not get this error message at all.

Here are the various details:

1. The firewall is off on the client workstation.

2. the connection string is:

driver={SQL Server};server=AA;uid=BB;pwd=CC;database=DD

where AA = DNS name of server machine where sql server is running.

BB = user name

CC = password

DD= database name

3. The client is remote to the server.

4. I can ping the server machine when the client machine reconnects to the network.

5. I can still telnet the server when the client machine reconnects to the network.

6. client database provider is MDAC odbc.

7. the client and server machines are in the same domain.

8. Shared memory and TCPIP protocols are enabled and TCP is at the top of the list.

9. No alias is being used.

10. Viris scan is not installed.

11. force encriptioned is NOT checked.

Please reply.

It appears a connection pooling issue. The closed connections by the client app are returned to the pool and become invalid after you unplug the cable. Next time you try to connect, odbc reuse the invalid connectin and throw the error. Retry your connection or disable pooling should resolve your issue.|||

I am already doing this:

1. When the machine disconnects from the network, I close the database connection. when the machine reconnects to the network, I try to establish a new connection to the database.

2. The connection pooling is also disabled for SQL server odbc driver under Connection Pooling tab of ODBC Data Source Administrator.

Please reply.

|||

Good day,

Tahir posted this request in Oct this year, has there been any response yet?

We are experiencing exactely the same problem in completely different ODBC client and are desperately looking for a way around this.

regards

|||

I eventually managed to find a way around it. Here are the changes which I made to my code:

1. Changed the connection steing to use native OLE DB as follows:

Provider=sqloledb;Data Source=AAA;Initial Catalog=BBB;User Id=CCC;Password=DDD

where AAA = SQL Server name

BBB = database name

CCC = login user name

DDD= password

2. I declare a list which holds pending SQL request.

3. hook up ADO connection object's Connectioncomplete and Disconnection events.

4. when execution of a SQL query failes, an exception is raisedcatch this exception. check the list of pending requests. if it is empty then add this failed request into the list and disconnect from the database. (this will force to disconnect from an invalid connection if there is any). Do not attempt to disconnect from the database if there is already a pending request in the list.

5. I have a separate thread which attempts to connect to the database. once I have got connection to the database, i suspend that thread.

6. in disconnection event handler, I check if there is an error while I tried to disconnect from the database. If there is NO error then I resume the thread which attempts to connect to the database.

7. in connection event handler, I check if there is an error while I tried to connect to the database. If there is NO error then I start a timer which checks pending request list and if there is any pending request, send it to the SQL server and then empty the list.

Regards,

Tahir Sultan

|||

Tahir thanks for the response.

We still have a problem because we can only use ODBC?

regards

Amanda

|||

Can catch the error and retry the connection be one of your option?

Otherwise, please post your code here and we can try to repro it.

HTH

|||

At the moment we are diconneting and then re-connecting, but even this is not solving the problem. We need to stop the application to get rid of the error. This is only the case for the communication link failure error with all other errors diconnecting and re-connecting works fine.

regards

|||

I had the same problem when I was using ODBC. Even disconnecting and reconnecting did not work at all. I tried all possible ODBC driver configuration but none of them seemed to make any difference. But moving to native OLE DB driver solved the problem.

Tahir Sultan

|||We have a situation common to laptops. I'm convinced that it is the result of some power management setting (hibernate, hard drive shutdown, or standby), but can never log it to pinpoint it exactly. Any suggestions?

Communication between tasks in an SSIS Package

Hi,
I have a Flat File Source and I want to retrieve few properties of it in an Script Component. How do I?

Also, How could I make the file path of Flat File Source or Connection manager dynamic or configurable through some file ?

any input is appreciated.

Fahad

Fahad349 wrote:

Hi,
Also, How could I make the file path of Flat File Source or Connection manager dynamic or configurable through some file ?

Fahad

Look at package configurations. The forum search will be your friend.

communicate between two servers

I have two sql servers, A and B. each one has one database, A and B. I am trying to write a stored porcedure in database A to retrieve the data in database B. how do I do it? can you give me a simple example. (like "select")Check Books on Line for info on Linked Servers. Set that up and then you use the fully qualified names

From server A:

select field1, field2 from serverb.databasename.ownername.tablename
where ...|||When I use
servera.database...... in server A it works
but when I use
serverb.database...... in server A it doesnt work.|||what's the error message?

it's supposed to be.. [ [ [ server. ] [ database ] . ] [ owner_name ] . ] object_name|||first, I link two servers. then I run a select in view.
the error msg is
"Access to the remote server is denied because no login-mapping exists"
how do I do?|||It's BOL on how to do it. Linking the servers opens a channel between them. You have to map logins so that you then have the proper access. Basically, you say "If I'm logged in as Joe on server A, when I connect to server B from server A, I want to act like server B's user named Fred." Right click the linked server, Properties, Security tab.

CommandText on Report Server

When a report runs is it possible to retrieve the command text and modify it in order to append some additional "where" conditions, before the sql is processed to return data to the report?

You can write code like this

declare sSQL varchar(500)

declare sWhere varchar(200)

set sSQL = "Select * from mytable"

if lenght(@.Parameter1) > 0

begin

sWhere = " Where mycolume = '" + @.Parameter2 + "'"

end

sSQL = sSQL + sWhere

EXEC (sSQL)

|||

Thanks for the reply, but I need to be clearer with the question that I am asking.

I have written code to construct the piece of SQL that I need to append - it retrieves conditions from the database which define some additional filtering for security.

What I need to know is if it is possible at runtime to trap the SQL statement stored in the <CommandText> element of the RDL for the report that is running - append my additional segment of SQL just before it gets processed. Bear in mind that a Report designed may have several SQL statements from individual parts of the overall report - a data set tp provide values for parameters, a dataset for each section on a report, chart and matrix for example.

Should I be looking at doing it via a Data Processing extension, therefore trapping any report that that is run via SSRS?