smul86 smul86 -4 years ago 93
SQL Question

SQL Server: difference in days for two dates in separate rows

I am using SQL Server 2012 and working an a report currently that is asking me to find the difference in days between two dates.

Basically, for a particular

ReportID
, I'm trying to find the difference in days between the (
ReportCompletedDate
when the
ReportType = 'PaperReceived'
) - (
ReportCompletedDate
when the
ReportType = 'Form Completed'
)

I tried to give some records below...

ReportID ReportType ReportCompletedDate
-------------------------------------------------
450 PaperReceived 9/5/2013
450 Form Completed 8/13/2013
451 PaperReceived 9/7/2013
451 Form Completed 7/12/2013
452 PaperReceived 10/6/2013
452 Form Completed 3/13/2013


So, for
ReportID = 450
for example, I want to get
9/5/2013 - 8/13/2013
in days.

If anyone also knows how to do this for calendar days and business days, that would be awesome...but at least calendar days should be fine.

I'm not sure how to go about this, usually when the two dates are in line, it's easier for me to figure out, but not sure how to go about it when the dates are in separate rows. Any advice would be gladly appreciated.

Answer Source

You can do a self join to make it appear on "one line". Like this:

SELECT DateDiff(day,BASE.ReportCompleteDate, FORM.ReportCompleteDate) as Diff
FROM TABLE_NAME_YOU_DID_NOT_SAY BASE
LEFT JOIN TABLE_NAME_YOU_DID_NOT_SAY FORM ON BASE.ReportId = FORM.ReportID AND FORM.ReportType = 'Form Completed'
WHERE BASE.ReportType = 'PaperRecieved'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download