Solved

Datediff

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql Stored Procedure field variable 17 31
Access Crosstab Query with Multiple Values 4 33
Query Syntax 17 37
delete the first occurence of a duplicate row in sql 5 26
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

825 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