I have a table of machine data that captures fault codes, time machine stopped and time machine started. I can easily calculate the downtime, but how do i take the last start time and subtract it from the next stop time (1 row from the next row) to get an up time. I need it to group by short date and fault as I intend to use Excel as a reporting tool and pulling in all data will not fit. What is the most efficient way?
Thanks,
Lee
Can anyone help with the following Transact SQL question? Thanks. Ineed a store procedure to return the the result recordset which will beexecute from a web page. The database has tables, A and B. For each Arecord, there are many related B records. In the B table there is atimestamp field which tracks the change of A record. For example, A1has B like the followings:ID TimeStamp Chg Code Descption== ========= ======= ========A1 1138375875 E null //end of the eventA1 1138025002 S resumeA1 1137092615 S don't careA1 1137092570 S stopA1 1137092256 I null //start of theeventI need to generate all records in table A and total elapse time foreach record, but B with Chg Code 'S' that has "don't cacre" to bededucted from the total time, so that the result will be like this:ID Name TotalTime(seconds)== ==== =======A1 xyz 351187
View 5 Replies View RelatedHello -I am trying to determine the last time a SP was executed. Does anyoneknow how to do this? I'm trying to cleanup some databases.Thanks!*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 3 Replies View Related
Hi, I'm sorry if this is the wrong place to ask this question. These forums are overwhelming and I couldn't find the appropriate place to post.
I am wondering if there is some way to determine when a query has completed. What I want to do is use a PleaseWaitButton in asp.net when the user clicks to run a query and have the button reactivate after the query has completed. I know how to set a specific wait time but I do not want the user to have to wait a default time for the query to complete. Any help you may provide would be greatly appreciated.
Thanks!
I am trying to figure out how to calculate the average time between phone calls for a user. The initial requirement is to calcualte this on all calls for an entire month but I would guess that would lead to other periods as well, such as daily, weekly, etc. One hurdle is what to do when going from one day to the next. I could possibly just week out any times between calls that are greater than a certain amount of time to address that.
Any way, here is a small sample of what I'll be dealing with. Any ideas on how to approach this or get it to work would be greatly appreciated.
Code Block
CREATE TABLE #avetime (origdate datetime, duration_seconds int, duration_minutes decimal(9,2), phoneuser varchar(20), calltype varchar(15))
INSERT into #avetime VALUES ('Jan 7 2008 9:19AM', 21, 0.4, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 9:19AM', 48, 0.8, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 9:33AM', 81, 1.4, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 9:35AM', 87, 1.5, 'Coleman', 'Internal')
INSERT into #avetime VALUES ('Jan 7 2008 9:37AM', 27, 0.5, 'Coleman', 'Internal')
INSERT into #avetime VALUES ('Jan 7 2008 9:42AM', 13, 0.2, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 9:43AM', 84, 1.6, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 12:00PM', 914, 15.2, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 2:24PM', 3, 0.1, 'Coleman', 'Internal')
INSERT into #avetime VALUES ('Jan 8 2008 10:13AM', 21, 0.4, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 8 2008 10:33AM', 482, 8.0, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 8 2008 11:49AM', 56, 0.9, 'Coleman', 'Long Distance')
I need to determine the actual date/time that a message was placed on the queue. In my "activated" procedure I want to log this information and pass it along to further processing routines. From what I can tell, the Queue table itself does not have this information captured.
View 4 Replies View RelatedI need to determine if a value for a group of rows is the same or different and I am not sure where to begin. I am fairly new to writing T-Sql code. I am working with SQL Server 2005. This is a work assignment; I am not a student.
I have two tables: One for Course and another for CourseDays. As the Course table implies, it lists courses for a school. The CourseDays tables has a row for each day a course is held. For instance, one course maybe held on Monday, Wednesday, and Friday, so there would be three rows, one row for each day. A given course could be held in a different room for each day. I am trying to write a query that returns one row for each course, and if the room number is the same for each day the class is held, return that room number in the row, else return 'Various'.
Course table:
CourseID, Desc
1 English
2 History
3 Science
CourseDays table:
CourseID, Day, RoomID
1 M 320
1 W 445
1 F 680
2 T 123
2 Th 123
3 M 514
3 T 514
3 W 521
3 Th 901
3 F 521
Expected results:
CourseID, Desc, RoomID
1 English Various
2 History 123
3 Science Various
I simplified the example, as there are about 30 columns in the table and about half a million rows.
I’m checking to see if anyone might know of a good way to accomplish what I’m trying to do. I have Customers and Orders. A customer might be the one who ordered a product or whom the product was sold to (yes they can be different. Think of a person ordering for a company). Now my requirement is to find the customers that Lapsed. A Lapsed customer is defined as a customer that has not made a purchase within 2-times their average time period between purchases. For example, if a customers purchase frequency averages 180 days. Then if they have not made a purchase within 360 days of today (2 x 180), then they are lapsed. Hopefully, that makes sense. If you need more information, just ask.
Here is some sample data (Just ignore customer ID 6):DECLARE @Customer TABLE
(
CustomerID INT NOT NULL PRIMARY KEY
)
DECLARE @Order TABLE
(
OrderID INT NOT NULL PRIMARY KEY,
OrderDateNumber INT NOT NULL,
OrderedByCustomerID INT NOT NULL,
SoldToCustomerID INT NOT NULL
)
INSERT @Customer
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
INSERT @Order
SELECT 1, 20070702, 2, 6
UNION ALL SELECT 2, 20040805, 3, 3
UNION ALL SELECT 3, 20071031, 2, 6
UNION ALL SELECT 4, 20080228, 2, 6
UNION ALL SELECT 5, 20070316, 2, 6
UNION ALL SELECT 6, 20070425, 5, 6
UNION ALL SELECT 7, 20070523, 5, 6
UNION ALL SELECT 8, 20070418, 3, 6
UNION ALL SELECT 9, 20051019, 3, 3
UNION ALL SELECT 10, 20010226, 2, 6
UNION ALL SELECT 11, 20050930, 3, 3
UNION ALL SELECT 12, 20050819, 3, 3
UNION ALL SELECT 13, 20060510, 3, 6
UNION ALL SELECT 14, 20070608, 2, 6
UNION ALL SELECT 15, 20061030, 3, 6
UNION ALL SELECT 16, 20050824, 3, 3
UNION ALL SELECT 17, 20061109, 5, 6
UNION ALL SELECT 18, 20071120, 2, 6
UNION ALL SELECT 19, 20050919, 4, 4
UNION ALL SELECT 20, 20071105, 2, 6
UNION ALL SELECT 21, 20071105, 2, 6
UNION ALL SELECT 22, 20050923, 3, 3
UNION ALL SELECT 23, 20050923, 3, 3
UNION ALL SELECT 24, 20040809, 3, 3
UNION ALL SELECT 25, 20050715, 3, 3
UNION ALL SELECT 26, 20000731, 2, 6
UNION ALL SELECT 27, 20011114, 2, 6
UNION ALL SELECT 28, 20040916, 4, 4
UNION ALL SELECT 29, 20071105, 2, 6
UNION ALL SELECT 30, 20070415, 3, 3
UNION ALL SELECT 31, 20070717, 3, 6
UNION ALL SELECT 32, 20080207, 4, 4
UNION ALL SELECT 33, 20050802, 3, 3
UNION ALL SELECT 34, 20041022, 4, 4
UNION ALL SELECT 35, 20060510, 2, 6
UNION ALL SELECT 36, 20061017, 4, 4
UNION ALL SELECT 37, 20050228, 3, 3
UNION ALL SELECT 38, 20070109, 5, 6
UNION ALL SELECT 39, 20071115, 2, 6
UNION ALL SELECT 40, 20080225, 4, 4
UNION ALL SELECT 41, 20030820, 3, 3
UNION ALL SELECT 42, 20071106, 2, 6
UNION ALL SELECT 43, 20070209, 5, 6
UNION ALL SELECT 44, 20070628, 4, 6
UNION ALL SELECT 45, 20051028, 3, 3
UNION ALL SELECT 46, 20051103, 3, 3
UNION ALL SELECT 47, 20070703, 2, 6
UNION ALL SELECT 48, 20080207, 2, 6
UNION ALL SELECT 49, 20070711, 3, 3
UNION ALL SELECT 50, 20070417, 3, 3
UNION ALL SELECT 51, 20051013, 3, 3
UNION ALL SELECT 52, 20050719, 3, 3
UNION ALL SELECT 53, 20071130, 2, 6
UNION ALL SELECT 54, 20070725, 2, 6
UNION ALL SELECT 55, 20070713, 3, 3
UNION ALL SELECT 56, 20070522, 5, 6
UNION ALL SELECT 57, 20050819, 3, 3
UNION ALL SELECT 58, 20050823, 4, 4
UNION ALL SELECT 59, 20041109, 3, 3
UNION ALL SELECT 60, 20031023, 4, 4
UNION ALL SELECT 61, 20000523, 2, 6
UNION ALL SELECT 62, 20051007, 3, 3
UNION ALL SELECT 63, 20050727, 3, 3
UNION ALL SELECT 64, 20051027, 3, 3
UNION ALL SELECT 65, 20021112, 3, 3
UNION ALL SELECT 66, 20050824, 3, 3
UNION ALL SELECT 67, 20070615, 5, 6
UNION ALL SELECT 68, 20050428, 2, 6
UNION ALL SELECT 69, 20060324, 3, 3
UNION ALL SELECT 70, 20070215, 5, 6
UNION ALL SELECT 71, 20070713, 3, 6
UNION ALL SELECT 72, 20050930, 3, 3
UNION ALL SELECT 73, 20070613, 2, 6
Here is the expected output(EDIT for correct output):CustomerID
3
5
Here are some more detailed results to see some of the calculations I performed:CustomerID MaxOrderDateOrderFrequencyInDays DateDiffOfLastOrder TwoTimesFrequency IsLapsed
-------------------------------------------------------------------------------------------
2 2008-02-28 141.850000 34 283.700000 0
3 2007-07-17 58.896551 260 117.793102 1
4 2008-02-25 176.222222 37 352.444444 0
5 2007-06-15 27.250000 292 54.500000 1
Yes, I already have a solution, but I didn’t want to post it yet because I didn’t want skew anyone’s answer. :)
PS, Sorry for the long post, but I wanted to provide some decent sample data.
Hi,
I am working on a SQL Server table designed by a partner company and cannot change the data structures. There is a table with a list of people available for calling out to a security system (keyholders).
From a web form, I need to allow my users to change the telephone calling order of the keyholders in the table.
The two important fields are AccountCode nVarChar and CallOrder nVarChar - where AccountCode + CallOrder must be unique.
As an example, the table may contain records with the following data..
1234, 1, Fred
1234, 2, Bert
1234, 3, Bob
If the user wants to make Bob the number 1 keyholder, Fred number 2 and Bert number 3 - what is the best practice for me to approach the update ?
Is this a job for ADO.Net or T-SQL ?
Thanks in advance.
Steve.
There is a stored procedure that inserts a row into 'Vendors' table. Is it possible that two different calls to this sp happen at the same time and as a result, each sp inserts into the table its row at exactly the same time?
View 1 Replies View RelatedHello,I have 4 tables having Customer, Customer_personal_info, Customer_Financial_info, Customer_Other_infoIn this Customer table had a primary key CustomerID , related with every other table with fkey.I want to insert data into four tables using one form having TABs .I created class and storedProcedures to insert row for each table.How to execute all four classes using beginTrans-commitTrans-Rollback-EndTrans. Thanking you,
View 1 Replies View RelatedHi all -
is there a way to process a file x records at a time?
We have a table that I need to append to an existing table. The date columns are currently in char but must be converted to datetime for the existing table. The problem is I have bad data. There are 3 million rows where the date field isn't valid for SQL's datetime format. Since this is the data I have, I have to work with it. I would like for SQL to just insert a null if it comes upon a bad date. Currently when it encounters a field that isn't valid, it stops the process with an error.
I have tried to go around it below, but there is still something "hanging" I would like to be able just to insert one million rows at a time and if it errors, then I can look at the next million, find the error, fix it and continue on.
Any suggestions? Or if you have a better idea all together I would love to see it.
SQL Server 7.0, SP2
,CASE
when
(substring(check_date,1,4) not between '1997' and '2000' or
substring(check_date,5,2) not between '01' and '12' or
substring(check_date,7,2) not between '01' and '31') THEN null
ELSE cast(check_date as datetime)
END AS check_date
Thanks,
Michelle
Hi all,
Can one of you help me with using a cursor that would insert only 100 rows at a time from source table 1 to target table 2. I am not able to loop beyond the first 100 rows.
Here is what I have till now:
CREATE procedure Insert100RowsAtaTime
AS
SET NOCOUNT ON
declare @Col1 int
declare @Col2 char(9)
DECLARE @RETURNVALUE int
DECLARE @ERRORMESSAGETXT varchar(510)
DECLARE @ERRORNUM int
DECLARE @LOCALROWCOUNT int
declare Insert_Cur cursor local fast_forward
FOR
SELECT top 100 Col1,Col2 from Table1
WHERE Col1 not in ( SELECT Col1 /* Col1 is PK. This statement is used to prevent the same rows from being inserted in Table 2*/
from Table2)
set @RETURNVALUE = 0
set @ERRORNUM = 0
BEGIN
open Insert_Cur
fetch NEXT from Insert_Cur into @Col1, @Col2
while (@@FETCH_STATUS = 0)
insert into Table2 (Col1,Col2) select @Col1,@Col2
SELECT @ERRORNUM = @@ERROR, @LOCALROWCOUNT = @@ROWCOUNT
IF @ERRORNUM = 0
BEGIN
IF @LOCALROWCOUNT >= 1
BEGIN
SELECT @RETURNVALUE = 0
END
ELSE
BEGIN
SELECT @RETURNVALUE = 1
RAISERROR ('INSERT FAILS',16, 1)
END
END
ELSE
BEGIN
SELECT @ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages]
WHERE error = @@ERROR
RAISERROR (@ERRORMESSAGETXT, 16, 1)
SELECT @RETURNVALUE = 1
END
fetch NEXT from Insert_Cur into @Col1, @Col2
end
close Insert_Cur
deallocate Insert_Cur
RETURN @RETURNVALUE
END
The stored procedure takes ~ 10 seconds every call. The execution plan is Index seek or clustered index scan almost everywhere . No table spools or lazy loads or key lookups. Cannot share the code or execution plan due to company rules and regulations.Issue is that i am dealing with result set of around 50,000 records . Out of these i have to return 20 records at a single time (which is also customizable i.e 40 / 60/ 150 records in a page). Application cannot handle all 50k records so i have to return 20 records for every stored procedure call.
The result set changes as per the start date and end date which i recieve as parameters. In application there are few Column filters namely- Country(around 50 countries), Outcome(around 6 to 10 values ) . These filters will values in drop down( as excel ) depending on the distinct values in that columns. These filters will be populated on every page, if no filter value is selected.Issue is if user does sorting or filtering any records , this stored procedure is called and every time i have to deal with ~50000 records.
Current Code :
Step 1 ) Get the required result set in temp table .
Step 2) Compute the results on some business rules . (Outcome and SharesAvailable calculation - see attachment)
Step 3) populate filter Columns (Country,Outcome) these values will be comma seperated.
Step 4) Dynamic query to get required result set i.e if user wants only 10 records in single page then TOP 10 . Sorting can be applied on any column mentioned in screenshot.
hi,
here i am with a table containing 5columns and 100 rows.i want to delete all rows at the same time. pls suggest me a way on this
One can never consent to creep,when one feels an impulse to soar
RAMMOHAN
I have a formview that uses a predefined dataset based on a cross table query. When the formview is in insert mode I need to insert the data into two seperate tables. Essentially I have tblPerson and tblAddress and my formview is capturing username, password, name, address line1, address line 2, etc. I presume I need to use a stored procedure to insert a row into tblPerson and then insert a row intp tblAddress. This is easy enough to do but the tables use RI and tblPerson has an imcremental primary key which needs to be innserted into a foreign key field in my address row. How do I do this? I'm using SQL Server.
View 3 Replies View RelatedHi guys,This might be simplest thing, but I am newbie to databases.I need to find out only rows modified within certain time period from adatabase. As I undertand a way out could be adding an where clause forthe time period might be an option, I might be wrong here again.But, wanted to know is there any other option. Can triggers or anyother things help me in this matter.Regards,Abhijeet
View 1 Replies View RelatedI have a table data like below
id type timestamp
1001 start1 10:34:23:545
1001 start2 10:34:24:545
1001 end2 10:34:24:845
1001 end1 10:34:25:545
1002 start1 10:34:25:645
1002 start2 10:34:25:745
1002 end2 10:34:25:945
1002 end1 10:34:25:965
I need the result as follows
id millisecond diff start1end1 millisecond diff start2end2
1001 end1 timestamp-start1 timestamp end2 timestamp-start2 timestamp
1002 end1 timestamp-start1 timestamp end2 timestamp-start2 timestamp
SQL Server 2008 R2
Dear Participants,
We are using merge replication for multi locational database but we are facing one problem in only one table which is not included in replication-
Table name is xxxxmast has only 39 row static information, but it used by every users for all task as select only information from this table like-
Select fin_year into mem_variable from xxxxmast where co_name = :global_variable
go
Code validation from here only.
Right validation from here only.
Report retrival validation from here only.
It means its usage for select from every user frequentely for so many times but we have to only fetch information from this table.
It was working prior fine but rightnow get problem for while-
Today Dated 08-March this table not accessible fro three times in eight hours-
1st time for 10 minute.
2nd time 10 minutes
3rd time 52 minutes.
Users want to login but at the login time years and other validation from this table, so users awaited for above mention time.
We had have do following by yesterday-
Drop table xxxxmast.
Create table xxxxmast.
Insert required data.
This is realy trouble for our application.
Any help realy great for us.
Thanks
R.Mall
I'm dealing with a problem.
The record information example
DateTimeStart , DateTimeEnd , action , duration (seconds)
2007-02-02 10:30:22 , 2007-02-02 11:30:22 action1 , 600
what i want is for every half hour between start and end a record
10.30 action1
11.00 action1
11.30 action1
how can i create this, i'm a little stuck on this
I have a table like this.
CREATE TABLE Table1
([S_ID] varchar(7), [S_ACTV_CODE] varchar(4), [S_USER] varchar(5), [S_DATETIME] varchar(19), [S_ACT_IND] int)
;
INSERT INTO Table1
([S_ID], [S_ACTV_CODE], [S_USER], [S_DATETIME], [S_ACT_IND])
VALUES
('AAA-111', NULL, 'USER1', '2015-06-15 00:21:06', 0),
('AAA-111', '2', 'USER1', '2015-06-15 00:21:07', 0),
[code]....
Basically I want to calculate the time spent by S_Users on a particular S_ACTV_CODE:
- S_ACTV_CODE_PREV means the previous active records.
- S_START_TIME is the time of S_DATETIME when a S_ACTV_CODE starts
- S_END_TIME is the time before a S_ACTV_CODE changes to another S_ACTV_CODE
- For the first record, S_ACTV_CODE is null, so there is no S_ACTV_CODE_PREV, so S_ACTV_CODE_PREV is NULL
- For the second record S_ACTV_CODE has some value, but S_ACTV_CODE_PREV is NULL for first record. So second record S_ACTV_CODE_PREV is also NULL
- For the last record (means S_ACTV_IND = 1), the user is currently working on it and S_ACTV_CODE is not changed. So S_END_TIME is a open time and we want to keep it as NULL
So the result should be as below:
S_ID S_ACTV_CODE_PREV S_ACTV_CODE_CURR S_USER S_START_TIME
S_END_TIME TIME_SPENT (in Sec)
AAA-111 NULL NULL USER1 2015-06-15 00:21:06
2015-06-15 00:21:07 1
AAA-111 NULL 2 USER1 2015-06-15 00:21:07
2015-06-17 03:20:33 183566
AAA-111 2 4 USER2 2015-06-17 03:20:33
[code]....
I want to Insert 10000 rows at a time and commit in sql server , Isthere a way to do it if the source tables have no id fields ?What would be the most efficient method?ThanksAjay
View 1 Replies View Related
Hi,
I have the following scenario :
CustomerDetail
customerid
customername
status
app_no
[status = 0 means customer virtually deleted]
CustomerArchive
archiveno [autoincrement]
customerid
customername
status
At the end of the month, I have to physically delete customers. I have written two stored procs:
proc1
create proc spoc_startdeletion
as
declare @app_no int
select @app_no = (select app_no from customerdetail where status=0)
EXEC spoc_insertcustomerarchive @app_no
-- After transferrin, physically delete
delete from customerdetail where status=0
proc2
create proc spoc_insertcustomerarchive
@app_no int
as
insert into customerarchive(customerid,customername,status)
select customerid,customername,status from customerdetail where app_no = @app_no
It works fine if there is only one row with status=0, however the problem is that when there are multiple rows in customerdetail with status=0, it returns 'Subquery returned more than one value'
How can i transfer multiple rows one by one from the customerdetail to customerarchive and then delete the rows once they are transferred.
Vidkshi
hi there
i am using .net framework 1.1 with SQL 2000 .
i want the data in table to get deleted automatically after 30 days of inserting data.
so how do i achieve this?
i have a table like below
create table staff_attendance
(
attendance_id int,
attendace_date datetime,
staff_id int,
working_year int,
hours int
)
values like
1 2014-06-30 00:00:00.0ST10121
2 2014-06-30 00:00:00.0ST10122
3 2014-06-30 00:00:00.0ST10122 ----same entry like previous one
4 2014-07-01 00:00:00.0ST10121
5 2014-07-01 00:00:00.0ST10122
6 2014-07-02 00:00:00.0ST10121
7 2014-07-02 00:00:00.0ST10122
8 2014-06-30 00:00:00.0ST10221
9 2014-06-30 00:00:00.0ST10222
10 2014-07-01 00:00:00.0ST1022 1
11 2014-07-01 00:00:00.0ST102 22
12 2014-07-02 00:00:00.0ST102 21
13 2014-07-02 00:00:00.0ST102 22
I Need to find the duplicate rows like same entries which is having more than 1 rows.... how do i find?
I have the table as
|start || end1 |
1/06/2015 1:00 || 1/06/2015 1:30
1/06/2015 2:00 || 1/06/2015 3:00
1/06/2015 3:20 || 1/06/2015 4:00
1/06/2015 4:00 || NULL
I want the output as : -
|start || end1 |
1/06/2015 1:00 || 1/06/2015 1:30
1/06/2015 1:30 || 1/06/2015 2:00
1/06/2015 2:00 || 1/06/2015 3:00
1/06/2015 3:00 || 1/06/2015 3:20
1/06/2015 3:20 || 1/06/2015 4:00
1/06/2015 4:00 || NULL
I am trying the below mentioned code but it is not giving me the desired output..
with cte as
(select
start
,end1
,ROW_NUMBER() over (order by (select 1)) as rn
[Code] .....
I am getting wrong output as -
| start || end1 |
1/06/2015 1:00 || 1/06/2015 1:30
1/06/2015 1:30 || 1/06/2015 2:00
1/06/2015 2:00 || 1/06/2015 4:00
1/06/2015 4:00 || 1/06/2015 4:00
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 .
LogID LocIDClientCert MsgType MsgOutMessageTimeStamp System
1151334934NOT SPECIFIEDQ_T12PS 2015-10-01 00:00:40.980AHR
1151335243NOT SPECIFIEDD_T12PV 2015-10-01 00:00:53.800AHR
1151342944NOT SPECIFIEDQ_T12PS 2015-10-01 00:05:40.957AHR
1151343281NOT SPECIFIEDD_T12PV 2015-10-01 00:05:53.670AHR
1151350046NOT SPECIFIEDQ_T12PS 2015-10-01 00:10:40.970AHR
1152760563759NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:29.617AHR
1152760739690NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:33.633AHR
I know 2008 MS SQL Server has a timestamp data type that adds date and time when the rows are inserted. Is there a way to automatically update the date and time when the rows are updated?
View 5 Replies View Relatedequipmentid downtimestartdate downtimeenddate dowtime
a3er 2015-03-15 02:00 2015-03-17 23:00 69
b6e4 2015-03-18 13:00 2015-03-20 04:00 39
i have many rows(in our production table, thousands of rows are there) like above in a table and i want like below output(in output total 6rows only)
equipmentid downtimestartdate downtimeenddate dowtime
a3er 2015-03-15 02:00 2015-03-15 24:00 22
a3er 2015-03-16 00:00 2015-03-15 24:00 24
a3er 2015-03-17 00:00 2015-03-15 23:00 23
[code]...
Hello!
I have a problem. I want to know if the time which is needed for creating an index increases proportional to the amount of rows. example: if creating an index on a table which 10.000 rows takes 15 seconds. does creating an index on a table with 20.000 rows take 30 seconds , 40.000 rows 60 seconds and so on...
or does it take longer like 10.000 rows 15 second, 20.000 rows 40 seconds, 40.000 rows 80 seconds.
thx for your help!!
Filipe
How to design at database level such a way so that when I implement a SQL query that returns one hundred thousand rows only display 25 rows at the client (Web page at a time). How can I accomplish this?
Once I display first 25 rows then how do I bring next 25 rows and so on. Can it be done via paging or there are other techniques. However I asked to design this in the database level. I am using MS SQL Server 2008. Front end Visual Studio 2010. I use C# for coding.
I am using SQL SERVER 2008R2, not Denali, so I cannot use OFFSET FETCH Clause.
In my stored procedure, I am doing a SELECT INTO #tblTemp FROM... Working fine. This resultset is going to be used in an SSIS package which will generate a pipe-delimited .txt file... Working fine.
For recoverability sake, I am trying to throttle back on the commit chunks to 1000 rows per commit until there are no more rows. I am trying to avoid large rollbacks.
Q: Am I supposed to handle the transactions (begin/commit/rollback/end trans) when the records are being inserted into the temp table? Or when they are being selected form the temp table?
Q: Or can I handle this in my SSIS package for a flat file destination? I don't see option for a flat file destination like I do for an OLE DB Destination (like Rows per batch, Maximum insert commit size).
I want to save every query executed from a given software, let's say Multi Script for example, and save in a table query text, execution time and rows count among other possible useful information. Right now I've created a sp and a job that runs every 1 milliseconds but I can't figure out how to get execution time and rows count. Another problem with this is that if the query takes too long I end up with several rows in my table.
View 5 Replies View Related