Results 1 to 4 of 4

Thread: [RESOLVED] SQL - translate date to sequence as integer - timeline

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2018
    Posts
    514

    Resolved [RESOLVED] SQL - translate date to sequence as integer - timeline

    Hi
    Is there a way to somehow translate/convert/map dates to a number that indicates the chronology of which record was created first?
    What I need is to have a column that looks at my date column and translate that to e.g. 0,1,2 chronologically:

    Name:  Sequence.jpg
Views: 144
Size:  29.8 KB


    Code:
    DVID	        INID	Date	      Sequence
    DV0048236	4588	 07-01-2020	1
    DV0048236	4218	 05-01-2020	0
    DV0053982	2356	 07-05-2019	0
    DV0053982	1245	 07-12-2020	2
    DV0053982	1195	 07-09-2019	1

    Thanks for any help.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: SQL - translate date to sequence as integer - timeline

    Is this for SQL Server? If so:

    https://docs.microsoft.com/en-us/sql...l-server-ver15

    I just realised that you actually want separate sequences for each DVID value. I suspect that ROW_NUMBER can still be used but I'm not sure of the specifics.

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2018
    Posts
    514

    Re: SQL - translate date to sequence as integer - timeline

    Yes, SQL server. And also yes, separate sequence for each DVID; each DVID can have multiple INID and those INIDs have different dates. will check that RoW_number now....

    Thanks.

    Edit:
    it seems to work:

    Code:
    SELECT 
      DEVIATION_NUMBER, INVESTIGATION_ID,IN_CreatedDate,
      ROW_NUMBER() OVER(PARTITION BY DEVIATION_NUMBER ORDER BY IN_CreatedDate ASC) AS #Sequence
    FROM DMS_DV_Data_NEW_IN 
    Where INVESTIGATION_ID is not null
    Thanks again
    Last edited by Grand; Nov 18th, 2020 at 03:21 AM.

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: [RESOLVED] SQL - translate date to sequence as integer - timeline

    Yeah, ROW_NUMBER, PARTION BY the DIVID ordered by the Create Date should give you what you want. Since Ascending is the default order, it's not necessary to specify it. Also I wouldn't use "#" in the field name.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

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



Click Here to Expand Forum to Full Width