SQL Server 2012 :: Tab Character In Query Results
Jul 9, 2015
I am getting a tab character at the end of my query fields, been trying various things to fix, such as using the replace function below but still i get the tabs!
select CAST(REPLACE(NAMEALIAS,CHAR(9),'')AS CHAR(40)) + ',' as PRODNAME,
CAST(REPLACE(ISNULL(GLOBALTRADEITEMNUMBER,0),CHAR(9),'')AS CHAR(18))as EANNO,LTRIM(cast(ISNULL(GLOBALTRADEITEMNUMBER,0) as char(18))) as KONSEAN
,LTRIM(CAST(I.ITEMID AS CHAR(8))) AS PRODCODE,'00' from INVENTTABLE I
LEFT JOIN INVENTITEMGROUPITEM IG ON I.ITEMID = IG.ITEMID
[Code] ....
Results:
Gießanzeiger groß ,04260376730475 04260376730475 730475 00
Gießanzeiger klein , 04260376730352 04260376730352 730468 00
Gießanzeiger klein , 04260376730468 04260376730468 730468 00
View 9 Replies
ADVERTISEMENT
Feb 11, 2008
I have an issue with some data that has a leading ASCII char 160 (the "a" with the accent mark) but it shows in query results as a space.
... where customername like char(160) + '%'
returns 2 rows but shows the customer name with a leading space. How would I change the collation or do otherwise to get this character to display correctly in the results?
Thanks!
-Dave
View 6 Replies
View Related
Jan 28, 2014
These separate COUNT queries are very fast:
SELECT COUNT(id) as viewcount from location_views WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357
SELECT COUNT(id)*2 as clickcount FROM extlinks WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357
But I want to add the COUNT statements, so this is what I did:
select COUNT(vws.id)+COUNT(lnks.id)*2 AS totalcount
FROM location_views vws,extlinks lnks
WHERE (vws.createdate>DATEADD(dd,-30,getdate()) AND vws.objectid=357)
OR
(lnks.createdate>DATEADD(dd,-30,getdate()) AND lnks.objectid=357)
Turns out the query becomes immensely slow. There must be something I'm doing wrong here which results in such bad performance, but what is it?
View 7 Replies
View Related
Apr 19, 2014
I have written this sample query to search a full-text indexed table and return the results. If the word occurs more than once I want it to return as a new record and the results show a short summary of the location. I was using 'like', but the full table scans were really slowing it down. Can performance be improved for the following (The results returned by my query are accurate)
Query
DECLARE @searchString nvarchar(255);
DECLARE @searchStringWild nvarchar(275);
SET @searchString = 'first';
SET @searchStringWild = UPPER('"' +@searchString+ '*"');
SELECT id, '...' + SUBSTRING(searchResults.MatchedCell, searchResults.StartPosition, searchResults.EndPosition - searchResults.StartPosition) + '...' as Result
[Code] ....
View 2 Replies
View Related
Mar 13, 2014
Previous to 2012, grid results from SSMS queries pasted into Excel beautifully and were easy to format.in 2008, The results from this query pasted into a spreadsheet with the query contained in a single Excel cell:
(sample - not the whole script)
select top 300
Avg_CPU_Time
,Total_Physical_Reads
,convert(datetime,Last_execution_time) as Timestamp
,Stored_Procedure
,Query_text
from dbadmin.dbo.History_CPU_IO_ByQueryAndSP
This was wonderful. I could deliver to developers wonderfully tidy reports on query resource usage with timestamps along with which stored procedure the queries came from.
Can't do that in SSMS 2012. Try it. It's a disaster. The Query_text, when pasted, spreads across multiple Excel columns, including the ones designated for other data. The result is totally unreadable.how to make the query_text stay in it's own cell? I've tried converting query_text to varchar and ntext. Same results.
View 9 Replies
View Related
Jun 15, 2015
I have to use a Group By in Sub query that will give me my desired results.
SELECT count(hc), CS, S_W, (SELECT COUNT(Reg_Temp)
FROM CS_S
WHERE Reg_Temp = "Reg") as [RegOnly]
FROM CS_S
GROUP BY S_W, CS
ORDER By CS, S_W
The query I have above counts ALL the Reg, I'd want it to give only the count that I'd get by using Group By clause i.e. S_W and CS.
View 8 Replies
View Related
Mar 3, 2014
I work for a school district and new requirement we were just given for scoring some student scores.
Everything will currently be in one table to keep track of students test scores for various things. This table will contain students information and a student will be shown more than once in the table. The Student ID is what we will key off of to find the multiple instances of the student. The table contains the following columns: studentName, StudentId, teacherName, focus1, controllingIdeas1, reading1, development1, organization1, conventions1, and contentUnderstanding1. All of the columns with a 1 at the end will be numeric values with possible decimal values.
What we need to be able to do is some how perform a search for these multiple entries of each student and when found, average the 2 scores for each 7 test categories. The result needs to be a single line for each student that gives the student name, student id, and the 7 test category averages exported to an csv file.
View 9 Replies
View Related
Jul 16, 2014
What is the best way to send emails with query results as attachment?
There is a stored proc sp_send_dbmail but the formatting is not very good.
View 3 Replies
View Related
Mar 20, 2015
I am trying to write a query that gives me the personal records from speed skaters on e.g. the 500 mtrs. I do this with the query:
SELECT cdsDistance AS Distance
, prsFirstName
, prsLastName
, min(crtFinalTime) AS MinTime
FROM tb....... INNER JOIN etc..
GROUP BY cdsDistance, prsFirstName, prsLastName
ORDER BY min(crtFinalTime)
In itself this works fine. However, there are complicating factors. Sometimes a speed skater has multiple PRs, meaning the he/she has the same fastest time more than once.
If these times are achieved on multple days, the 1st date is the official PR. (meaning: "Min of racedate")
If they are raced on the same day the 1st race is the PR (meaning: "Min of distancenumber")
Changing the code to:
SELECT cdsDistance AS Distance
, prsFirstName
, prsLastName
, MIN(crtFinalTime) AS MinTime
, MIN(cdsStartDate) AS RaceDate
, MIN(cdsDistanceNumber) AS DistanceNumber
FROM tb.......
GROUP BY cdsDistance, prsFirstName, prsLastName
ORDER BY min(crtFinalTime)
This gives me the wrong outcome because it gives me the "MIN" of every field, and they are not necessarily on the same row.
An option would be to calculate min(crtFinalTime), if for a person there is more than 1 result, calculate min of date, and then (if there is still more than 1 row) min of distancenumber.
Seems complicated, and I have the feeling there must be a better way (apart from: how to get this code)
Stacking subqueries in the FROM statement seems like a option be costly (time wise). There are more than 10 million rows (and growing) to run through.
As an example a few times:
DistanceFirst nameLast name Time Date Distance nr.
500 Yuya Oikawa 34.49 201311155
500 Yuya Oikawa 34.49 201311153
500 Yuya Oikawa 34.49 201311172
Yuya has 3 best times (34.49), 15-11-2013 is the 1st date, then distance nr 3 is the 1st distance raced. Therefore the 2nd row is the only row I would like to get in my endresult.
View 4 Replies
View Related
Oct 28, 2015
I have created a table(T1) from select query result, that Select query is parameterised. Now I need to update the select query table(T1) based on the result every time.
Below is my Query:
ALTER PROCEDURE [dbo].[RPT_Cost_copy]
SELECT MEII.*, SIMM.U_SP2DC, UPPER(SIMM.U_C3C2) AS GRP3,sb.cost, PREV.Z1, PREV.Z3, SB.Z2, SB.Z4,SIMM.U_C3DC1 AS FAM
INTO T1
FROM
(SELECT a.meu, a.mep2, SUM(a.mest) as excst
FROM mei as A WHERE a.myar=@yr and a.mprd=@mth AND LTRIM(A.MCU) <> '' AND LTRIM(A.MRP2) <> ''
[code]....
View 2 Replies
View Related
Oct 31, 2014
Consider following script
create table data_set (id int primary key, col1 varchar(10))
go
insert into data_set values (1,'a'), (2,'b'),(3,'c'),(4,'d'),(5,'a'),(6,'b'),(7,'e'),(8,'f'),(9,'a'),(10,'a')
select * from data_set
I tried this below
Declare @child_ids int
@col_val varchar
@count int
select @child_ids, @col_val, @count, count(col1) as records from data_set
group by col1
order by col1
But I want my output like below .....
View 2 Replies
View Related
Nov 26, 2014
possible to send Pivot query results as automated database email ?
View 3 Replies
View Related
Sep 1, 2015
I wonder if it is possible to run a stored procedure and save the results directly to a file in a predetermined directory
As an example I have created a (simple) stored procedure:
USE CovasCopy
GO
CREATE PROCEDURE spTryOut
(
@LastName as NVARCHAR(50)
, @FirstName AS NVARCHAR(25)
[Code] ....
What I would like to add is a (or more?) lines that save the results in a file (csv/txt/tab?)
The name I would like the file to have is "LastName, FirstName, Date query ran, time (HHMMSS?) query ran"
The directory: D:SQLServerResults
View 9 Replies
View Related
Dec 19, 2014
I need to build a query, or use a function to removing alfabetic Character from a string.
My string is
WI14000323-0003
The value i want to keep is : 14000323-0003.
I try using this SELECT:
SELECT STUFF(Upper(z.lote), PATINDEX('%[A-Z]%', Upper(z.lote)), 2, '') from mytable
but in this case i have only 2 letters WI, but in the future i dont know if is only 2 letters, 1 letter or more, then is not what i pretend.
View 2 Replies
View Related
Oct 18, 2015
How to write a function to remove duplicated characters only if they come in sequence.
Examples
darrk should return dark
eeagle should return eagle
redd should return red
corner should corner as it is as the r's are not in sequence.
View 9 Replies
View Related
Mar 29, 2015
I'm using OpenRowSet to import about 30 columns from a csv file with 190 columns with a format file. Ultimately, I want to put this in an SSIS Package. I am receiving the following error when trying to import date and decimal info.
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 990, column 64 (TOTALSALES). There are several similar errors. I looked at this line and it is 17873.34 so I am not seeing the problem. Every value in the column is either 0 or a 2 digit decimal value. If I change the SQL Column and format file to to NVARCHAR, it imports fine.
The existing format file and SQL Column looks as follows. There are multiple errors referring to different columns and all of them seem to be valid decimals. I am having the same issue with date fields that exist in the csv as 20130521. If I bring it in as text, it is fine.
<FIELD COLLATION="SQL_Latin1_General_CP1_CI_AS" MAX_LENGTH="12" TERMINATOR="," xsi:type="CharTerm" ID="64"/>
<COLUMN xsi:type="SQLDECIMAL" SCALE="2" PRECISION="15" NAME="TOTALSALES" SOURCE="64"/>
The SQL Column is defined as Decimal ((15,2), NULL))
I created a small csv file with representative decimal, date, integer and NVarchar fields and it imports into SQL fine as decimal and date info. The SQL Query used is pretty simple. Ultimately, I am planning to create a package that imports this data and joins to a production table based on values in the csv file. It will either update existing values in a Production Table or insert New Values
INSERT INTO Import.dbo.test1
SELECT *
FROM OPENROWSET(BULK 'C:ShareImport.csv',
FirstRow=2,
FORMATFILE='C:ShareImport.xml'
) AS t1;
I am assuming there is bad data in the csv file but I'm not sure how to identify it as my test file seems to bring in date values with a format of 20140923 and 2 digit decimal values and that is what exists in the line numbers being referenced. I've not used OpenRow Set for this purpose before. The only workaround I've found is to bring it all in as text and create additional fields so I can cast or convert the date values which I'd rather not do as this process seems to work in my small sample file.
View 1 Replies
View Related
Dec 4, 2013
How to get the required result in SQL 2012
Create table DBInfo (Path varchar (500))
Insert into DBInfo values('/Data Sources')
Insert into DBInfo values('/Data Sources/SALES')
Insert into DBInfo values('/PRODUCTION')
Insert into DBInfo values('/PRODUCTION/SERVICE')
Insert into DBInfo values('/PRODUCTION/SERVICE/MAINTENANCE')
Insert into DBInfo values('/PRODUCTION/SERVICE/LOGISTICS')
My Expected Output
Column1,Column2,Column3
Data SourcesNullNull
Data SourcesSalesNull
PRODUCTIONNullNull
PRODUCTIONSERVICENull
PRODUCTIONSERVICEMAINTENANCE
PRODUCTIONSERVICELOGISTICS
View 1 Replies
View Related
Jan 20, 2014
I have a problem where I want to write a function to remove recurring characters from a string and replace them with a single same character.
For instance I have the string '12333345566689' and the result should be '12345689'. In Oracle I could do this with "regexp_replace('12333345566689', '(.)1+', '1')", but in T-SQL the only solution I could think of is something like this:
DECLARE @code NVARCHAR(255)
SET @code = '12333345566689';
SET @code = REPLACE(REPLACE(REPLACE(@Code, '1', '~1'), '1~', ''), '~1', '1');
and repeat this for 2 - 9. But I'm sure there is a more elegant version for this in SQL Server 2012.
View 9 Replies
View Related
Apr 11, 2015
In my staging table I am having data like below
ABL¯ABL¯0¯0¯ABL¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ABQ¯480¯825¯DLS¯AMA¯ABQ¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ACD¯808¯1255¯DLS¯ELP¯TCS¯PHX¯ACD¯ ¯ ¯ ¯ ¯
ABL¯ADE¯1256¯471¯DLS¯AMA¯ABQ¯LSV¯ADE¯ ¯ ¯ ¯ ¯
ABL¯AFT¯1140¯1744¯DLS¯LAX¯FON¯AFT¯ ¯ ¯ ¯ ¯ ¯
ABL¯AHM¯1178¯1637¯DLS¯LAX¯AHM¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ALB¯1769¯1825¯DLS¯WIL¯ALB¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ALE¯1041¯1150¯DLS¯ALE¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯
Now I want to find the Number of times a '¯'character appears in a string. I should get output 14
View 2 Replies
View Related
Apr 13, 2007
I have the following problem, I need to create the TotalsY and TotalsN values.
Can anybody give some direction on how to count these character values.
Column1 Y
Column 2 Y
Column 3 N
TotalsY 2
Totals N 1
Column1 =First(Fields!NOB_Pickup_L_D_T.Value)
Column 2 =First(Fields!NOB_Return_L_D_T.Value)
Column 3 =First(Fields!NOB_New_L_D_T.Value)
TotalsY 2 <I Dont Know>
Totals N 1 <I Don't Know>
Thanks,
Rick
View 1 Replies
View Related
Jun 11, 2014
I have an database that is housing a path used to locate an external file. This application was written many years ago and I am now trying to bring the files into the database as a VARBINARY.
The table is holding the path like this "/folder/folder/file"
I am trying to convert that path to "folderfolderfile"
In my Select statement I have
SELECT ProdID, REPLACE (PATH, /, ) FROM dbo.blahblah
The problem is that I can't figure out to make SQL understand that "/" is the character I want to replace.
View 2 Replies
View Related
Mar 13, 2014
below is my statement to get data from one column (VARCHAR) from table SUPPLY_ITEM_01
SELECT
@@SERVERNAME as ServerName,DB_NAME() AS DatabaseName,
SUM(CASE WHEN CHARINDEX (CHAR(013) , supplydetail) > 0 THEN 1 ELSE 0 END) AS TotalCHAR013,
SUM(CASE WHEN CHARINDEX (CHAR(012), supplydetail ) >0 THEN 1 ELSE 0 END)AS TotalCHAR012,
SUM(CASE WHEN CHARINDEX (CHAR(010), supplydetail ) >0 THEN 1 ELSE 0 END) AS TotalCHAR010,
SUM(CASE WHEN CHARINDEX (CHAR(009),supplydetail ) >0 THEN 1 ELSE 0 END) AS TotalCHAR009
FROM
[code]...
I need to get result from all the tables and all the columns which has bad data including schemaName, table name and column name in result.
View 9 Replies
View Related
Aug 7, 2014
I have created a view thats pulling data from two different tables to combine them into one report.
table 1 lists the client code and table 2 lists the client partner and they're linked by a variable.
When running the report the result shows the client codes with their respective partner however any client codes that didn't have a partner are not displaying in the report and I need all client codes to be displayed even if there's no partner.
Is there a way I can make this display all results and if the client partner doesn't exist for it to still display as 'Null' for the partner but still display the client code?
Script:
SELECT TOP (100) PERCENT C.cltCode AS ClientCode, C.cltSortName AS SortName, C.cltTerminationDate AS [Term date], dbo.vcltAttrib6.ainTVal AS Department,
C.objInstID AS ClientID
FROM dbo.cdbClient AS C INNER JOIN
dbo.vcltAttrib6 ON C.objInstID = dbo.vcltAttrib6.ainObjectInstID
GROUP BY C.cltSortName, C.cltTerminationDate, dbo.vcltAttrib6.ainTVal, C.objInstID, C.cltCode
ORDER BY ClientID
View 2 Replies
View Related
Sep 11, 2014
I have a simple example of what I am trying to do. Here is some code to make a quick table to demonstrate:
create table #students
(
lastname varchar(50)
,firstname varchar(50)
,address1 varchar(50)
[Code].....
I want to select all the records, and them them be in alphabetical order first by lastname, then by firstname, then by address. HOWEVER, and this is the tricky part, I want to group names together that have the same address. So, in this example, I want the results to be in this order:
HallC6309 N Olive
HallP6309 N Olive <---- grouped with the C record because they have the same address
HallE5488 W Catalina <---- back to alphabetical by first name
HallJ7222 N Cocopas
View 7 Replies
View Related
Jun 5, 2014
I have a lot of rows of hours, set up like this: 0745, 0800, 2200, 1145 and so on (varchar(5), for some reason).
These are converted into a smalldatetime like this:
CONVERT(smalldatetime, STUFF(timestarted, 3, 0, ':')) [this would give output like this - 1900-01-01 11:45:00]
This code has been in place for years...and we stick the date on later from another column.
But recently, it's started to fail for some rows, with "The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value".
My assumption is that new data being added in is junk. If I query for these values and just list them (rather than adding a column to convert them also) that's fine, of course. I've checked all the stuffed (but not yet converted - so 11:45 rather than 1145) output to see if it ISDATE(), and it is. There are no times with hours > 23 or minutes greater than 59 either.
If I add the CONVERT in, we see the error message. But here's the oddity, if I place all of the rows into a holding table, and retry the conversion, there is no error. It's this last bit that is puzzling me. Plus I can't see any errors in the hours data that would cause a conversion problem.
I've put the whole of this into a cursor to try to trap the error rows too, but all processes fine. Why would it fail if NOT in a cursor?
View 9 Replies
View Related
Sep 5, 2014
I have a view I've created which displays client sortname, partner and date added which displays 7 results.
When I add another table to this view to display the Industry it then only gives me 4 results as the other 3 results have no Industry instead of giving me the 7 results and showing the Industry column as empty for the other 3.
Is there a way I can make it show all 7 results and havethe column where the industry is empty display the other results instead of not displaying any results at all for them?
Script:
SELECT dbo.cdbClient.cltSortName AS ClientName, dbo.vcltAttrib4.ainTVal AS ClientPartner, dbo.vcltAttrib422.ainDVal AS [Date Added],
dbo.cdbAttribInst.ainTVal AS Inudstry
FROM dbo.cdbClient LEFT OUTER JOIN
dbo.cdbObject ON dbo.cdbClient.cltCategoryID = dbo.cdbObject.objID LEFT OUTER JOIN
[Code] ....
In the above script the cbdAttribInst table has the Industry column I need which is 'ainTVal'...
View 8 Replies
View Related
May 26, 2015
I can find many examples of loading DBCC results into tables. They all begin with a create table statement defining the results. My question is , other than trial and error, is there a way to determine what data types will be returned. Sure you can say that first element looks like an integer, but is it really a bigint, and that text string can be varchar(max) but will char(2) work.
I'm not looking for an answer for a specific DBCC function, but rather a generic way I can determine the characteristics of any DBCC result set.
I tried
SELECT *
INTO #tmp
FROM OPENROWSET('SQLOLEDB',
'Server=ray;Trusted_Connection=Yes;Database=Ed_sandbox',
'Set FmtOnly OFF; DBCC loginfo WITH tableresults ')
but I got back
Msg 11527, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'DBCC loginfo WITH tableresults' does not support metadata discovery.
View 1 Replies
View Related
Sep 29, 2015
I have a table that has multiple transactions for stock items.
This table holds all records relating to items that are inducted onto the system and there movement. For each stock item i am interested in getting the drop destination, if it has one, and only when it follows the sequential order of "Inducted>OnTransport>Dropped" (this sequence isn't always the case). Also note the CreatedDate for the Inducted and OnTransport records for the valid sequences are always the same. Below is a valid sequence for a stock item so i would want to return 'Lane01' for the Destination of this occurrence of the stock item, if this item didn't have a valid drop location then destination would be blank. Also note each stock item can be inducted more than one time per-day.
I think i have managed to build the below sql but it will only do one item at a time, so would have to wrap it in a function. Is there a way of writing a set based select statement that gets all the inducted items and for the ones that do follow the "Inducted>OnTransport>Dropped" return the destination it was dropped at? I've attached scrips below:
DECLARE @StockItemID nVarchar(128)
DECLARE @CreateDate DATETIME
Set @StockItemID='8cbe17da-6079-4170-b27a-41c0d38830f6'
Set @CreateDate = CAST('2015-08-31 13:52:39.890' AS datetime)
[Code] ....
View 9 Replies
View Related
Nov 2, 2015
I have written a SQL statement.There is a table called customer.It contains all customer data with customerid as PK.There is another table called logs and it contains customerid as foreign key and it contains a field to keep more than 90 days older user accounts.That field name is "Checked"
What I need get all records from these 2 tables and remove/hide more than 90 days older customers from record set.See my illustration.
I have written this code but I dont understand how to remove more than 90 days older user from result (because customer table doesnt contain a record called "Checked")
SELECT *
FROM [dbo].[Customers],[dbo].[VIESLog] WHERE [dbo].[VIESLog].[Checked] < DATEADD(day, -90, GETDATE())
View 6 Replies
View Related
Nov 25, 2014
I have a simple update statement (see example below) that when runs, I expect to see the number of records updated in the Results tab. This information shows up in the Messages tab; however, what is displayed in the Results tab is (No column name) 40. From where the 40 is being generated. I have tried restarting SSMS 2012, restarting my computer, turning NOCOUNT on and off.
"UPDATE TableA
SET Supervisor = 'A123'
WHERE PersonnelNumber = 'B456'"
View 4 Replies
View Related
Dec 1, 2014
I can create a string into a local "@" variable. It can include a date/time, and text for my timing testing.
I need to be able to send that string to the results window where table output goes, rather than to the message window where a PRINT statement goes.
How do I do that?
View 2 Replies
View Related
Mar 5, 2015
I have a lookup table, as below. Each triggercode can have several service codes.
TriggerCodeServiceCode
BBRONZH BBRZFET
BBRONZH RDYNIP1
BBRONZP BBRZFET
BCSTICP ULDBND2
BCSTMCP RBNDLOC
I then have a table of accounts, and each account can have one to many service codes. This table also has the rate for each code.
AccountServiceCodeRate
11518801DSRDISC -2
11571901BBRZFET 5
11571901RBNDLOC 0
11571901CDHCTC 0
17412902CDHCTC1 0
14706401ULDBND2 2
14706401RBNDLOC 3
What I would like to end up with is a pivot table of each account, the trigger code and service codes attached to that account, and the rate for each.
I have been able to dynamically get the pivot, but I'm not joining correctly, as its returning every dynamic column, not just the columns of a trigger code. The code below will return the account and trigger code, but also every service code, regardless of which trigger code they belong to, and just show null values.
What I would like to get is just the service codes and the appropriate trigger code for each account.
SELECT @cols = STUFF((SELECT DISTINCT ',' + ServiceCode
FROM TriggerTable
FOR XML PATH(''), TYPE
).value('(./text())[1]', 'VARCHAR(MAX)')
,1,2,'')
[Code] ....
View 1 Replies
View Related
Jun 19, 2015
I am trying to calculate the time difference between the value in the row and the min value in the table. So say the min value in the table is 2014-05-29 14:44:17.713. (This is the start time of the test.) Now say the test ends at 2014-05-29 17:10:17.010. There are many rows recorded during that start and end time, for each row created a time stamp is created. I am trying to calculate the elapsed time and have it as a row in the results.
min(timestamp) - timestamp(value in row) = elapsed time for that test
where Channel = '273'
Here is the table DDL
CREATE DATABASE SpecTest;
USE SpecTest
GO
CREATE TABLE [dbo].[Spec1](
[Spec1ID] [int] IDENTITY(1,1) NOT NULL,
[Channel] [int] NOT NULL,
[Code] ....
Here is some dummy data to use
INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)
VALUES(273, '2014-05-29 14:44:17.713', 800, '-64.91');
INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)
VALUES(273, '2014-05-29 15:05:09.507', 800, '-59.11');
INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)
[Code] ....
Example desired results (I hope the formatting works)
Channel | Timestamp | Lambda | Power | Elapsed_Time
______________________________________________________________
273 | '2014-05-29 14:44:17.713', | 800, | '-64.91' | 0
273 | '2014-05-29 15:05:09.507', | 800, | '-64.91' | 00:20:51
273 | '2014-05-29 15:26:00.520', | 800, | '-64.91' | 00:41:42
273 | '2014-05-29 16:28:34.213', | 800, | '-64.91' | 01:44:16
273 | '2014-05-29 16:49:25.853', | 800, | '-64.91' | 02:05:08
273 | '2014-05-29 17:10:17.010', | 800, | '-64.91' | 02:25:59
View 9 Replies
View Related
