Transact SQL :: Getting Time Difference Between Same Column In Different Rows

Jun 21, 2015

I 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

View 5 Replies


ADVERTISEMENT

Transact SQL :: Split Rows Based On Time Difference More Than 1day

Aug 5, 2015

equipmentid 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]...

View 2 Replies View Related

Transact SQL :: Total Difference Time In Last Column?

May 23, 2015

I have the below query

DECLARE @GivenDate DATE='2015-05-13'
create table #table (LedgerID int,AttDate Date, checkedtime time,checkedtype varchar(1))
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','09:01:48.0000000','I')
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','13:05:52.0000000','O')
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','14:10:25.0000000','I')

[code]....

the result is like below

i need 'TotalDiffernceTime' column as new column (OUT1-IN1)+(OUT2-IN2).

i am using SQLServer 2008 R2

View 8 Replies View Related

Transact SQL :: Getting Time Difference In Hours And Minutes?

Jul 10, 2015

Currently my script is using the below mentioned query to find the time difference.

DATEDIFF(HH,DATEADD(SS,hcreacion,fcreacion) ,DATEADD(SS,hcerrar,fcreacion))

If there is 1 hr 30 minutes time difference, I am getting 2 hours as output. But we need 1.30 as output. is there any way to achieve this?

View 14 Replies View Related

Transact SQL :: Inventory Difference Between Rows Between Stores

May 8, 2015

how to measure a change in inventory over various stores.  My sql2008R2 express db gets a new row of data everyday from each store(about 40 stores) for a single product stock count "OnHand" and if there is any new stock on order.  When the new stock arrives it is added to the "OnHand" count.   I want to measure the delta change per day,per store.  I'm stuck on how to separate the stores and how to query the delta of stock.My data base looks like this
                               
TimeStamp Store
OnHand OnOrder
2015/04/22 18   1 - Concord
12
     0
2015/04/23 11   1 - Concord
11
 
[code]....

View 17 Replies View Related

Transact SQL :: Pivot Columns Into Rows With Difference

Sep 22, 2015

Here is the table:

empid lastname firstname title titleofcourtesy ModifiedOROriginal

1 Davis1 Sara CEO Ms. Modified
1 Davis Sara CEO Ms. Original

We need an output like this:

empid
1
1

lastname
Davis1
Davis

firstname
Sara
Sara

title
CEO
CEO

titleofcourtesy
Ms.
Ms.

ModifiedOROriginal
Modified
Original

View 2 Replies View Related

Transact SQL :: How To Make A Report On Difference Between Rows

Nov 16, 2015

Given a table that has three columns that together create a key and two columns that together define NameValue pairs, how can the difference between instances of values be calculated and displayed?One table is used to contain periodic dumps of data from various sources. Because this is an early stage of development for the software project instead of having explicit columns that contain specific data the table contains name/value pairs. This allows the software to export anything to the database table. When this data is imported, earch row shares the same key (three columns containing a machine type, serial number and a timestamp), a name that identifies the data and a string that contains the actual data. While this arrangement makes it trivial to support the addition of any data that the software developers want to export, it makes it less obvious as to how to generate reports.Let's make an example. Lets assume that there are two vending machines, each of which has just 3 snacks and each of which generates two separate reports.

Type Sn Timestamp Name Value
A 1 2015-08-15 12:34 Snick 5
A 1 2015-08-15 12:34 Mars 10
A 1 2015-08-15 12:34 MandM 0B 2 2015-08-15 15:31 Snick 1
B 2 2015-08-15 15:31 Mars 9
B 2 2015-08-15 15:31 MandM 0A 1 2015-08-21 09:12 Snick 11
A 1 2015-08-21 09:12 Mars 18

[code]...

So, the names of the values become the report's columns. The reports are sorted by timestamp, then by type, then by serial number.The value associated with the previous row that shares the same name is subtracted from the value of the next row in which the same name occurs and that becomes the displayed value in the report.

View 8 Replies View Related

SQL 2012 :: Calculate Time Difference For Consecutive Rows?

Jul 2, 2015

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]....

View 9 Replies View Related

SQL Server 2008 :: How To Find Time Difference Between Two Rows Of Record

Nov 6, 2015

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

View 6 Replies View Related

Transact SQL :: Selecting Rows As Column Name And Other Column Values As Rows

