Post reply

Date Calculation

  • January 21, 2010 at 1:43 pm

    #214786

    Hi Friends

    I got a problem here.

    I have a Reading Date Column like this.

    Reading

    18/11/09

    14/05/09

    20/11/08

    23/05/08

    22/11/07

    Here I want to display another column like this

    NoDays

    188 --------------Diff between 18/11/09 And 14/05/09

    175

    181

    183

    Can anybody help me ?

    Thanks.

  • January 21, 2010 at 3:38 pm

    #1106990

    Need more info. Table schema perhaps w/ some scripts for loading data in them and perhaps the desired results.

  • January 21, 2010 at 3:38 pm

    #1106992

    also are you sure the dates are being stored that way? dd/mm/yy? That's odd.

  • January 21, 2010 at 4:21 pm

    #1107008

    Hi

    The data has been stored as INT type for example 11/11/09 means 20091111

    So i converted

    to Reading_date using this statement.

    CONVERT(DATETIME,CAST(ReadingDate_Key AS VARCHAR(12)),112) AS ReadingDate.

    Plaese let me know if you need any more information

  • January 21, 2010 at 4:28 pm

    #1107011

    Here is the data in my table

    Reading

    20090909

    20060909

    20070909

    My user asked no of days between readings.

    Here is the sample ouput i want

    Reading No Of days

    09/09/2009 DateDIFF(09/09/2009 ,09/09/2007)

    09/09/2007 DATEDIFF(09/09/2007,09/09/2006)

    09/09/2007

    Here i used convert function to display 20090909 as 09/09/2007.

    But i dont know how to caluculate noofdays.

    I think you can understand my situation.

    Thanks,

  • January 21, 2010 at 11:15 pm

    #1107108

    Anitha,

    You're probably not getting the help you need just because of the way you posted the data. Take a look at the first link in my signature below to get better answers a whole lot quicker. People will jump through hoops for you if you post data in the manner identified in that article.

    --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)
    Intro to Tally Tables and Functions

  • January 22, 2010 at 12:04 am

    #1107124

    HI Jeff

    Thanks for your time.

    Actually what i am looking is row caculation between dates(Date type is DATETIME).

    Any help would be Appreciated .

  • January 22, 2010 at 12:17 am

    #1107126

    Yes, I know what you're looking for. If you take the time to post the data in a readily consumable format (like in the article I referred you to), people will be more willing to help. I'm at your service when you do. 😉

    --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)
    Intro to Tally Tables and Functions

  • January 22, 2010 at 12:32 am

    #1107129

    HI Jeff

    I really thankful to you.

    I will send you now.

  • January 22, 2010 at 12:55 am

    #1107133

    Table:

    MeterReading---------- ReadingDate

    0 ---------- '2003-06-13 00:00:00.000'

    70 ---------- '2003-11-12 00:00:00.000'

    162 ---------- '2004-05-25 00:00:00.000'

    239 ---------- '2004-11-03 00:00:00.000'

    365 ---------- '2005-05-24 00:00:00.000'

    Output What i am looking for:

    ReadingDate ------------------------- NoOfDays(Row Calculated Column)

    '2003-06-13 00:00:00.000' --------- 0

    '2003-11-12 00:00:00.000' -------- Diff('2003-11-12' -'2003-06-13')

    '2004-05-25 00:00:00.000' -------- Diff('2004-05-25','2003-11-12')

    '2004-11-03 00:00:00.000' -------- Diff('2004-11-03 ','2004-05-25

    '2005-05-24 00:00:00.000' -------- Diff('2004-05-25','2004-11-03')

    I think it makes sense.....

    Please let me know if you need any thing more....

  • January 22, 2010 at 12:57 am

    #1107135

    Sorry jeff

    i typed correctly and checked with the preview but it came odd format.

  • January 22, 2010 at 1:17 am

    #1107139

    I updated the code now it looks ok...

    Table:

    MeterReading---------- ReadingDate

    0 ---------- '2003-06-13 00:00:00.000'

    70 ---------- '2003-11-12 00:00:00.000'

    162 ---------- '2004-05-25 00:00:00.000'

    239 ---------- '2004-11-03 00:00:00.000'

    365 ---------- '2005-05-24 00:00:00.000'

    Output What i am looking for:

    ReadingDate ------------------------- NoOfDays(Row Calculated Column)

    '2003-06-13 00:00:00.000' --------- 0

    '2003-11-12 00:00:00.000' -------- Diff('2003-11-12' -'2003-06-13')

    '2004-05-25 00:00:00.000' -------- Diff('2004-05-25','2003-11-12')

    '2004-11-03 00:00:00.000' -------- Diff('2004-11-03 ','2004-05-25

    '2005-05-24 00:00:00.000' -------- Diff('2004-05-25','2004-11-03')

    I think it makes sense.....

    Please let me know if you need any thing more....

  • January 22, 2010 at 7:42 am

    #1107337

    select DateDIFF(dd,'09/09/2009' ,'09/10/2007')

  • January 22, 2010 at 8:06 am

    #1107363

    Please take a close look at the code I have provided. In it you will see that I have provided you with the CREATE TABLE statement and the INSERT INTO statements to load sample data. This is what you had been asked for in earlier posts. If you provide this, many people will quickly jump in and help. It demonstrates that you are commited to solving your problem.

    create table dbo.Meter (

    MeterID int,

    ReadingDate datetime,

    MeterReading int

    );

    insert into dbo.Meter

    select 1, '2003-06-13 00:00:00.000', 0 union all

    select 1, '2003-11-12 00:00:00.000', 70 union all

    select 1, '2004-05-25 00:00:00.000', 162 union all

    select 1, '2004-11-03 00:00:00.000', 239 union all

    select 1, '2005-05-24 00:00:00.000', 365;

    with MeterReadings as (

    select

    row_number() over (partition by MeterID order by ReadingDate asc) as RowNum,

    MeterID,

    ReadingDate,

    MeterReading

    from

    dbo.Meter

    )

    select

    r1.MeterID,

    r1.ReadingDate,

    datediff(dd, isnull(r2.ReadingDate, r1.ReadingDate), r1.ReadingDate) as DaysBetweenReadings

    from

    MeterReadings r1

    left outer join MeterReadings r2

    on (r1.MeterID = r2.MeterID

    and r1.RowNum = r2.RowNum + 1)

  • January 22, 2010 at 8:45 am

    #1107401

    It demonstrates that you are commited to solving your problem.

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

    It also gives us all level ground to work from. When you don't provide this sort of info i think we all are hesitent to help because we realize that your not exactly sure what your asking which means we will have to come up w/ several solution all the while getting closer and closer to you finding out exactly wehat you really want. For example. Does this table w/ the dates have an identity column? It would help if you provided more detailed info. Perhaps all the tables columns instead of just the dates. Which by the way have changed from mm/dd/yy to yyyymmdd though out your postings. Those sorts of things matter.

Viewing 15 posts - 1 through 15 (of 16 total)

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