Help on the last updated date

Hi,
I'm trying to retrieve data from the last updated date. For example, when
job runs after long holiday, it will look for the last updated date and
retrieve the data. how would I achieve this?
I tried Max(invoicedate), the query is always timed out.
Please help,
Thanks,
SarahSELECT TOP 1 columns FROM table ORDER BY invoicedate DESC
Do you have an index on invoicedate? If not, then on a large table I really
wouldn't be surprised to see a timeout in either case.
"SG" <sguo@.coopervision.ca> wrote in message
news:u3bACnb%23FHA.3496@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm trying to retrieve data from the last updated date. For example, when
> job runs after long holiday, it will look for the last updated date and
> retrieve the data. how would I achieve this?
> I tried Max(invoicedate), the query is always timed out.
> Please help,
> Thanks,
> Sarah
>|||Hi Aaron,
Thanks for your quick response. Actually what I need to do is that I like to
have this max updated date to be a critiria to retrieve data from a few
tables, can I do this?
Thanks,
Sarah
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23Vbyd9b%23FHA.1248@.TK2MSFTNGP14.phx.gbl...
> SELECT TOP 1 columns FROM table ORDER BY invoicedate DESC
> Do you have an index on invoicedate? If not, then on a large table I
> really wouldn't be surprised to see a timeout in either case.
>
> "SG" <sguo@.coopervision.ca> wrote in message
> news:u3bACnb%23FHA.3496@.TK2MSFTNGP15.phx.gbl...
>|||> Thanks for your quick response. Actually what I need to do is that I like
> to have this max updated date to be a critiria to retrieve data from a few
> tables, can I do this?
Probably, but without better requirements, I can't tell you how.
http://www.aspfaq.com/5006|||Hi Aaron,
I have a table has sales history, it will be updated every day. I like to
get last day's sales on my report. I used DATEADD(d, -1, GETDATE()), it
works when there is no long wend or holiday. But I like to get a solution
to refine this to look for the last updated date. For example: Last day
sales was on Dec 23, 2005, and when I come back from holiday on Dec 28,2005,
I like my program to run to retrieve the data of Dec 23, not Dec 27.
Select * from sales where invoicedate=DATE(d,-1,GETDATE()).
Can I do this?
Thanks,
Sarah
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OIFlDRc%23FHA.1484@.tk2msftngp13.phx.gbl...
> Probably, but without better requirements, I can't tell you how.
> http://www.aspfaq.com/5006
>|||SELECT * --specify your columns; avoid the use of * in production
FROM sales
WHERE invoicedate = (SELECT MAX(InvoiceDate) FROM sales)
Be careful, though. If you get one order on the last day, and all of
your other orders show up on the day before, you'll have an awful empty
report.
Stu

Help on SQL Query

Hi,

I have a table called employee. I have fields like Emp.Nbr and login.date. I want to retrieve unique data of employee numbers. But I want unique with max(login.date) of an employee.

Ex: 111 7/19/2007

222 6/13/2007

111 7/25/2007

333 5/21/2007

222 7/12/2007

I want out put like

111 7/25/2007

222 7/12/2007

333 5/21/2007

How to write query for the above out put.

Kindly help.

Thanks

SELECT EMP.NBR,MAX(login.date)

FROM EMP INNER JOIN Login

on EMP.ID = Login.EmpId

Group by EMP.NBR

|||

select Nbr,max(LoginDate)

from emp

group by nbr

|||

Here it is,

Code Snippet

Create Table #emp (

[EmpId] Varchar(100) ,

[Date] DateTime

);

Insert Into #emp Values('111','7/19/2007');

Insert Into #emp Values('222','6/13/2007');

Insert Into #emp Values('111','7/25/2007');

Insert Into #emp Values('333','5/21/2007');

Insert Into #emp Values('222','7/12/2007');

Select EmpId, Max(Date) As date From #emp Group By EmpID

|||

hope this will suites your requirement: Here I have used Table Variable instead of Temporary Tables.

DECLARE @.Table TABLE ( Nbr int , LoginDate smalldatetime )

INSERT INTO @.Table (Nbr, LoginDate) VALUES(111, '7/19/2007')

INSERT INTO @.Table (Nbr, LoginDate) VALUES(222, '6/13/2007')

INSERT INTO @.Table (Nbr, LoginDate) VALUES(111, '7/25/2007')

INSERT INTO @.Table (Nbr, LoginDate) VALUES(333, '5/21/2007')

INSERT INTO @.Table (Nbr, LoginDate) VALUES(222, '7/12/2007')

SELECT

Nbr, max(LoginDate)

FROM @.Table

GROUP BY Nbr

Regards,

Prashanthi.

Help obtain a window of rows from a table

