How to write a query that pulls data for each year on the given day

Question

I have a query in which I need to pull Claims Data for the given day and return that data for each year before on that given day e.g.

I have a claim today: 1/15/2019

I want to be able to pull all the other years in one generic query as this will be a Stored Procedure.

This will allow me to view if there are claims on the given day for any of the past years.

I am able to do 1 year, 2 years, 3 years, all separately if I were to create a variable or case statement but is there a way to do it all in a simpler manner?

Here is the code for 1 year ago:

SELECT * FROM tblClaim c
WHERE c.IncidentDate = DATEadd(year, -1, CAST(FLOOR( CAST(GETDATE() AS FLOAT)) AS DATETIME)) AND c.StatusID = 2

user_1144035 · Answer

Huh? This should be much easier by just using date parts:

SELECT c.* 
FROM tblClaim c
WHERE c.StatusID = 2 AND
      DAY(c.IncidentDate) = DAY(GETDATE()) AND
      MONTH(c.IncidentDate) = MONTH(GETDATE());

Two notes:

Beware Feb 29th.
This will not use an index.

user_10292250 · Answer

So this is what I have to handle leap years if you want to do it as a stored procedure. There might be an easier way, but I couldn't think of one. You can also change my input parameter to a flexible date if you plan to use SSRS.

I Created a mock table:

CREATE TABLE #tblClaim (statusid INT, incidentdate DATE)
INSERT INTO #tblClaim (statusid, incidentdate)
VALUES (1,'2019-01-15'),(2,'2019-01-15'),(1,'2018-01-15'),(2,'2018-01-15'),
       (1,'2017-01-15'),(2,'2016-01-15'),(1,'2012-02-29'),(2,'2012-02-29'),
       (1,'2016-02-29'),(2,'2016-02-29'),(1,'2008-02-29'),(2,'2008-02-29'),
       (1,'2012-02-28'),(2,'2012-02-28'),(1,'2016-03-01'),(2,'2016-03-01'),
       (1,'2008-03-01'),(2,'2008-02-28');

Then I created a proc(adjust your variables you need displayed accordingly):

CREATE PROC dbo.myproc as

DECLARE @leapyear INT = 229
DECLARE @date DATE = GETDATE()
--DECLARE @date DATE = '20160229'   <---used to test leap year

IF DAY(@date) + (MONTH(@date)*100) = @leapyear
BEGIN

    DECLARE @day    INT = 1
    DECLARE @month  INT = 3
    DECLARE @day1   INT = 28
    DECLARE @month1 INT = 2;

    WITH CTE (statusid, incidentdate)
    AS

        (
        SELECT * 
        FROM #tblClaim
        WHERE statusid = 2
            AND DAY(incidentdate)   = @day 
            AND MONTH(incidentdate) = @month
    UNION ALL
        SELECT * 
        FROM #tblClaim
        WHERE statusid = 2
            AND DAY(incidentdate)   = @day1 
            AND MONTH(incidentdate) = @month1
    UNION ALL 
        SELECT * 
        FROM #tblClaim
        WHERE statusid = 2
            AND DAY(incidentdate)   = DAY(@date) 
            AND MONTH(incidentdate) = MONTH(@date))
    SELECT * FROM CTE

END 
ELSE BEGIN

    SELECT * 
    FROM #tblClaim
    WHERE statusid = 2 
        AND DAY(incidentdate)   = DAY(@date)
        AND MONTH(incidentdate) = MONTH(@date)

END

Leap year returns these results:

Today's date returns:

user_7253343 · Answer

this query will provide 3 years data

select [date] from TableName where (year([date]) = year(getdate())-1
or year([date]) = year(getdate())-2
or year([date]) = year(getdate())-3)

How to write a query that pulls data for each year on the given day

3 Answers