时间戳

首页 > 解决方案 > 如何获得下一个截止日期

sql - 如何获得下一个截止日期

问题描述

我有两张桌子。一个客户表和另一个是预定表，其中包含预定事务并包括下一个预定付款日期。我需要每天运行报告 - 我需要根据今天的日期计算下一个截止日期。

下面是我写的脚本

select a.ApplicationId
, d.Reference as ' Payment Reference'
,a.PaymentMethod
,sch.Date as 'Next Payment due Date' 
from [dbo].[Application]  as a
left join  [Direct Debit] as d on d.ApplicationId =a.ApplicationId
left join [Schedule] as sch on sch.ApplicationId=a.ApplicationId
where PaymentMethod ='Direct Debit' and  sch.Scheduletype='InstalmentSchedule'  and sch.applicationID =6923
GROUP BY a.ApplicationId, d.Reference,a.PaymentMethod,sch.Date

到期日

标签： sqltsql

解决方案

您可以将子选择添加到您的日程安排中，以便仅获取未来的下一个日期

SELECT
    a.ApplicationId
   ,d.Reference AS 'Payment Reference'
   ,a.PaymentMethod
   ,sch.Date AS 'Next Payment due Date'
FROM [dbo].[Application] AS a
LEFT JOIN [Direct Debit] AS d
    ON d.ApplicationId = a.ApplicationId
LEFT JOIN (SELECT
        ApplicationId
       ,Scheduletype
       ,MIN([Date]) [Date]
    FROM [Schedule]
    WHERE [Date] > GETDATE()
    GROUP BY ApplicationId
            ,Scheduletype) AS sch
    ON sch.ApplicationId = a.ApplicationId
WHERE PaymentMethod = 'Direct Debit'
AND sch.Scheduletype = 'InstalmentSchedule'
AND sch.applicationID = 6923
GROUP BY a.ApplicationId
        ,d.Reference
        ,a.PaymentMethod
        ,sch.Date

子选择是：

SELECT
    ApplicationId
   ,Scheduletype
   ,MIN([Date]) [Date]
FROM [Schedule]
WHERE [Date] > GETDATE() /* date must be in the future */
GROUP BY ApplicationId
        ,Scheduletype

推荐阅读