寻找代码优化的最大活动日期

我正在使用下面的查询来查找sql server 2008中需要收集活动的帐户。它运行良好，但一旦数据在其中，我就在powerpivot中做了很多工作。我希望在sql查询中这样做，使其更高效地工作，并限制输出文件的大小。

条件是帐户有余额（xrxTrnGr.balance>0）并且上次活动发生在45天前。

活动日期由Max（xrxPatNotes.NoteDate）AS"NoteDate的最大值"、Max（xrxrxTrnICf.PostDate）AS"IcfPostDate的最大点"、Max

我正在寻找一个更有效的查询，它将以列的形式返回最后一个活动日期，并且只返回那些超过45天没有活动的帐户。请帮忙。

SELECT DB_NAME() AS DataBaseName, xrxTrnLgr.PatId
     , MAX(xrxTrnLgr.Balance) AS 'BALANCE'
     , Max(xrxPatNotes.NoteDate) AS 'Max of NoteDate'
     , Max(xrxTrnIcf.PostDate) AS 'Max of IcfPostDate'
     , Max(xrxPat.EntryDate) AS 'Entry Date'
     , Max(xrxPat.Coverage) AS 'Coverage'
     , Max(xrxPat.DctId) AS 'Doctor'
     , Max(xrxTrnPay.PostDate) AS 'Last Payment'
FROM xrxTrnLgr 
LEFT OUTER JOIN xrxPatNotes ON xrxTrnLgr.PatId = xrxPatNotes.PatId
LEFT OUTER JOIN xrxTrnIcf ON xrxTrnLgr.PatId = xrxTrnIcf.PatId
LEFT OUTER JOIN xrxPat ON xrxTrnLgr.PatId = xrxPat.PatId
LEFT OUTER JOIN xrxTrnPay ON xrxTrnLgr.PatId = xrxTrnPay.PatId
GROUP BY xrxTrnLgr.PatId, xrxTrnLgr.Balance
HAVING (xrxTrnLgr.Balance>0)

with Acts (PatId, ActiveDate) as
(
SELECT PatId, max(NoteDate )
  FROM xrxPatNotes 
 GROUP BY PatId
UNION 
SELECT PatId, max(PostDate)
  FROM xrxTrnIcf 
  GROUP BY PatId
-- continue to UNION other activity tables
),
     ActsMoreThan45Days as
(
SELECT PatId,max(ActiveDate) as ActiveDate
  FROM Acts
 GROUP BY PatId
HAVING DATEDIFF(d, max(ActiveDate), GETDATE()) >= 45
)
SELECT DB_NAME() AS DataBaseName, 
       xrxTrnLgr.PatId, 
       xrxTrnLgr.Balance, 
       A45.ActiveDate, 
       xrxPat.Coverage,
       xrxPat.DctId AS Doctor
  FROM xrxTrnLgr 
  LEFT OUTER JOIN ActsMoreThan45Days A45
    ON xrxTrnLgr.PatId = A45.PatId
  LEFT OUTER JOIN xrxPat 
    ON xrxTrnLgr.PatId = xrxPat.PatId
 WHERE xrxTrnLgr.Balance>0

与其按xrxTrnLgr上的平衡列进行分组，不如将最大活动日期连接到xrxTrn Lgr表，这样会快得多。
聚合所有活动表并分别获得最大活动日期，然后比较所有这些最大活动日期将是有效的。

不幸的是，SQL Server没有GREATEST函数，因此您可以将其作为如下UDF来执行：

CREATE FUNCTION dbo.Greatest(@date1 datetime, @date2 datetime) RETURNS datetime
AS
BEGIN
    IF @date1 > @date2
        RETURN @date1
    RETURN @date2
END

然后像这样查询：

SELECT
    DB_NAME() AS DataBaseName,
    xrxTrnLgr.PatId,
    MAX(xrxTrnLgr.Balance) AS 'BALANCE',
    dbo.Greatest(MAX(xrxPatNotes.NoteDate), dbo.Greatest(Max(xrxTrnIcf.PostDate), dbo.Greatest(Max(xrxPat.EntryDate), Max(xrxTrnPay.PostDate)) as 'Last Activity',
    Max(xrxPat.Coverage) AS 'Coverage',
    Max(xrxPat.DctId) AS 'Doctor'
FROM  xrxTrnLgr LEFT OUTER JOIN xrxPatNotes ON xrxTrnLgr.PatId = xrxPatNotes.PatId
LEFT OUTER JOIN  xrxTrnIcf ON xrxTrnLgr.PatId = xrxTrnIcf.PatId
LEFT OUTER JOIN xrxPat ON xrxTrnLgr.PatId = xrxPat.PatId
LEFT OUTER JOIN xrxTrnPay ON xrxTrnLgr.PatId = xrxTrnPay.PatId
GROUP BY xrxTrnLgr.PatId, xrxTrnLgr.Balance
HAVING (xrxTrnLgr.Balance>0) AND 
    DATEDIFF(d, dbo.Greatest(MAX(xrxPatNotes.NoteDate), dbo.Greatest(Max(xrxTrnIcf.PostDate), dbo.Greatest(Max(xrxPat.EntryDate), Max(xrxTrnPay.PostDate)), GETDATE()) > 45

SELECT DB_NAME() AS DataBaseName, xrxTrnLgr.PatId
     , MAX(xrxTrnLgr.Balance) AS 'BALANCE'
     , Max(xrxPatNotes.NoteDate) AS 'Max of NoteDate'
     , Max(xrxTrnIcf.PostDate) AS 'Max of IcfPostDate'
     , Max(xrxPat.EntryDate) AS 'Entry Date'
     , Max(xrxPat.Coverage) AS 'Coverage'
     , Max(xrxPat.DctId) AS 'Doctor'
     , Max(xrxTrnPay.PostDate) AS 'Last Payment'
FROM xrxTrnLgr 
LEFT OUTER JOIN xrxPatNotes ON xrxTrnLgr.PatId = xrxPatNotes.PatId
LEFT OUTER JOIN xrxTrnIcf   ON xrxTrnLgr.PatId = xrxTrnIcf.PatId
LEFT OUTER JOIN xrxPat      ON xrxTrnLgr.PatId = xrxPat.PatId 
LEFT OUTER JOIN xrxTrnPay   ON xrxTrnLgr.PatId = xrxTrnPay.PatId
where xrxTrnLgr.Balance > 0
GROUP BY xrxTrnLgr.PatId 
HAVING datediff(dd, MAX(xrxTrnLgr.Balance)   , getdate()) > 45
   and datediff(dd, Max(xrxPatNotes.NoteDate), getdate()) > 45
   ...

您可能正在寻找或

相关内容

最新更新

热门标签：