如何使用SP从表中获取日期之间的差异 [英] how to get the difference between dates from a table using an SP

问题描述

我的表格中有以下记录:

Hi Have the following records in my table:

DateId  Date                      InstId
1       2013-09-01 00:00:00.000      1
2       2013-09-09 00:00:00.000      1
3       2013-09-01 00:00:00.000      1
4       2013-09-08 00:00:00.000      1
5       2013-09-15 00:00:00.000      2
6       2013-09-22 00:00:00.000      2
7       2013-09-01 00:00:00.000      3
8       2013-09-15 00:00:00.000      3
9       2013-09-29 00:00:00.000      3

现在我想要的是获取两个连续日期之间的差，并比较如果InstId相同，则差是否小于30天或更少.
例子
-如果Date2-Date1> 30然后做点事
-如果Date3-Date2> 30做某事，等等...

Now what I want is to get the difference between two consecutive Dates and compare if the difference is less then 30 days or not where InstId is same.
example
-if Date2 - Date1 > 30 then do something
-if Date3 - Date2 > 30 the do something, etc...

How to perform this, using a stored procedure?

推荐答案

我不确定您想如何使用它，但是这里有两种使用天差的方案.
第一个让您获得具有相同IntsId
的连续记录之间的天差异

I am not sure how you would wanna use it but here are two different scenario to use the days difference.
The first one gets you the Days difference between consecutive records with same IntsId

select ISNULL(DATEDIFF(dd,M1.Date, (Select [Date] from MyTable where dateId = M1.dateId + 1 and InstId = M1.InstId)), 0) as diff from MyTable M1

第二个检查是否有连续的记录具有相同的InstId且差异大于30.

The second one checks if there are any consecutive records with same InstId with difference greater than 30.

select 'Charge 10%' as Fine  from MyTable M1
where  ISNULL(DATEDIFF(dd,M1.Date, (Select [Date] from MyTable where dateId = M1.dateId + 1 and InstId = M1.InstId)), 0) > 30

希望对您有所帮助.

祝你好运

Hope it helps.

Good luck

这篇关于如何使用SP从表中获取日期之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！