CODEWITHSUNDEEP
t-sqlsql-server-2012
tsqln00b
tsqln00b

Reputation: 355

T-SQL increment datetime field by Year

I have the following query in which I am trying to create a field that counts the number of days for a year. The issue is that when I get to 2014, it keeps counting and I need it to start back at 1.

SELECT
    IJDATE,
    ROW_NUMBER() OVER( ORDER BY IJDATE ) AS 'InvoiceDay'
FROM
    S2K_IJ
WHERE
    IJTYPE = '1'
AND
    YEAR(IJDATE) > 2012
GROUP BY
    IJDATE
ORDER BY
    IJDATE
GO

Upvotes: 0

Views: 46

Answers (1)

KrazzyNefarious
KrazzyNefarious

Reputation: 3230

SELECT
    IJDATE,
    ROW_NUMBER() OVER( partition by year(IJDATE) ORDER BY IJDATE ) AS 'InvoiceDay'
FROM
    S2K_IJ
WHERE
    IJTYPE = '1'
AND
    YEAR(IJDATE) > 2012
GROUP BY
    IJDATE
ORDER BY
    IJDATE
GO

You need to partition by the year so that it starts the count from 1.

Upvotes: 1

Related Questions