Solved

Datediff

Posted on 2008-10-17
4
466 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
Comment Utility
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
Comment Utility
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
Comment Utility
I do not understand your solution.  Can you please explain further for me?
0
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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 video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

10 Experts available now in Live!

Get 1:1 Help Now