CODEWITHSUNDEEP
sql-server
Noor Rahman
Noor Rahman

Reputation: 155

Get current year's records from a view in SQL Server

I have a view called vwMemberPolicyInfo and I need to display all records entered in current year.

My query is : 

select  
    EffectiveDate AS [Month], 
    count(MemberID) AS Agents, 
    sum(ActualPremium) AS Premium
from 
    vwMemberPolicyInfo 
Where 
    EffectiveDate > DATEADD(year, -1, GETDATE())
GROUP BY 
    EffectiveDate

but it is not working fine it show result:

2010-11-01 00:00:00.000 74  40644.00
2010-07-01 00:00:00.000 86  50418.00

2011-03-08 00:00:00.000 1   744.00
2011-04-08 00:00:00.000 1   0.00
2010-11-02 00:00:00.000 5   2676.00

2011-04-14 00:00:00.000 1   1185.00
2011-02-28 00:00:00.000 7   2988.00

2011-02-23 00:00:00.000 8   8518.00
2011-04-10 00:00:00.000 1   1332.00
2011-04-07 00:00:00.000 3   2544.00

I need only the current year entries to display ..thanks in advance

Upvotes: 2

Views: 13239

Answers (4)

jack.mike.info
jack.mike.info

Reputation: 128

select
EffectiveDate AS [Month], count(MemberID) AS Agents, sum(ActualPremium) AS Premium from vwMemberPolicyInfo Where dateadd(year,0,EffectiveDate) = DATEADD(year, 0, GETDATE()) GROUP BY EffectiveDate

dateadd(year,0,EffectiveDate) = DATEADD(year, -1, GETDATE())
dateadd(year,-1,EffectiveDate) = DATEADD(year, -1, GETDATE())

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425291

SELECT  MONTH(EffectiveDate) AS [Month], 
        count(MemberID) AS Agents, 
        sum(ActualPremium) AS Premium
FROM    vwMemberPolicyInfo 
WHERE   EffectiveDate >= DATEADD(year, YEAR(GETDATE()) - 1, CAST('0001-01-01' AS DATE))
        AND EffectiveDate < DATEADD(year, YEAR(GETDATE()), CAST('0001-01-01' AS DATE))
GROUP BY
        MONTH(EffectiveDate)

Upvotes: 5

marc_s
marc_s

Reputation: 754348

It's working just fine - it's doing exactly what you told it to - it gives you back all rows that are between today and a year back (e.g. between Apr 15, 2011 and Apr 15, 2010)

What you probably want to achieve is something like this:

SELECT
    EffectiveDate AS [Month], 
    COUNT(MemberID) AS Agents, 
    SUM(ActualPremium) AS Premium
FROM
    dbo.vwMemberPolicyInfo 
WHERE
    YEAR(EffectiveDate) = 2011
GROUP BY 
    EffectiveDate

This will return only rows from the year 2011.

Upvotes: 1

icesar
icesar

Reputation: 496

Try where YEAR(EffectiveDate) = YEAR(GETDATE())

Hope it helps.

Upvotes: 7

Related Questions