Post reply

Gathering Year-to-Date Info

  • December 28, 2004 at 11:52 am

    #60040

    Hi, I'm having some kind of brain fart here.  I need to create a view the sums all sales from the beginning of (any) year to the current date.  I can't hard code the dates (i.e. 1/1/04 to Now) because I need it to work indefinitely as the years change without having to redo the criteria.  Can someone tell me how to do this in a view or stored procedure?

    Thanks in Advance!

  • December 28, 2004 at 12:20 pm

    #535171

    I would probably do it as a stored procedure so you can add some logic. 

    Here is an example if you want it to pull from the current year

    create proc usp_DateSample as

    declare @myDate dateTime
    set @myDate = '1/1/' + convert(char(4),year(getDate()))
    select * from mySalesData where salesDate > @myDate
    return 0

     

    Hope this helps,

    Kathi

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • December 28, 2004 at 12:27 pm

    #535173

    Beryl,

    You need to be more specific on what tables are involved?, what the result should look like?, what are the data types involved? and if possible put some DDL also. You can not expect to get a good answer without your cooperation.

    I could say:

    select Year(Date), Sum(Sales) TotalSales

    From YourTable

    Group by Year(Date)

    and that may not be what you want.

     

     


    * Noel

  • December 28, 2004 at 12:47 pm

    #535178

    Thanks Kathi, just what I needed!

  • December 28, 2004 at 12:48 pm

    #535180

    Thanks Kathi, just what I needed!

  • December 28, 2004 at 10:10 pm

    #535207

    Beryl,

    Borrowing on Kathi's idea, this may run a bit faster because it does no string manipulation or conversion and only has one comparson operator (=).  And it could easily be a view because it requires no parameters...

    SELECT * FROM dbo.mySalesData
WHERE YEAR(SalesDate) =  YEAR(GETDATE())

    If you wanted the ability to get all of last year, the view is simple...

    SELECT * FROM dbo.mySalesData
WHERE YEAR(SalesDate) =  YEAR(GETDATE()) -1

    If you wanted the ability to get all of the data for just the last 3 years (previous 2 plus current), the view would, again, be simple...

    SELECT * FROM dbo.mySalesData
WHERE YEAR(SalesDate) >=  YEAR(GETDATE()) -2

    And, of course, you could turn this into a stored procedure and pass in the number of previous years (or just years if you subtract 1) to make a bit of a universal stored proc, as Kathi suggested...

     CREATE PROCEDURE dbo.GetXYearsOfData
        @myYears TINYINT
AS
 SELECT * FROM dbo.mySalesData
  WHERE YEAR(SalesDate) >=  YEAR(GETDATE()) - (@myYears - 1)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

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