Post reply

help with a query

  • May 14, 2007 at 1:44 pm

    #173694

    Hi there,

    I have a question on how I can calculate a value.

    I have a column of month-end dates.  each month end date has a value.  how can I calculate the sum of one month-end minus the previous to get a difference?

    the data looks as such:

    2002-12-31 3191729.51

    2003-01-31 3192016.75

    2003-02-28 3192303.95

    So I am looking for the difference between 01/31 and 12/31, 2/28 and 1/31

    I figure I want to skip the MIN(date) since there is nothing to compare it to.

    Any way to do it without something as ugly as a cursor?

    Thanks,

    Chris

     

     

  • May 14, 2007 at 1:51 pm

    #706340

    How will you know what dates you are tyring to calculate?  will they be passed in from your application? 

    Is this something that is the result of a process that runs on the last day of the month so you need to calculate your difference on the first?  In other words is this something where you can say use substract a day from getdate() and get one of your values? 

    Or do you need to do this for the entire table and you won't knwo what data is there to begin with?

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • May 14, 2007 at 2:20 pm

    #706347

    basically I need it for the entire table and won't know what the starting date is going to be.  On 12/31 you had 10000 and on 1/31 you had 12000.  I need to know you made 2000.  On 2/28 you had 9000, so I need to know you lost 3000.

     

  • May 14, 2007 at 4:19 pm

    #706358

    This sounds fairly straight-forward, but it would help us if you would give us your table DDL, sample data, and an example of the result set that you desire.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • May 14, 2007 at 6:47 pm

    #706366

    This should give you a basic example of what you are looking for. 

    ---------------------------------------------------------------------------------

    -- Just building dummy data

    ---------------------------------------------------------------------------------

    if exists(select 1 from sysobjects where id = OBJECT_ID(N'tblTest'))

     DROP TABLE tblTest

    GO

    CREATE TABLE tblTest (iRowId INT IDENTITY(1,1),dtEndDate DATETIME,iValue REAL)

    INSERT INTO tblTest  Select '2005-07-31 10:09:16.000',1000

     UNION

     Select '2005-08-31 10:09:16.000',10000

     UNION   

     Select '2005-09-30 10:09:16.000',3000

     UNION

     Select '2005-10-31 10:09:16.000',400

     UNION

     Select '2005-11-30 10:09:16.000',80000

    GO

    ---------------------------------------------------------------------------------

    -- THIS IS THE WHAT APPLIES TO YOUR TABLE

    ---------------------------------------------------------------------------------

    select datename(mm,dtEndDate)+' '+datename(yyyy,dtEndDate) BaseDate

    ,datename(mm,PrevDate)+' '+datename(yyyy,PrevDate) PrevDate

    ,iValue BaseValue

    ,PrevValue

    ,iValue - PrevValue Variance

    from (

    select OQ.iRowId,OQ.dtEndDate,OQ.iValue,SQ.iValue PrevValue,SQ.dtEndDate PrevDate

    From tblTest OQ FULL OUTER JOIN tblTest SQ ON

     datediff(mm,OQ.dtEndDate,SQ.dtEndDate) = -1

    ) ResultQuery

     

    The base data looks like:

    iRowId      dtEndDate                                              iValue                  

    ----------- ------------------------------------------------------ ------------------------

    1           2005-07-31 10:09:16.000                                1000.0

    2           2005-08-31 10:09:16.000                                10000.0

    3           2005-09-30 10:09:16.000                                3000.0

    4           2005-10-31 10:09:16.000                                400.0

    5           2005-11-30 10:09:16.000                                80000.0

    (5 row(s) affected)

    After the query:

    BaseDate              PrevDate            BaseValue        PrevValue        Variance                

    ------------------------------------------------------------------------------------------

    July 2005             NULL                1000.0           NULL            NULL

    August 2005           July 2005           10000.0          1000.0          9000.0

    September 2005        August 2005         3000.0           10000.0         -7000.0

    October 2005          September 2005      400.0            3000.0          -2600.0

    November 2005         October 2005        80000.0          400.0           79600.0

    NULL                  November 2005       NULL             80000.0         NULL

    (6 row(s) affected)

    -

  • May 15, 2007 at 12:34 am

    #706408

    Perhaps I'm not understanding the question, but the number of days between the last day of the month and the previous last day of the month is the day of the month.

    Jan 31 = 31 days since the last day of the previous month

    Feb 28 = 28 days since the last day of the previous month

    June 30 = 30 days since the last day of the previous month

     

    Select day(dtEndDate)

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • May 16, 2007 at 8:34 am

    #706818

    thanks Jason, that did the trick.  I always forget about derrived tables and joining on yourself.

     

    Thanks,

    Chris

     

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

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