December 14, 2018 at 3:24 am
I'm looking at calculating the difference between two date fields in SQL (SQL Server) which may (or may not) overlap to another month.
I have a TimeFrom field and a TimeTo field and a status field. Both of the time fields are in datetime format and the status is numeric.
Here is an example of the dates:
+---------------------+---------------------+--------+
| TimeFrom | TimeTo | Status |
+---------------------+---------------------+--------+
| 2018-04-01 09:03:27 | 2018-07-15 10:11:12 | 12 |
+---------------------+---------------------+--------+
I need to be calculate the time between those dates but in the relevant month and I'm struggling to work this out myself.
Output would look something like:
+-----------+--------+---------------------+
| YearMonth | Status | Duration (hh:mm:ss) |
+-----------+--------+---------------------+
| 2018-04 | 12 | xx:xx:xx |
+-----------+--------+---------------------+
| 2018-05 | 12 | xx:xx:xx |
+-----------+--------+---------------------+
| 2018-06 | 12 | xx:xx:xx |
+-----------+--------+---------------------+
| 2018-07 | 12 | xx:xx:xx |
+-----------+--------+---------------------+
December 14, 2018 at 5:10 am
alex.richards - Friday, December 14, 2018 3:24 AMI'm looking at calculating the difference between two date fields in SQL (SQL Server) which may (or may not) overlap to another month.I have a TimeFrom field and a TimeTo field and a status field. Both of the time fields are in datetime format and the status is numeric.
Here is an example of the dates:
+---------------------+---------------------+--------+
| TimeFrom | TimeTo | Status |
+---------------------+---------------------+--------+
| 2018-04-01 09:03:27 | 2018-07-15 10:11:12 | 12 |
+---------------------+---------------------+--------+I need to be calculate the time between those dates but in the relevant month and I'm struggling to work this out myself.
Output would look something like:
+-----------+--------+---------------------+
| YearMonth | Status | Duration (hh:mm:ss) |
+-----------+--------+---------------------+
| 2018-04 | 12 | xx:xx:xx |
+-----------+--------+---------------------+
| 2018-05 | 12 | xx:xx:xx |
+-----------+--------+---------------------+
| 2018-06 | 12 | xx:xx:xx |
+-----------+--------+---------------------+
| 2018-07 | 12 | xx:xx:xx |
+-----------+--------+---------------------+
Not sure I understand your expected output. I feel the important part here is the values of xx:xx:xx, which is the part you've omitted to provide us the expected results for.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 14, 2018 at 5:11 am
Thom A - Friday, December 14, 2018 5:10 AMalex.richards - Friday, December 14, 2018 3:24 AMI'm looking at calculating the difference between two date fields in SQL (SQL Server) which may (or may not) overlap to another month.I have a TimeFrom field and a TimeTo field and a status field. Both of the time fields are in datetime format and the status is numeric.
Here is an example of the dates:
+---------------------+---------------------+--------+
| TimeFrom | TimeTo | Status |
+---------------------+---------------------+--------+
| 2018-04-01 09:03:27 | 2018-07-15 10:11:12 | 12 |
+---------------------+---------------------+--------+I need to be calculate the time between those dates but in the relevant month and I'm struggling to work this out myself.
Output would look something like:
+-----------+--------+---------------------+
| YearMonth | Status | Duration (hh:mm:ss) |
+-----------+--------+---------------------+
| 2018-04 | 12 | xx:xx:xx |
+-----------+--------+---------------------+
| 2018-05 | 12 | xx:xx:xx |
+-----------+--------+---------------------+
| 2018-06 | 12 | xx:xx:xx |
+-----------+--------+---------------------+
| 2018-07 | 12 | xx:xx:xx |
+-----------+--------+---------------------+Not sure I understand your expected output. I feel the important part here is the values of xx:xx:xx, which is the part you've omitted to provide us the expected results for.
Hi Thom,
The expected output for that column is in the header (hours:minutes:seconds). This should be time duration between 1/4/18 - 1/5/18 exclusive, 1/5/18 - 1/6/18 exclusive etc.
December 14, 2018 at 5:28 am
alex.richards - Friday, December 14, 2018 5:11 AMHi Thom,
The expected output for that column is in the header (hours:minutes:seconds). This should be time duration between 1/4/18 - 1/5/18 exclusive, 1/5/18 - 1/6/18 exclusive etc.
So, for the first row you want the value to be 720:00:00 (30 * 24)? Considering that no months have part of a day in them, why do you need the minutes and seconds? The values will always be 0.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 14, 2018 at 5:31 am
Thom A - Friday, December 14, 2018 5:28 AMalex.richards - Friday, December 14, 2018 5:11 AMHi Thom,
The expected output for that column is in the header (hours:minutes:seconds). This should be time duration between 1/4/18 - 1/5/18 exclusive, 1/5/18 - 1/6/18 exclusive etc.So, for the first row you want the value to be 720:00:00 (30 * 24)? Considering that no months have part of a day in them, why do you need the minutes and seconds?
Due to there being timestamps and I need an accurate figure to the second 🙂
December 14, 2018 at 5:34 am
alex.richards - Friday, December 14, 2018 5:31 AMThom A - Friday, December 14, 2018 5:28 AMalex.richards - Friday, December 14, 2018 5:11 AMHi Thom,
The expected output for that column is in the header (hours:minutes:seconds). This should be time duration between 1/4/18 - 1/5/18 exclusive, 1/5/18 - 1/6/18 exclusive etc.So, for the first row you want the value to be 720:00:00 (30 * 24)? Considering that no months have part of a day in them, why do you need the minutes and seconds?
Due to there being timestamps and I need an accurate figure to the second 🙂
But 720 is correct, yes? Again, expected results would be great, especially considering that the value you saying you are struggling with is the exact value you haven't given us in your expected output.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 14, 2018 at 5:44 am
If my guess is correct, then this is what you are after. You'll need to make use of a Calendar Table, such as the one here.
The above, on my system, returns the following:
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 14, 2018 at 6:58 am
DECLARE
@TimeTo DATETIME = '2018-07-15 10:11:12'
,@TimeFrom DATETIME = '2018-04-01 09:03:27';
SELECT
CAST(DATEPART(YEAR,startM) AS CHAR(4))
+'-' + CASE
WHEN DATEPART(Month,startM) <10
THEN '0'+CAST(DATEPART(Month,startM) AS CHAR(1))
ELSE CAST(DATEPART(Month,startM) AS CHAR(2))
END AS YearMonth
,12 AS Status
,CAST(DATEDIFF(HOUR,m.startM,M.EndM) AS CHAR(3))+':00:00' AS duration
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY(SELECT NULL))-1 AS N
FROM
(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t1(N)
)t
CROSS APPLY(
SELECT
DATEADD(MONTH,DATEDIFF(MONTH,0,DATEADD(MONTH,t.N,@TimeFrom)),0) AS startM
,DATEADD(MONTH,DATEDIFF(MONTH,0,DATEADD(MONTH,t.N,@TimeFrom))+1,0) AS EndM
)m
I know Thom has already provided an answer but this will work if you haven't got a calendar table. Which you should get if you haven't by the way, very useful things 🙂
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
December 15, 2018 at 9:55 pm
alex.richards - Friday, December 14, 2018 5:31 AMThom A - Friday, December 14, 2018 5:28 AMalex.richards - Friday, December 14, 2018 5:11 AMHi Thom,
The expected output for that column is in the header (hours:minutes:seconds). This should be time duration between 1/4/18 - 1/5/18 exclusive, 1/5/18 - 1/6/18 exclusive etc.So, for the first row you want the value to be 720:00:00 (30 * 24)? Considering that no months have part of a day in them, why do you need the minutes and seconds?
Due to there being timestamps and I need an accurate figure to the second 🙂
Ok... so that means "No"... Neither the first month nor the last month should contain all of the hours of the month. You want the hours for the first and last month to represent the partial month according to the first and last dates. In other words, for the dates you gave in your original post, you really want the following durations by month, correct?
YearMonth MonthDur
2018-04 710:56:33
2018-05 744:00:00
2018-06 720:00:00
2018-07 346:11:12
If that's true, then let's start off by saying it's time to start using a "Tally-table-like" readless function. It has a shedload of uses. Please see the documented attachment called "fnTally.txt" for more information as to how to use it.
Once you have that, it becomes trivial to create sequences of numbers or dates rather quickly. The other attached function (it's about time I turned it into a function for this common request 😉 ) does just that to return the output you requested and it can easily be used (see the usage examples in the code).
Both of the attachments contain SQL code. It still absolutely amazes me that you can't upload a file with a .SQL extension on an SQL forum.
If you have any questions after reading the documentation in either function, please don't hesitate to post back.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply