Timestamp TimeSpan - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Timestamp TimeSpan

Author

Topic

MKz71
Starting Member

30 Posts

Posted - 2011-09-20 : 07:44:03

I am pulling in certain data and would like to reduce my code by having SQL do all the work for me in a Stored Procedure. It is currently pulling in many records, but if it does what I want then it will only yield a single line of results. Below is my Stored Procedure:


ALTER PROCEDURE dbo.spGetTPHCurrentPipe
	@shiftStart DATETIME = NULL
AS
	BEGIN
		SELECT dtTimeStamp, PipeGauge, PipeDiameter, PipeWeight
		FROM tblPipeCount
		WHERE (PipeGauge = (SELECT PipeGauge FROM tblPipeCount WHERE dtTimeStamp = (SELECT TOP(1)dtTimeStamp FROM tblPipeCount ORDER BY dtTimeStamp DESC)))
			AND (PipeDiameter = (SELECT PipeDiameter FROM tblPipeCount WHERE dtTimeStamp = (SELECT TOP(1)dtTimeStamp FROM tblPipeCount ORDER BY dtTimeStamp DESC)))
			AND dtTimeStamp BETWEEN @shiftStart AND getdate()
		ORDER BY dtTimeStamp ASC
	END
	RETURN

I know it isn't the cleanest looking code, but it gets the job done. If you noticed, the 'PipeGauge' and 'PipeDiameter' columns will be the exact same data for every record pulled. The only data that will be different is the 'dtTimeStamp' and 'PipeWeight'. Also, as you can see this will be ordered by the 'dtTimeStamp' column too. Is there any way to return the following:


SELECT DATEDIFF(FIRST_RECORD_dtTimeStamp,LAST_RECORD_dtTimeStamp), PipeGauge, PipeDiameter, SUM(PipeWeight) / 2000 AS PipeData

I am not very good with SQL and I hope this will just help give an idea of what I am trying to accomplish. The TimeSpan between the first and last record of 'dtTimeStamp', the 'PipeGauge', the 'PipeDiameter', and the sum of all the pipe weights divided by 2000. Is this possible, can anyone help me?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 08:09:08

may be this?


SELECT DATEDIFF(ss,MIN(dtTimeStamp),MAX(dtTimeStamp)), MIN(PipeGauge), MIN(PipeDiameter), SUM(PipeWeight) / 2000 AS PipeData 
FROM table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MKz71
Starting Member

30 Posts

Posted - 2011-09-20 : 08:36:17

Ok. This executes without error. It also has the potential to return a NULL for all fields, which is good that I see that now, so I can put in some error checking in my code. Thanks for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 10:51:15

no probs. wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources