Professional Web Applications Themes

SELECT - Number of days between records - Microsoft SQL / MS SQL Server

Greetings, I need some assistance writing a SELECT statement that will return the number of days elapsed between records in a table with a Transaction Date field. I need to calculate the Days column as shown below: FMTransID GLTransID TransDate TransAmount **Days** ----------- ----------- ----------------------- ---------------- ---- 302 938 2003-05-27 00:00:00 5147.8000 0 294 910 2003-05-27 00:00:00 166.0000 0 155 942 2003-05-28 00:00:00 -230.0000 1 296 912 2003-05-29 00:00:00 -80.0000 1 159 944 2003-05-30 00:00:00 -86.0000 1 486 2009 2003-06-27 00:00:00 -4366.0100 28 519 2044 2003-06-30 00:00:00 -465.7900 3 521 2044 2003-06-30 00:00:00 80.0000 0 520 2044 2003-06-30 00:00:00 -166.0000 ...

  1. #1

    Default SELECT - Number of days between records

    Greetings,

    I need some assistance writing a SELECT statement that will return the
    number of days elapsed between records in a table with a Transaction Date
    field. I need to calculate the Days column as shown below:

    FMTransID GLTransID TransDate TransAmount **Days**
    ----------- ----------- ----------------------- ---------------- ----
    302 938 2003-05-27 00:00:00 5147.8000 0
    294 910 2003-05-27 00:00:00 166.0000 0
    155 942 2003-05-28 00:00:00 -230.0000 1
    296 912 2003-05-29 00:00:00 -80.0000 1
    159 944 2003-05-30 00:00:00 -86.0000 1
    486 2009 2003-06-27 00:00:00 -4366.0100 28
    519 2044 2003-06-30 00:00:00 -465.7900 3
    521 2044 2003-06-30 00:00:00 80.0000 0
    520 2044 2003-06-30 00:00:00 -166.0000 0

    Any help is appreciated.

    Paul Roy
    Developer
    Corom Inter Media


    Paul Guest

  2. #2

    Default Re: SELECT - Number of days between records

    Try:

    select
    datediff (dd, TransDate, '2003-05-27)
    from
    MyTable

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Paul Roy" <com> wrote in message news:TJCRa.795$bellglobal.com...
    Greetings,

    I need some assistance writing a SELECT statement that will return the
    number of days elapsed between records in a table with a Transaction Date
    field. I need to calculate the Days column as shown below:

    FMTransID GLTransID TransDate TransAmount **Days**
    ----------- ----------- ----------------------- ---------------- ----
    302 938 2003-05-27 00:00:00 5147.8000 0
    294 910 2003-05-27 00:00:00 166.0000 0
    155 942 2003-05-28 00:00:00 -230.0000 1
    296 912 2003-05-29 00:00:00 -80.0000 1
    159 944 2003-05-30 00:00:00 -86.0000 1
    486 2009 2003-06-27 00:00:00 -4366.0100 28
    519 2044 2003-06-30 00:00:00 -465.7900 3
    521 2044 2003-06-30 00:00:00 80.0000 0
    520 2044 2003-06-30 00:00:00 -166.0000 0

    Any help is appreciated.

    Paul Roy
    Developer
    Corom Inter Media



    Tom Guest

  3. #3

    Default Re: SELECT - Number of days between records

    I have assumed Fmtransid is the PK. As Martin has pointed out, it's unclear
    how you want your results ordered. I've used Transdate, fmtransid.

    CREATE TABLE Transactions (FMTransID INTEGER PRIMARY KEY, GLTransID INTEGER
    NOT NULL, TransDate DATETIME NOT NULL, TransAmount DECIMAL(10,4) NOT NULL)

    INSERT INTO Transactions VALUES (302, 938, '20030527', 5147.8000)
    INSERT INTO Transactions VALUES (294, 910, '20030527', 166.0000)
    INSERT INTO Transactions VALUES (155, 942, '20030528', -230.0000)
    INSERT INTO Transactions VALUES (296, 912, '20030529', -80.0000)
    INSERT INTO Transactions VALUES (159, 944, '20030530', -86.0000)
    INSERT INTO Transactions VALUES (486, 2009, '20030627', -4366.0100)
    INSERT INTO Transactions VALUES (519, 2044, '20030630', -465.7900)
    INSERT INTO Transactions VALUES (521, 2044, '20030630', 80.0000)
    INSERT INTO Transactions VALUES (520, 2044, '20030630', -166.0000)

    SELECT T1.fmtransid,
    MIN(T1.gltransid) AS gltransid, MIN(T1.transdate) AS transdate,
    MIN(T1.transamount) AS transamount,
    COALESCE(DATEDIFF(DAY,MAX(T2.transdate),MIN(T1.tra nsdate)),0) AS days
    FROM Transactions AS T1
    LEFT JOIN Transactions AS T2
    ON T1.fmtransid<>T2.fmtransid
    AND (T1.transdate>T2.transdate OR
    (T1.transdate=T2.transdate AND T1.fmtransid>T2.fmtransid))
    GROUP BY T1.fmtransid
    ORDER BY transdate, T1.fmtransid

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  4. #4

    Default Re: SELECT - Number of days between records

    Marty,

    Sorry, I have many columns in the table and eliminated several to keep my
    post brief. Here is a better sample:

    FnlID GLTransID TransDate TransAmount **Days
    ----------- ----------- ----------------------- ---------------- ----
    24 938 2003-05-25 00:00:00 5147.8000 0
    24 910 2003-05-27 00:00:00 166.0000 2
    24 942 2003-05-28 00:00:00 -230.0000 1
    24 912 2003-05-29 00:00:00 -80.0000 1
    24 944 2003-05-30 00:00:00 -86.0000 1
    24 2009 2003-06-27 00:00:00 -4366.0100 28
    24 2044 2003-06-30 00:00:00 -465.7900 3
    24 2044 2003-07-04 00:00:00 80.0000 4
    24 2044 2003-07-30 00:00:00 -166.0000 26

    The records are ordered by FnlID, then TransDate. I need to know the number
    of days that have elapsed since the previous record: first record will
    always be 0 since there is no other record before it.

    Thanks for your help.

    Paul Roy
    Developer
    Corom Inter Media


    "Martin Nicholson" <com> wrote in message
    news:bf6uvh$chd$atl.mindspring.net... 
    I [/ref]
    Date [/ref]
    **Days** 
    >
    >[/ref]


    Paul Guest

  5. #5

    Default Re: SELECT - Number of days between records

    But what is the primary key?
    Please post a CREATE TABLE statement (simplified to just the relevant
    columns) so we don't have to guess.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  6. #6

    Default Re: SELECT - Number of days between records

    The solution is:

    select *,
    coalesce(datediff(day, (select max(TransDate) from tFnl where TransDate <
    t.TransDate), t.TransDate), 0)
    as 'Days' from tFnl t

    given DDL:

    CREATE TABLE [tFnl] (
    [FnlID] [int] NOT NULL ,
    [GLTransID] [int] NOT NULL ,
    [TransDate] [datetime] NOT NULL ,
    [TransAmount] [numeric](15, 4) NOT NULL
    ) ON [PRIMARY]
    GO

    and DML:

    insert into tFnl values (24, 938, convert(datetime,
    '2003-05-25'), 5147.8000)
    insert into tFnl values (24, 910, convert(datetime,
    '2003-05-27'), 166.0000)
    insert into tFnl values (24, 942, convert(datetime,
    '2003-05-28'), -230.0000)
    insert into tFnl values (24, 912, convert(datetime,
    '2003-05-29'), -80.0000)
    insert into tFnl values (24, 944, convert(datetime,
    '2003-05-30'), -86.0000)
    insert into tFnl values (24, 2009, convert(datetime,
    '2003-06-27'), -4366.0100)
    insert into tFnl values (24, 2044, convert(datetime,
    '2003-06-30'), -465.7900)
    insert into tFnl values (24, 2044, convert(datetime,
    '2003-07-04'), 80.0000)
    insert into tFnl values (24, 2044, convert(datetime,
    '2003-07-30'), -166.0000)


    regards, -marty nicholson

    "Paul Roy" <com> wrote in message
    news:GwDRa.1354$bellglobal.com... 
    number [/ref]
    However, [/ref]
    want [/ref]
    example [/ref]
    > Date [/ref]
    > **Days** 
    > >
    > >[/ref]
    >
    >[/ref]


    Martin Guest

  7. #7

    Default Re: SELECT - Number of days between records

    Oops - forgot one 'where condition'

    select *,
    coalesce(datediff(day, (select max(TransDate) from tFnl where FnlId =
    t.FnlID and TransDate < t.TransDate), t.TransDate), 0)
    as 'Days' from tFnl t


    -marty


    "Paul Roy" <com> wrote in message
    news:GwDRa.1354$bellglobal.com... 
    number [/ref]
    However, [/ref]
    want [/ref]
    example [/ref]
    > Date [/ref]
    > **Days** 
    > >
    > >[/ref]
    >
    >[/ref]


    Martin Guest

Similar Threads

  1. Determining the number of days elapsed?
    By jip in forum Dreamweaver AppDev
    Replies: 2
    Last Post: June 30th, 12:28 PM
  2. Display Last 10 days worth of records
    By drewangell in forum Dreamweaver AppDev
    Replies: 1
    Last Post: April 27th, 04:15 PM
  3. Finding the number of days ago
    By Eric in forum UNIX Programming
    Replies: 4
    Last Post: August 21st, 05:03 AM
  4. Range between a number of days
    By Paul Vernon in forum IBM DB2
    Replies: 0
    Last Post: June 26th, 09:54 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139