How do I select data from previous years in SQL?-CodePudding

I tried using WHERE (YEAR(InceptionDate), -3, GETDATE()), but

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

But I get an error

An expression of non-boolean type specified in a context where a condition is expected, near ','

This is not a data issue so not providing the dataset. As you can see, it works in the commented code but that is only for 2019. I want last 5 years data

CodePudding user response：

You would be better off calculating the 1st of the year 5 years ago, and then querying on that date/time value.

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

If you want to exclude this year - you can add

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

If the column InceptionDate is defined as a date data type - then you can use DATEFROMPARTS(YEAR(GETDATE()) - 4, 1, 1).

CodePudding user response：

First get the year you want as the start date

--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

CodePudding user response：

If you want for the last 5 years including this year …

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

… and if you want for the previous 5 years NOT including the current year …

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

… this is a safe approach given it allows for the potential for future dated data (i.e. budget/forecast data).