sql server 2008 -计算支付中断

Question

我目前正在编写一份报告，以突出付款违约，这是基于客户在6月付款，但随后未能在7月付款。

我目前已经把它设置为做一个except查询，检查一个月并将其与下一个月进行比较。与下面类似(语法可能不正确，因为我不得不编辑某些数据)

DECLARE @StartDatePaid AS DATETIME
DECLARE @EndDatePaid AS DATETIME
DECLARE @StartDateMissed AS DATETIME
DECLARE @EndDateMissed AS DATETIME
SET @StartDatePaid = '01-Oct-2013'
SET @EndDatePaid   = '31-Oct-2013'
SET @StartDateMissed = '01-Nov-2013'
SET @EndDateMissed = '05-Dec-2013'
SELECT        d.StoreNo   
                , d.CustNo
FROM (
--Paid Range
SELECT     c.CustNo, m.StoreNo 
FROM         dbo.tblCont AS c INNER JOIN
                      dbo.tblContDep AS cd ON c.ContractNo = cd.ContractNo INNER JOIN
                      dbo.tblCust AS m ON c.CustNo = m.CustNo INNER JOIN
                      dbo.tblTrans AS mx ON m.CustNo = mx.CustNo AND cd.AgendaCode = mx.AgendaCode INNER JOIN
                      dbo.tblCalender AS cl ON mx.DateEvent  = cl.Date 
WHERE     (cd.Payment > 0) AND (m.Closed <> 'Y') AND (cd.AgendaCode <> 'OPCLIPMT')
                                 AND mx.DateEvent BETWEEN @StartDatePaid AND @EndDatePaid 
GROUP BY c.CustNo, m.StoreNo, mx.DateEvent 
EXCEPT 
--Missed Range
SELECT     c.CustNo, m.StoreNo
FROM         dbo.tblCont AS c INNER JOIN
                      dbo.tblContDep AS cd ON c.ContractNo = cd.ContractNo INNER JOIN
                      dbo.tblCust AS m ON c.CustNo = m.CustNo INNER JOIN
                      dbo.tblTrans AS mx ON m.CustNo = mx.CustNo AND cd.AgendaCode = mx.AgendaCode INNER JOIN
                      dtLookups.dbo.tblCalender AS cl ON mx.DateEvent = cl.Date 
WHERE     (cd.Payment > 0) AND (m.Closed <> 'Y') AND (cd.AgendaCode <> 'OPCLIPMT') AND (mx.DateEvent BETWEEN @StartDateMissed AND @EndDateMissed )
GROUP BY c.CustNo, m.StoreNo, mx.DateEvent
      ) AS d 
      WHERE d.StoreNo IN (72, 114, 121, 139, 185, 241, 266)
      GROUP BY 
                      d.StoreNo, d.CustNo

我将把它切换到基于日历月而不是日期范围，我的问题是我如何最好地一次生成几个月的损坏。为了获得一个月对一个月的比较一次，因为它是我只能让它创建一个月的破坏基于提供的数据。

期望输出的示例

Month| breakges
June | 201
July | 189
Aug  | 250

乐于接受最佳实践或改进方法的建议。

Answer 1

我承认我不明白你的问题。假设你们的破碎是第一次未付款，而不是以后的。您可以生成所需的输出，如下所示:

-- prepare your source data
with cte1 as
(
  select 
    user_id, 
    date, -- representing month by the first day
    missed -- bool flag if payment was missed in that month
    from ...    
)
-- add a sequence number to the source data ordered by date
with cte2 as
(
  select *,
    row_number() over(partition by user_id order by date) rn
  from cte1
)
-- select those records where payment was missed but the previous was ok
,cte3 as
(
  select user_id, date from cte2 a
  where a.missed = 1
  and exists (
    select * from cte2 b 
    where b.missed = 0
    and b.uid = a.uid 
    and b.rn = a.rn -1
    )
)
select date, count(*) as breakage from cte3 group by date