-
Nov 18th, 2020, 02:44 AM
#1
Thread Starter
Fanatic Member
[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:
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.
-
Nov 18th, 2020, 02:51 AM
#2
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.
-
Nov 18th, 2020, 02:57 AM
#3
Thread Starter
Fanatic Member
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.
-
Nov 18th, 2020, 08:17 AM
#4
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|