Jun 25, 2015

I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.

View 28 Replies View Related

SQL Server 2008 :: Time Difference With Dates In Same Column

Mar 17, 2015

How do I find the time difference when the dates are in one column? I need to find hours and minutes between each row.

CREATE TABLE #Time ([TimeStamp] DATETIME, TimeDiff INT)
INSERT INTO #Time (TimeStamp)
VALUES ('2014-09-02 07:51:02.810'), ('2014-09-02 07:48:09.567'), ('2014-09-02 08:37:09.647')
, ('2014-09-02 16:16:42.593'), ('2014-09-02 08:06:13.387'),('2014-09-02 14:32:00.113')
DROP TABLE #Time

View 6 Replies View Related

SQL Server 2008 :: Get Time Difference Of Char Datatype Column Value

May 29, 2015

How can I get time difference of the following record :

STARTTIME ENDTIME
3:30 PM 4:30PM
7:30 PM 8:30PM

I have tried it by below query,

SELECT CONVERT(TIME,STARTTIME,108) - CONVERT(TIME,ENDTIME,108) FROM BATCH_MASTER

but it gives following error message

[color=red]Operand data type time is invalid for subtract operator.[/color]

View 6 Replies View Related

SQL Server 2008 :: Calculated Time Difference Column In Create Table

Jul 14, 2015

How to include a time difference column using 2 other date columns during creation of a table ?

The requirement is to create a table and include the following columns:

1.Downtime start date & time
2.Downtime end date & time
3.Downtime Duration in hh:mm (calculated date difference between column 1 and 2)

View 3 Replies View Related

Transact SQL :: Find Unique Rows In Column That Are Associated With Another Field In Another Column?

May 1, 2015

I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1.  To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique. 

Table1

GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2

The result of the query would only have PID1 because it has two unique GUID's. PID2 would not be listed has it has the same GUID3 in each row.

Result:

PID1 

View 2 Replies View Related

Transact SQL :: Calculate DateTime Column By Merging Values From Date Column And Only Time Part Of DateTime Column?

Aug 3, 2015

How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?

View 5 Replies View Related

Transact SQL :: Select 1000 Rows At A Time From / Into A Large Temp Table?

May 12, 2015

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).

View 6 Replies View Related

Transact SQL :: Time Column With Order Wise

Nov 17, 2015

I have a table like below

CREATE table #TempTable (ID integer,CTime time(7),CType Varchar(1))
insert into #TempTable VALUES (1001,'16:50:05.0000000','I')
insert into #TempTable VALUES (1001,'13:27:49.0000000','O')
insert into #TempTable VALUES (1001,'20:44:00.0000000','O')
insert into #TempTable VALUES (1001,'21:12:00.0000000','O')

I need result like below screen shot 

here 'I' stands for In,and 'O' for Out, in this example first In time not available

View 7 Replies View Related

Transact SQL :: Sum Time Column After Pivot Query

May 18, 2015

i have a table like below,

