October 2, 2007 at 6:59 am
Hi,
I have a requirement to calculate the difference between dates in a query. The query has been amended to concatenate date & time, I just need to work out the difference between B - A. Is this possible?
DateTimeDate/TimeQty
A24/07/2007 00:0014295024/07/07 14:29:50470
B24/07/2007 00:0017550023/07/07 17:55:0015
C24/07/2007 00:0013265624/07/07 13:26:5637
D24/07/2007 00:0015031024/07/07 15:03:10120
E24/07/2007 00:008184526/07/07 08:18:45108
F24/07/2007 00:0011213025/07/07 11:21:3026
G24/07/2007 00:0015294726/07/07 15:29:47938
H24/07/2007 00:0013175226/07/07 13:17:52973
Nick
October 2, 2007 at 7:10 am
El barto,
It is possible, however, in order for us to write specific sample queries for your specific table; you will need to provide the table structure/ DDL.
Regards,
Wameng Vang
MCTS
October 2, 2007 at 7:22 am
Hi,
Here's the table
CREATE TABLE
(
[SHIFT] [char] (6) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT [DF___OPERATOR__2A90068A] DEFAULT (''),
[END_DATE] [smalldatetime] NOT NULL CONSTRAINT [DF___END_DATE__23E308FB] DEFAULT ('1900-JAN-01'),
[END_TIME] [int] NOT NULL CONSTRAINT [DF___END_TIME__24D72D34] DEFAULT ((-1)),
[OPERATOR] [char] (6) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT [DF___OPERATOR__2A90068A] DEFAULT (''),
[DURATION] [int] NOT NULL CONSTRAINT [DF___DURATION__2B842AC3] DEFAULT (0),
[QUANTITY] [numeric](26, 8) NOT NULL CONSTRAINT [DF___QUANTITY__2E60976E] DEFAULT (0),
) ON [PRIMARY]
GO
Thanks,
Nick
October 2, 2007 at 8:13 am
So which fields or rows do you want to differnce of time between?
Are you looking for the difference between two columns or the difference between every row? Every pair of rows?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 2, 2007 at 8:48 am
Hi,
I would like to find the time difference between column B Eg 24/07/2007 17:55 and column A 24/07/2007 14:29.
Thanks,
Nick
October 2, 2007 at 9:29 am
El barto,
Base on the DDL provided, we still need more information.
Please provide some sample data.
Please provide your current select statement.
This will help reduce our time in providing a sample query to meet your needs.
Regards,
Wameng Vang
MCTS
October 2, 2007 at 12:19 pm
I still don't think you're providing quite enough information to make the determination, but here's a SWAG based on what you've provided:
SELECT DATEDIFF(mi,t1.[END_DATE],t2.[END_DATE]) AS MinutesDifferent
,t1.[OPERATOR]
FROM dbo.
t1
CROSS APPLY (SELECT TOP 1 t3.[end_date]
FROM dbo.
t3
WHERE t3.[Shift] = t1.[shift]
AND t3.[operator] = t1.[operator]
AND t3.[end_date] > t1.[end_date]
ORDER BY t3.end_date desc) AS t2
Assuming two rows for each operator within a shift, this will return the minutes difference between the end dates.
It's quick & off the top of my head, but more information may help to clarify an answer.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply