SQL Server:单独行中两个日期的天数差异 [英] SQL Server: difference in days for two dates in separate rows
问题描述
我正在使用SQL Server 2012，目前正在处理一份报告，要求我找出两个日期之间的天差.
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.
基本上，对于特定的ReportID，我试图找出(ReportCompletedDate当ReportType = 'PaperReceived')-(ReportCompletedDate当ReportType = 'Form Completed')
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
因此，例如对于ReportID = 450，我想在几天之内得到9/5/2013 - 8/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.
推荐答案
您可以进行自我联接以使其显示在一行"上.像这样:
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'
这篇关于SQL Server:单独行中两个日期的天数差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
