如何在SQL中選擇前幾年的資料？-有解無憂

我嘗試使用WHERE (YEAR(InceptionDate), -3, GETDATE())，但是

SELECT 
    CompanyName, ServiceSubCodeName, subdiagnosisName,
    YEAR(InceptionDate), 
    SUM(AmountCharged) AmountCharged,
    SUM(AmountAllowed) AmountAllowed, 
    SUM(AmountDenied) AmountDenied,  
    SUM(AmountDenied) * 100.0 / SUM(AmountCharged) AS DeniedPart
FROM 
    Fact.ClaimDetailVw
WHERE 
    (YEAR(InceptionDate), -5, GETDATE())
    -- WHERE YEAR(InceptionDate) = 2019 AND   
    AND VetCompanyName <> 'Unknown' 
GROUP BY
    CompanyName, ServiceSubCodeName, subdiagnosisName, 
    YEAR(InceptionDate)
HAVING 
    SUM(AmountCharged) <> 0
ORDER BY 
    CompanyName

但我收到一個錯誤

在預期條件的背景關系中指定的非布爾型別的運算式，靠近 ','

這不是資料問題，因此不提供資料集。如您所見，它在注釋代碼中有效，但這僅適用于 2019 年。我想要過去 5 年的資料

uj5u.com熱心網友回復：

您最好計算 5 年前的第一年，然后查詢該日期/時間值。

SELECT 
    CompanyName, ServiceSubCodeName, subdiagnosisName,
    YEAR(InceptionDate), 
    SUM(AmountCharged) AmountCharged,
    SUM(AmountAllowed) AmountAllowed, 
    SUM(AmountDenied) AmountDenied,  
    SUM(AmountDenied) * 100.0 / SUM(AmountCharged) AS DeniedPart
FROM 
    Fact.ClaimDetailVw
WHERE 
    InceptionDate >= DATETIMEFROMPARTS(YEAR(GETDATE()) - 4, 1, 1, 0, 0, 0, 0)
    AND VetCompanyName <> 'Unknown' 
GROUP BY
    CompanyName, ServiceSubCodeName, subdiagnosisName, 
    YEAR(InceptionDate)
HAVING 
    SUM(AmountCharged) <> 0
ORDER BY 
    CompanyName

如果你想排除今年 - 你可以添加

    AND InceptionDate < DATETIMEFROMPARTS(YEAR(GETDATE()), 1, 1, 0, 0, 0, 0)

如果列 InceptionDate 被定義為日期資料型別 - 那么您可以使用 DATEFROMPARTS(YEAR(GETDATE()) - 4, 1, 1)。

uj5u.com熱心網友回復：

首先獲取您想要的年份作為開始日期

--get start year
    
declare @StartYear  int = year(getdate()) - 5;
    
select @StartYear
    
SELECT
    CompanyName,
    ServiceSubCodeName,
    subdiagnosisName,
    YEAR(InceptionDate), 
    SUM(AmountCharged) AmountCharged,
    SUM(AmountAllowed) AmountAllowed , 
    SUM(AmountDenied) AmountDenied , 
    SUM(AmountDenied)*100.0/SUM(AmountCharged) ASDeniedPert
FROM Fact.ClaimDetailVw
WHERE YEAR(InceptionDate) > @StartYear  
AND VetCompanyName <> 'Unknown' 
GROUP BY  CompanyName,ServiceSubCodeName,subdiagnosisName, YEAR(InceptionDate)
HAVING SUM(AmountCharged) <> 0
ORDER BY CompanyName

uj5u.com熱心網友回復：

如果你想在過去的 5 年里，包括今年......

WHERE YEAR(InceptionDate) BETWEEN YEAR(GETDATE()) - 4 AND YEAR(GETDATE())

……如果您想要過去 5 年不包括當前年份……

WHERE YEAR(InceptionDate) BETWEEN YEAR(GETDATE()) - 5 AND YEAR(GETDATE()) - 1

……這是一種安全的方法，因為它考慮到了未來過時資料（即預算/預測資料）的潛力。

轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/384643.html

標籤：sql-server 查询语句短信

上一篇：如何僅將唯一記錄從另一個表匯入到一個表中？

下一篇：表被多次掃描以進行選擇/計數