CREATE TABLE #ATTTABLE
(
Name VARCHAR(20),
AttDate DATE,
PresntTime TIME

[code]....

and then i pivot table by date as column wise using the below query and also displays total time by rowswise

SELECT t1.*, t2.Total
FROM (
SELECT  name,[2015-08-01],[2015-08-02]
FROM (
SELECT  name, AttDate,PresentTime 

[code]....

now what i need is to display sum of time at last row as well, means total time of against date

View 16 Replies View Related

Transact SQL :: Based On One Column Need To Cascade Rows One By One

Sep 23, 2015

Here's the my structure and data as follows

>
SELECT tranno ,mrno medrecno ,createdon,createdat,no_of_trans nooftrans 
FROM mytab WHERE mrno = 'MR1514' and tranno = 1111
ORDER BY no_of_trans
tranno medrecno
createdon createdat
nooftrans

[Code] ...

Now requirement is : -

tranno medrecno
createdon createdat
nooftrans     tranno medrecno
createdon createdat
nooftrans

[Code] ....

View 4 Replies View Related

Transact SQL :: How To Repeat Rows Based On Column Value

Apr 30, 2015

I have a query that gives me a result with a column value for example 4.

I now want to repeat this row 4 times with a new column that calculated from 1 - 4.

Or when column value is 3 I want to repeat row 3 times with new column name 1-3

View 7 Replies View Related

Transact SQL :: Combine Multiple Rows In A New Column

Jul 16, 2015

I have the table below and like to combine the rows to create a single link row in a new column. The rows should be combined based on the job number columns which is the same for the rows to be combined.

DECLARE @M31
( M31_SQL_ID INT
,JOB_NUMBER INT
,LINE_NUMBER INT
,WORKS_DESC VARCHAR)

[Code] ...

Output should be as below

219242
16/7/15 called tenant and she thought we would just fix for free - advised her I can get a quote how ever she may have to pay - she will call back  

219245
16/7/15 called tnt said no report number. Said she will speak with her husband and call back with her decision and 16/07/15 the work order was sent to agent ...

View 3 Replies View Related

Transact SQL :: How To Convert Row To Column With Rows Dynamic

May 21, 2015

I want to convert row to columns with rows dynamic. I have two table, I'll join them and get data. See at

Ok , I try convert rows to columns with PIVOT . i want the result as

GROUP_MOD_IDADDEDITDELETE
G02101

CREATE PROC GET_FUNC @GID CHAR(5)
AS
BEGIN
SELECT FUNCTION_MOD_NAME

[Code] ....

But it's error

Msg 156, Level 15, State 1, Procedure GET_FUNC, Line 13
Incorrect syntax near the keyword 'FOR'.

Note: Type of ALLOW is bit and rows FUNCTION_MOD_NAME is dynamic

View 2 Replies View Related

Transact SQL :: Converting Rows To Column And Getting Data

Jul 22, 2015

I have a sql table like this

event_id timestamp event_name event_score
1 3000 alarm 0.95
10 3500 alarm 0.85
5 4000 alarm 0.93
20 4200 alarm 0.87
30 5000 alarm 0.87
30 8000 alarm 0.97
40     9000        alarm       0.98 
13        9700        alarm       0.98

And I am expecting output like this(With multiple rows)

event_id1 event_id2 event_id3 event_name event_score1 event_score2 event_score3
1 10 5 alarm 0.95 0.85 0.93
30 40 13 alarm 0.97 0.98 0.98

Also I have certain conditions like the timestamp difference between event_Id1 and event_Id3 < 3600 eg 1 hour

View 6 Replies View Related

Transact SQL :: How To Set Case Sensitivity Of A Column In Design Time

Aug 3, 2015

With Sql Server Management Studio, while creating/modifying a table I want to specify one of its columns to store values in lower case only. Can it be done through the designer or by some other means?

N.B.: Using SQL Server 2005.

View 22 Replies View Related

Transact SQL :: Get Only Newly Inserted Rows By Datetime Column?

Aug 17, 2015

I have a TableA where data get inserted from Excel(IMPORT/EXPORT wizard)

TableA;
ID(identity)   Date (NOT NULL Defaulyt Getdate() )                          
 Name            Phone
1                   2014-06-17 17:28:21.190          
Nick              12345678910
2                  2014-05-17 17:28:21.190        
 Stan              00045678910
3                  2015-08-17 17:28:21.190        
 Kim                 11111678910
4                  2015-08-17 17:28:21.190          
Tom                NUll 

 3,4 are the rows i have inserted now , you can see by date, likewise i have 100,000 rows(old and new combination) and now the data from excel to TableA can be imported/exported daily , hourly, weekly basis.

Now i want to find out only the rows which are imported to tableA today, or hours back, or yesterday .....

 the reason is , 

Step1:get data from excel and import to tableA( this is a manual Step) and i know when the data is inserted  with exact date and time.

Step2: get newly inserted rows from TableA and pass them as Parameters in Stored Procedure.( i may run step 2 after 1 hour, or after  1 day or after 1 week ,but i want only rows that are inserted ) 

I tried with using where Datecreated, but did work.

View 4 Replies View Related

Transact SQL :: Keep Entire Columns Into 2 Rows When There Is A Comma In A Column

Nov 2, 2015

I have a table and one of the column have tab delimited value and I need to separate the tab delimited values and keep them in separate rows.

ID       Name                  State      Country
1       Scott, Ricky            NSW       AUS
2       Martin                   VIC         AUS
3    James, Peter,John     WA        AUS

ID column is not a primary key. I want the output columns to identify the comma (,) and put them in separate rows as below

ID       Name                  State      Country
1       Scott                     NSW       AUS
1       Ricky                     NSW       AUS
2       Martin                   VIC         AUS
3      James                     WA        AUS
3      Peter                      WA        AUS
3      John                       WA        AUS

How to strip the camma(,) in SQL?

View 3 Replies View Related

Transact SQL :: How To Generate Rows Certain Times With A Counter Column

Jul 10, 2015

How can I write it in SQL? I have a table  that column  A   has two values of   Product  and Cost...Where value of A is Product, I have to add 6 more rows, and add column b with values from 1 to 7 for each rows ( counter)Where value of A is Cost, I have to add 4  more rows, and add column b with values from 1 to 5 for each rows ( counter).

View 3 Replies View Related

SQL Server 2008 :: How To Split Time Column Values Into Rows

Jun 6, 2015

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

View 1 Replies View Related

Transact SQL :: Split Column To Rows Which Is Delimited Using Commas And Semicolons

Jul 9, 2015

All I have a situation where I need to split the column to rows which is delimited using commas and semicolons. Please find the below sample data.

Data in Tables

Test1, Test2, Test3
Test4;Test5;Test6

Desired output
Test1
Test2
Test3
Test4
Test5
Test6

Is there any way that I can get this output in SQL other than using the XML Conversion, since the data has some special characters in this.

View 7 Replies View Related

Transact SQL :: Arithmetic Calculation Between Two Rows And Result In Alias Column?

Jul 12, 2015

I am trying to compare Sales value of year 2015 with sales value of 2016 and the difference stored in alias column as Sales_growth for year 2016 , for year 2015 the alias column be as '1' similarly difference between margin of 2015 and 2016 and result stored in alias column as margin_rate in year 2016for 2015 as 1 but when there is no record for year 2015 and record present in 2016 for a given (month, SM,SG,CUST,SP) then the alias column sales_growth and margin_rate should be 100 

Formula for calculation

SGR = (sales(2015)-sales (2016)) / Sales_growth(2015)
SGR = (3456.05-3603.33) /3456.05 = -0.043
MR =( margin (2015)-margin( 2016) / margin(2015)
MR = (1059.24-1053.07)/1059.24= 0.006
DECLARE @T1 TABLE

[code]....

last record : as there is no record  for year 2015 and record present in 2016 for a given (month, SM,SG,CUST,SP) then the alias column sales_growth and margin_rate should be 100

View 18 Replies View Related

Transact SQL :: Strategy To Translate Column Data Into Distinct Rows

Aug 27, 2015

I am writing a query where I am identifying different scenarios where data changes between one week and the next. I've set up my result set in the following manner:

PrimaryID       SKUChange              DateChange         LocationIdChange        StateChange
10003             TRUE                       FALSE                  TRUE                          FALSE
etc...

The output I'd like to see would be like this:

PrimaryID        Field Changed          Previous Value      New Value
10003             SKUName                 SKU12345           SKU56789
10003             LocationId                 Den123               NYC987
etc...

The key here being that in the initial resultset ID 10003 is represented by one row but indicates two changes, and in the final output those two changes are being represented by two distinct rows. Obviously, I will bring in the previous and new values from a source.

View 3 Replies View Related

Transact SQL :: Check 2 Columns And Return Rows As Long As One Column Is Not Null

Oct 19, 2015

how to do a check for 2 columns. As long as there is data for at least one of the columns I want to return rows.

Example Data

create table test
(
ID int,
set1 varchar(50),
set2 varchar(50),

[code]....

View 4 Replies View Related

Transact SQL :: Adding A Column To A Large (100 Million Rows) Table With Default Constraint?

Apr 24, 2013

IF NOT EXISTS (SELECT TOP 1 1 FROM dbo.syscolumns WHERE id = OBJECT_ID(N'dbo.Employee) and name = 'DoNotCall')
BEGIN
ALTER TABLE [dbo].[Employee] ADD [DoNotCall] bit not null Constraint DoNot_Call_Default DEFAULT 0
IF ( @@ERROR <> 0 )
GOTO QuitWithRollback
END

It just takes a LOT of time in SQL Server Management studio. I have to cancel the query and cancelling takes a whole lot time. I am using SQL Server 2008.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved