Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Datediff

Posted on 2008-10-17
4
Medium Priority
?
489 Views
Last Modified: 2012-08-14
How can I use sql to give me the difference between a date timestamp field in days and exclude weekends?  The data below shows how many times an item has changed status, I want to create an extra column called 'Days' to show the number of days that the item has changed status between each line.  If this item does not change status, then I want it to calculate against the current date. Please see an example of the data below:

Change Date

2008-08-13 12:16:56.377
2008-08-13 12:16:56.377
2008-08-13 19:08:39.090
2008-08-14 21:15:28.617
2008-08-24 18:57:13.433
2008-09-05 16:33:02.653
2008-09-05 16:33:02.653
2008-09-25 12:56:46.277
2008-09-30 15:42:14.880
2008-10-01 15:42:40.640
2008-10-03 15:51:36.587
2008-10-17 09:49:18.010


This is what I would like for my output to be:

Change Date                                                          Days

2008-08-13 12:16:56.377                                         0
2008-08-13 12:16:56.377                                         0
2008-08-13 19:08:39.090                                         0    
2008-08-14 21:15:28.617                                         1
2008-08-24 18:57:13.433                                        10
2008-09-05 16:33:02.653                                        12  
2008-09-05 16:33:02.653                                        12  
2008-09-25 12:56:46.277                                        20
2008-09-30 15:42:14.880                                         5
2008-10-01 15:42:40.640                                         1
2008-10-03 15:51:36.587                                         2
2008-10-17 09:49:18.010                                         14

0
Comment
Question by:daintysally
  • 3
4 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 22742526
this will give you the result including weekends

with a as
(select change_date, row_numbeR() over (order by change_date) as rown
from your_table
)

select  t1.change_date,  
     case  when t2.rown is null then datediff(d, t1.change_date, getdate())
               else datediff(d, t1.change_date, t2.change_date)

from  a t1 left outer join a t2 on t1.rown = t2.rown-1
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22742546
if you want to remove the weekends you need to add in the case checks for the t1.change_date and t2.change_date day of the week and according to the dayof the week (which can be retreived using datepart) you can calculate how many weekend days you had in there
(it depends on the first day of the week and which days are considerd weekends for you)
0
 

Author Comment

by:daintysally
ID: 22742649
I do not understand your solution.  Can you please explain further for me?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22755234
the code i wrote sums up all the days
removing the weekends depends on where you from
some countries week end is friday-saturday, some weekend is saturday-sunday etc...
you need to modify the query and add logic that will calculate the number of weekend days between the dates
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question