|
Thank Mike for the info. That locking is something i was seeing. Im not sure a DataTable is a good idea for me with the amounts of information i have to deal with. I'll probably do the whole thing in chunks bit by bit.
thanks
paul
|
|
|
|
|
Hi,
I have a question about a sql query I need to do. I have a table with 3 fields, userID, entryDate and value. My query will have a date parameter called targetDate. What I was hoping to do was retrieve a single row for each user where the entryDate is the most recent entry for that user prior to the targetDate parameter. If there are no entries prior the the targetDate for the user, no rows will be returned for that user, if they have multiple entries with entryDate < targetDate, obviously only the most recent will be returned. If anyone was able to help me with the SQL for this, that'd be great.
Thanks,
Matt
|
|
|
|
|
You have do do this with a two pronged approach. Use can use max() and group by clause to get the newest date (only) for each customer, then you can join back on the table to get the rest of the data:-
select yourTable.* from (
select userID, max(entryDate) as entryDate
from yourTable
group by userid
) k
inner join yourTable
on yourTable.userId = k.userId and yourTable.entryDate = k.entryDate
using System.Beer;
|
|
|
|
|
Thanks Jon!
That works perfectly 
|
|
|
|
|
Thanks Jon,
That works perfectly 
|
|
|
|
|
What’s the proper way to do object/relation mapping now days, for various data sources?
Is there any nice solutions here at code project, or should I wait for Microsoft’s ObjectSpaces (if it will ever be released) to solve my issues?
|
|
|
|
|
Hi,
Is it possible to have report with dynamic (varying) columns?
Like in one scenario it may return 3 columns (col1, clo2, col3)
In another scenario it may return 2 columns (col2, col3)
Thanks in advance
|
|
|
|
|
"auto update statistics" -- is there any good reason to turn it off?
|
|
|
|
|
i really don't understand you question as i don't know (and i believe everyone here don't either) what "auto update statistics" is ???
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
|
The statistics are used by SQL Server's Query Optimiser to decide whether an index is suitable for use in a query. The statistics show how selective the index is - that is, how many rows will be returned for a particular set of key data. You can view the statistics with DBCC SHOW_STATISTICS.
For the statistics to be useful, they must be reasonably up-to-date. If they're out of date, or missing, the query optimiser may pick a poor execution plan. If 'auto update statistics' is on, SQL Server automatically updates the statistics after a certain number of changes (INSERT, UPDATE, DELETE) have occurred - a stored query plan may then be recompiled.
Statistics aren't just useful for deciding whether to use an index. They can also be used to decide which table should be scanned in the inner loop and which in the outer loop of a nested-loops join operation. For that purpose you can create statistics on a collection of columns. If the 'auto create statistics' option is on, SQL Server will automatically create statistics for columns used in queries that aren't indexed.
On the whole, I'd recommend leaving both options turned on. The cost of building the statistics is typically small.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
I think I've heard people saying for OLAP database ... you should turn this off? Even then, I'm not sure why because application reads off OLAP cube, not staging database. Does it matter? OLAP Vs OLTP? I don't see ...
Norman Fung (c)
|
|
|
|
|
I thought it will work for a series of data in one column but it did not. Actually, if i have 1,2,3 in one column and would like to put 1 in column A, 2 in column B and 3 in coulumn C of another table.
I had something like:
insert test (a)
select substring(aa,1,patindex('%,%',aa)-1)as a from table1
insert test (b)
select substring(aa,3,patindex('%,%',aa)-1)as b from table1
insert test (c)
select substring(aa,5,patindex('%,%',aa)-1)as c from table1
but what happens is that it does not put data in 1 row. instead it gives me 3 rows like this:
1 NULL NULL
null 2 NULL
NULL NULL 3
How do I put this in one row
phokojoe
|
|
|
|
|
Each INSERT statement creates the number of rows selected - 1 in the case of an INSERT...VALUES statement, the number of rows returned by the SELECT in the case of INSERT...SELECT.
To put all the data in a single row, you simply need to select all the data you need for that row:
INSERT test (a, b, c)
SELECT
SUBSTRING(aa, 1, PATINDEX('%,%',aa)-1 ),
SUBSTRING(aa, 3, PATINDEX('%,%',aa)-1 ),
SUBSTRING(aa, 5, PATINDEX('%,%',aa)-1 )
FROM table1
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hello friends
Can anybody knows,it is possible to take sql server2000 database backup on another machine's disc.
I normally take backup on the disc of same machine where my database server is but if i want to take backup to another machine disc then that will possible???
T.I.A
Shashank
|
|
|
|
|
This is not possible in SQL Server 2000. I have a similar situation.
What I do is back the database in the current server programatically using SQL DMO and then FTP it. I know this information is not of much use. Still thought I should share it.
Thanks
Live Life King Size
Alomgir Miah
|
|
|
|
|
Hai
I am using SQl Server. in Which i Create a table Where Some of the Fields and set with default values.
+ When i try to generate SQL script using Enterprise manager(Database>All Task>>Generate SQL Script) . i didnt get Sql Script for default value and Some Constrants. Is there any way to generate the Script.
+ In one of my Table i Preset with some data . Is there any way to generate Insert SQL Script.
if these things are not done by Enterprise manage. please suggest me some third party utility.
Thanks & Regards
|
|
|
|
|
I am storing only date in sql as date/time datatype.When i am retriving it back in datagrid not only it returns the date but aslo sho the time 12.00am with it.I dont want to get the time.What can i do in this regard.
Thanking u in anticipation
Take it easy...
|
|
|
|
|
You should process the date before showing it. What prg language are you using?
Marc Soleda.
... she said you are the perfect stranger she said baby let's keep it like this... Tunnel of Love, Dire Straits.
|
|
|
|
|
i am using vb.net.Its asp calender control that is inserting the date withit or it is the sql datatype that is storing it like that.If u can tell in detail how ca i ,i will b very greatful
Take it easy...
|
|
|
|
|
Sorry, I don't know VB.NET quite well so I don't know if it's the control that inserts the data with the date and time or is the db that store the data in that way. You'd debug your code to know this item.
MArc Soleda
... she said you are the perfect stranger she said baby let's keep it like this... Tunnel of Love, Dire Straits.
|
|
|
|
|
The SQL DateTime type always includes both date and time (wouldn't that seem obvious from its name?). How you choose to display that is up to you. See if the display format for the datagrid column can be changed.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
While selecting from database itself convert it to date only format, then datagrid will show the same(Date Only)
select CONVERT(VARCHAR(10), DateTimeField, 103) AS DateOnly from tbl_Name
/*Above for date only*/
go
select DateTimeField AS DateandTime from tbl_Name
/*Abovefor date and time only*/
|
|
|
|
|
Thanks 4 repling i will try it and then let u know
Thanx Again
Take it easy...
|
|
|
|
|
Hi Buddy Could you please tell how can i set date/time datatype custom format in SQL like in MS Access [dd/MM/yyy].
I mean i want to store date in day/month/year [27/07/2005].
Muhammad Waqas Butt
waqasb4all@yahoo.com
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
