Post reply

how to get current year

  • September 18, 2012 at 6:55 am

    #261808

    Table contains Orderdate Column which contains years starting from 2001 to 2010. i need to get currect year using any of the build in function or query can any body guide me

  • September 18, 2012 at 6:56 am

    #1539068

    two built in functions should get you what you are after:

    SELECT YEAR(getdate())

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 18, 2012 at 6:57 am

    #1539070

    Ummm. . .

    YEAR(GETDATE());

    http://msdn.microsoft.com/en-us/library/ms186313.aspx

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • September 18, 2012 at 7:10 am

    #1539074

    Sorry for not giving enough information

    CREATE TABLE #TestBasedOnYear

    (

    OrderDate DATETIME,

    TotalDue MONEY

    )

    INSERT INTO #TestBasedOnYear(OrderDate,TotalDue)

    SELECT '2001-07-01 00:00:00.000','27231.5495'

    UNION ALL

    SELECT '2001-07-01 00:00:00.000','1716.1794'

    UNION ALL

    SELECT '2001-07-01 00:00:00.000','19005.2087'

    UNION ALL

    SELECT '2002-01-01 00:00:00.000','36096.7069'

    UNION ALL

    SELECT '2002-01-01 00:00:00.000','556.2026'

    UNION ALL

    SELECT '2003-01-01 00:00:00.000','47633.1875'

    UNION ALL

    SELECT '2003-01-01 00:00:00.000','46.0291'

    UNION ALL

    SELECT '2004-01-01 00:00:00.000','9128.8743'

    UNION ALL

    SELECT '2004-01-01 00:00:00.000','52154.9009'

    UNION ALL

    SELECT '2004-01-01 00:00:00.000','38.7783'

    UNION ALL

    SELECT '2004-01-01 00:00:00.000','4650.7326'

    Out Put Should look like

    TotalDueForTheCurrentYear OverAllTotalDue

    65973.2861 198258.3498

  • September 18, 2012 at 7:18 am

    #1539078

    Two options:

    SELECT TotalDueForTheCurrentYear = SUM(CASE WHEN YEAR(OrderDate) = YEAR(GETDATE()) THEN TotalDue ELSE 0 END),

    OverAllTotalDue = SUM(TotalDue)

    FROM #TestBasedOnYear

    SELECT TotalDueForTheCurrentYear = SUM(CASE WHEN OrderDate >= DATEADD( yyyy, DATEDIFF(yyyy,0,GETDATE()), 0) AND OrderDate < DATEADD( yyyy, DATEDIFF(yyyy,0,GETDATE()) + 1, 0) THEN TotalDue ELSE 0 END),

    OverAllTotalDue = SUM(TotalDue)

    FROM #TestBasedOnYear

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • September 18, 2012 at 7:23 am

    #1539080

    Thanks for giving me solution

  • September 18, 2012 at 7:29 am

    #1539084

    Of course you do, because it's not 2004 any more.

    Here's a third alternative:

    WITH Totals(Yr, TotalDueForTheCurrentYear, OverAllTotalDue) AS (

    SELECT

    YEAR(OrderDate)

    ,SUM(TotalDue) OVER(PARTITION BY YEAR(OrderDate))

    ,SUM(TotalDue) OVER(PARTITION BY 1)

    FROM

    #TestBasedOnYear

    )

    SELECT DISTINCT

    TotalDueForTheCurrentYear

    ,OverAllTotalDue

    FROM

    Totals

    WHERE

    Yr = 2004

    John

  • September 18, 2012 at 7:30 am

    #1539085

    Bang on:)

  • September 18, 2012 at 5:36 pm

    #1539328

    John Mitchell, I see that the solution works. It's nice. I understand CTE and 'PARTITION BY YEAR(OrderDate)' etc., but will you please explain why 'PARTITION BY 1'?

    --Quote me

  • September 18, 2012 at 6:23 pm

    #1539333

    I can answer for John.

    SUM OVER requires a PARTITION clause. In this case, since you're partitioning over the entire rowset, anything will do, like 1 or NULL.

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • September 18, 2012 at 11:34 pm

    #1539387

    So, group by limits you to one aggregation, and PARTITION BY allows for multiple groupings. Thanks Dwain.

    --Quote me

  • September 18, 2012 at 11:44 pm

    #1539388

    polkadot (9/18/2012)

    So, group by limits you to one aggregation, and PARTITION BY allows for multiple groupings. Thanks Dwain.

    Actually, either supports aggregation to many levels (I am not sure of the limit).

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply