Solved

Datediff

Posted on 2008-10-17
4
470 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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query 4 47
Script to randomly create characters in MS SQL 6 32
Oracle Pivot Question 8 45
Unable to save view in SSMS 21 61
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

863 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now