Hi,
I have a client program in vb.net that access a SQL server database. Each
time the client program need some data it retrieve the whole table, so it is
pretty slow.
I wonder if it is possible, for the client, to retrieve only a window of
rows around the actual value he is using. That is, if he is actually in row
4000 he will retrieve from row 3000 to 5000 but not the complete table. If
this is possible what I need is something like:
1) The client send SQL-Server a string with the actual ordering and the ID
of the actual row.
2) SQL-Server order the table following the order specified in the string
send by the client.
3) Using the ordered table SQL-Server "find" the ID of the actual row.
4) SQL-Server return a number of rows before and after the Id of the actual
row (no idea how to do this).
Any help.
Thanks,
JamesHi James,
Yes - its basically paging.
The basics are this...
declare @.results table (
idrow int not null identity,
yourresultcol1...
yourresultcol2...
)
insert @.results ( yourresultscol1, yourresultscol2 )
select yourresultscol1, yourresultscol2
from table...
where ...
order by ...
select *
from @.results
where idrow between @.start and @.finish
I know its not a complete working example but does that give you enough
idea?
Tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"James" <info@.pricetech.es> wrote in message
news:OTkWPANHGHA.1628@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a client program in vb.net that access a SQL server database. Each
> time the client program need some data it retrieve the whole table, so it
> is pretty slow.
> I wonder if it is possible, for the client, to retrieve only a window of
> rows around the actual value he is using. That is, if he is actually in
> row 4000 he will retrieve from row 3000 to 5000 but not the complete
> table. If this is possible what I need is something like:
> 1) The client send SQL-Server a string with the actual ordering and the ID
> of the actual row.
> 2) SQL-Server order the table following the order specified in the string
> send by the client.
> 3) Using the ordered table SQL-Server "find" the ID of the actual row.
> 4) SQL-Server return a number of rows before and after the Id of the
> actual row (no idea how to do this).
> Any help.
> Thanks,
> James
>
>|||Sorry, i forgot to mention, in SQL 2005 its a whole lot easier.
We have the rownumber() function and cte that does it all for us - there are
some really useful examples in bol.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:uhnuUbNHGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Hi James,
> Yes - its basically paging.
> The basics are this...
> declare @.results table (
> idrow int not null identity,
> yourresultcol1...
> yourresultcol2...
> )
> insert @.results ( yourresultscol1, yourresultscol2 )
> select yourresultscol1, yourresultscol2
> from table...
> where ...
> order by ...
> select *
> from @.results
> where idrow between @.start and @.finish
> I know its not a complete working example but does that give you enough
> idea?
> Tony
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "James" <info@.pricetech.es> wrote in message
> news:OTkWPANHGHA.1628@.TK2MSFTNGP12.phx.gbl...
>|||James
I think Tom Moreau had already answered the same or almost the same question
a few days ago. Pls search on internet
"James" <info@.pricetech.es> wrote in message
news:OTkWPANHGHA.1628@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a client program in vb.net that access a SQL server database. Each
> time the client program need some data it retrieve the whole table, so it
> is pretty slow.
> I wonder if it is possible, for the client, to retrieve only a window of
> rows around the actual value he is using. That is, if he is actually in
> row 4000 he will retrieve from row 3000 to 5000 but not the complete
> table. If this is possible what I need is something like:
> 1) The client send SQL-Server a string with the actual ordering and the ID
> of the actual row.
> 2) SQL-Server order the table following the order specified in the string
> send by the client.
> 3) Using the ordered table SQL-Server "find" the ID of the actual row.
> 4) SQL-Server return a number of rows before and after the Id of the
> actual row (no idea how to do this).
> Any help.
> Thanks,
> James
>
>|||James wrote:
> Hi,
> I have a client program in vb.net that access a SQL server database. Each
> time the client program need some data it retrieve the whole table, so it
is
> pretty slow.
> I wonder if it is possible, for the client, to retrieve only a window of
> rows around the actual value he is using. That is, if he is actually in ro
w
> 4000 he will retrieve from row 3000 to 5000 but not the complete table. If
> this is possible what I need is something like:
> 1) The client send SQL-Server a string with the actual ordering and the ID
> of the actual row.
> 2) SQL-Server order the table following the order specified in the string
> send by the client.
> 3) Using the ordered table SQL-Server "find" the ID of the actual row.
> 4) SQL-Server return a number of rows before and after the Id of the actua
l
> row (no idea how to do this).
> Any help.
> Thanks,
> James
Take a look at:
http://www.aspfaq.com/show.asp?id=2120
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

help me in connection

I am using sql server and c# (windows application)>>

the task is to retrieve records from table in the database and view it in page load , with the abilty to choose the records >>

this means that select range >>> e.g. from 3 to 7 (this can done by textbox)

so how can i do this >>

then I shall have two search buttons ( by name and by id) >> how can I implement that >>>

the last thing is to do the validation for every thing i can validate how I can do this >>>

thank u at the beginning for help me

adorer:

I am using sql server and c# (windows application)>>

Since you are writing a Windows application, and these forums are for ASP.NET, you should post your question on theMSDN forums.

|||

SqlConnection cn =

new SqlConnection("User ID=sa;password=;Initial Catalog=Training;Data Source=local");

cn.Open();

DataSet ds =

new DataSet();

SqlDataAdapter da =

new SqlDataAdapter("select * from Students where ID = '"+ textBox1.Text + "'", cn);

da.Fill(ds, "Students");

dataGrid1.DataSource = ds;

cn.Close();

does this do the task