I would like to return the latest time stamp as well as the earliest time stamp out of a series of records.The key would be the same ID so i need a function or query that can search through each record and find the earliest time for that ID and the latest time.
ID LatestTime EarliestTime
1 05:00:00 02:00:30
I have achieved the latest time by using a RowNumber function and returning the latest record. The stickler is how to find the earliest record as well.
Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row? thanks
I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.
The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?
I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.
cn.Open() sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table scb = New SqlCeCommandBuilder(sda) sda.Update(dataset) cn.Close()
I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:
1. Start the PPC Program
2. Load DB into dataset
3. Create a ONE new record in dataset
4. Fill back to DB
When I take this four steps everytime, the filling time is almost 1s or even more!
Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)
However, when I give up the dataset and using the following code:
cn.Open() Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)
I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!
Code: CREATE TABLE [dbo].[DigiTracker]( [DigitrackID] [int] IDENTITY(1,1) NOT NULL, [contact_id] [int] NOT NULL,
[Code]....
-- not accurate b/c it returns 0 for all TimePerPageSec SELECT DATEDIFF(second,min(createDt),max(createDt)) AS TimePerPageSec FROM DigiTracker GROUP BY contact_id, ZinePageNumber time spent (in seconds) between each record
I want to know how long does each of the script piece take. So how can I keep the records of time spent and save them as a file or save them into a table?
Hi everyone:Using Sql Server SQL 8I'm trying to INSERT records into a "can software package" batchtable. I have a work-table that mimics the batch table. Aftermanipulating the records in the work-file I want to INSERT them intothe batch table.The problem is the batch table in the can software has a trigger onthe batch table which is going to force me to INSERT one record at atime. I've always been able to do an INSERT with no problem.The batch table has pretty basic columns:BatchIDBatchDateSeqNumberThese three fields are the key and then just some miscellaneouscolumns. Any easy way to loop thru my work-file to insert theserecords. Never done a loop in SQL so an example would be reallyreally appreciated. I have a sequence number so I was hoping to do aWhile loop but I really don't know enough about creating a loop tomake that call. Thanks in advance for any help.
I have a table with several datetime fields but they all list just the correct date. The time is always 12:00:00 AM. I don't have a field that records the actual time a record was created. I have a timestamp column, but it appears to get updated everytime the DB backs up. Is there a way using the date fields I have or otherwise, to determine the time a record was created?
We have a website that allows people to enter tasks and they are then given a stageID. ie 1 which is review for when they are first entered into our database. If they have not been reviewed in 5 minutes I want them to update automatically and move onto the next stage. I have tried using a stored procedure that adds the reocrd with a waitfor command but that doesn't work because it locks the connection ofcourse until it's finished the stored procedure which means the webpage doesn't come back for 5 minutes which we don't need. I'm asking if there is any other way I can do this without a waitfor command or somehow use it so that it doesn't hold the current connection.
CREATE TABLE GuestAccount ( Id_Guest smallint, SeqNo int IDENTITY(1,1), account money Null PRIMARY KEY(Id_Guest,SeqNo) ) Q? I want my SeqNo start from 1 for new Id_Guest is it possible ?
I am not sure how to go about doing this. I have a record that has a start time of 1 am and a stop time of 9pm (same day for simplicity) and I want to know how many hours during a peek time and how many were not.
For example, the application starts and inserts into the data base the start time of 1am and then the user stops the app at 9pm. Lets say the peek hours are 1pm to 7pm. I know i can do a date diff function to get how long the app ran for but how can I get the amount of hours it ran during the peek time? I know there has to be some mathematical solution to this but it is escaping me at the moment. I want to do this over many records so a a cte or pivot table is the end solution for performance.
This is a beginner's question so bear with me - I have a SSIS package that builds an Analysis Services fact table - depending on user input, this table is either re-built from scratch or any additional records since it was last run are created.
Where is the best place to store the time a package was last run - in a seperate table in the database or somewhere in the fact table?
Working with SQL 7 and Visual Basic 5.0: We have experienced a lock situation and we do not know how it happened nor how to solve it:
When trying to update a record we get the following message.
Run time Error 3197: The Microsoft Jet Database engine stopped the process because you and another user are attempting to change the same data at the same time.
And for sure, ONLY one user is connected at the time. Apparently, the record is marked and can be read but CAN NOT be updated. Fortunately the record CAN be deleted !!!
The only way we found was using the VISDATA.exe that comes with Vbasic, to delete the record and add a new record with the same information.
Since this condition makes any updating program to be aborted, it's a big problem. How this happened? Is there any way to prevent this? Is there a way to detect this in advance? Is there a way to correct this situation automatically, such as rebuild or check the Data Base?
Your advise will be greatly appretiated
TIA Gerardo Alvarez asaca@asaven.com asaca@telcel.net.ve Bienvenido a nuestro Site en http://www.asaven.com
I want to check a record in my table which has time ins and time outs feilds. I want to check on insertionof any new record that a new record should not be inserted on same timings and before 45 mins of timein and after 45 mins of timeout. How do i do it sql query ?
I'm writing a classic ASP application that records all logging of userlogins on our support site. The logging is a rolling window of how manypeople have logged in for a given month, i.e. tracked by a 'lastlogin'field so the tracking is done in a date range. So, for the month ofJanuary I would record lastlogin dates between January 1 and January31. My question is this...on the last day of the month (example beingJanauary)at 23:59:59PM I want to write the total number of users thathad a lastlogin date within the month of January and write that totalnumber to another table so I have a point-in-time figure for Januarylogins and how many users logged in in the month of January. How do Ido that? How can I have the sql server dynamically check the last dayof each month at 23:59:59PM and then run that query to obtain the totalnumbers users that logged in and then record that value to anothertable?? Do I use a SQL Job to do that?Any help would be greatly appreciated! Thanks!
I have the table with the similar set of records which mentioned below, find the time difference between two rows of record. By Using the MsgOut column i have to find time taken b/w PS & PV and some record doesnt have PV .
I am trying to pull the records which are being affected i.e, comparing the values and if not same then populating the record.
I am using the below condition in where clause however i am getting runt time error as "Conversion failure when converting date and/or time from character string"
Condition in Where clause:
cast(isNull(tab1.Col1,'') as datetime) <> cast(isNull(tab2.col1,'') as datetime)
I am trying to write a stored procedure that loops through the list of user tables, gets the record count for each one and write a record to an audit table with DATE, TABLENAME, RECORDCOUNT.I keep getting an error "Conversion failed when converting date and/or time from character string".Here is the script...
DECLARE @table nvarchar(500) DECLARE @sql nvarchar(520) DECLARE CursorSelect CURSOR FOR select table_name from INFORMATION_SCHEMA.tables where table_name not like 'sys%' order by table_name
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
Hi I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen.
To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues.
On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form.
I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it.
We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.
If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.
Thanks, Sarah
The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.
This is running on a Websphere Application Server v6.1.
I am attempting to create a multi-record file (as described in my last thread) and have found the following set of instructions very helpful: http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx
I have been able to create a sample file with two of my record types.
I now need to build on this further, because I have 9 record types in total that need to be extracted to a single flat file.
does anyone have any ideas how I might extend the example above to include more record types or know of another means of achieving this?
Thanks in advance for any help you might be able to provide.
Is that possible to restrict inserting the record if record already exist in the table.
Scenario: query should be
We are inserting a bulk information of data, it should not insert the row if it already exist in the table. excluding that it should insert the other rows.
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray
i have a matrix report and i want to switch the record background color with each record in the value column in that matrix report e.g 1st record background color is gray and next record background color is white and then the next record background color is gray ... and so on
