×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Derive staus date time by aligning start and end datetime

Derive staus date time by aligning start and end datetime

Derive staus date time by aligning start and end datetime

(OP)
Hi,

I would like to derive the status datetime from joining and aligning start datetime and end datetime from original data as below.

CODE

APPT_I	     STUS_C	   STRT_S			END_S
123		APPT 	02/10/2023 14:10:31	?
123		APPT 	02/10/2023 14:10:31	09/10/2023 08:00:06 -- take this record
123		DRFR 	09/10/2023 08:00:06	?
123		DRFR 	09/10/2023 08:00:06	10/10/2023 10:50:09 -- take this record
123		APIP 	10/10/2023 10:50:09	?
123		APIP 	10/10/2023 10:50:09	11/10/2023 09:41:46 -- take this record
123		APPC 	11/10/2023 09:41:46	?
123		APPC 	11/10/2023 09:41:46	16/10/2023 11:28:26 -- take this record
123		APPT 	16/10/2023 11:28:26	16/10/2023 11:33:35 -- take this record
123		APIP 	16/10/2023 11:33:35	16/10/2023 11:33:57 -- take this record
123		APPC 	16/10/2023 11:33:57	?
123		APPC 	16/10/2023 11:33:57	27/10/2023 09:46:33 -- take this record
123		NPWI 	27/10/2023 09:46:33	?                   -- take this record 

I would like to take the record which has end datetime as it is aligned with start datetime plus additional the last record but without end date time hence expected result as per below

CODE

APPT_I	STUS_C	STUS_T
123	APPT 	02/10/2023 14:10:31
123	DRFR 	09/10/2023 08:00:06
123	APIP 	10/10/2023 10:50:09
123	APPC 	11/10/2023 09:41:46
123	APPT 	16/10/2023 11:28:26
123	APIP 	16/10/2023 11:33:35
123	APPC 	16/10/2023 11:33:57
123	NPWI 	27/10/2023 09:46:33 

it may be simple for some of you but i couldn't figure out to take the last record.
Any practical query answer would be greatly appreciated.

Thanks,

RE: Derive staus date time by aligning start and end datetime

Are there really questionmarks in that field, or are that null values? I hope it's null, because if it's ? those fields all are character and not sortable in chronological order unless first converted to datetime, because in that formatting the datetimes would be sorted mainly by the day portion of dates, not mainly by year and so on.

CODE

Select APPT_I, STUS_C, STRT_S as STUS_T FROM yourtable WHERE NOT END_S IS NULL
UNION Select APPT_I, STUS_C, STRT_S as STUS_T FROM yourtable WHERE END_S IS NULL
ORDER BY 3 

At first glance this looks like splitting up records into two groups and putting them together, but since this is only a UNION and not a UNION ALL, it will not repeat records, so all the records with no END_S are not repeated unless they are the only record, i.e. the last one without an end datetime, which is exactly what you need.

Chriss

RE: Derive staus date time by aligning start and end datetime

(OP)
Thanks, Chriss,

I was thinking of joining the same tables to match between start and end times.
didn't think the union would work... smart!

However, it only works if the STRT_S between null and not null are the same values, how if they are different values but I still would like to take the values with END_S like sample below

CODE

APPT_I	     STUS_C	   STRT_S			END_S
123		APPT 	02/10/2023 14:10:31	?
123		APPT 	02/10/2023 14:12:31	09/10/2023 08:00:06 -- take this record
123		DRFR 	09/10/2023 08:00:06	?
123		DRFR 	09/10/2023 08:00:06	10/10/2023 10:50:09 -- take this record
123		APIP 	10/10/2023 10:50:09	?
123		APIP 	10/10/2023 10:55:09	11/10/2023 09:41:46 -- take this record
123		APPC 	11/10/2023 09:41:46	?
123		APPC 	11/10/2023 09:41:46	16/10/2023 11:28:26 -- take this record
123		APPT 	16/10/2023 11:28:26	16/10/2023 11:33:35 -- take this record
123		APIP 	16/10/2023 11:33:35	16/10/2023 11:33:57 -- take this record
123		APPC 	16/10/2023 11:33:57	?
123		APPC 	16/10/2023 11:33:57	27/10/2023 09:46:33 -- take this record
123		NPWI 	27/10/2023 09:46:33	?                   -- take this record 

And expected result as below

CODE

APPT_I	STUS_C	STUS_T
123	APPT 	02/10/2023 14:12:31
123	DRFR 	09/10/2023 08:00:06
123	APIP 	10/10/2023 10:50:09
123	APPC 	11/10/2023 09:41:46
123	APPT 	16/10/2023 11:28:26
123	APIP 	16/10/2023 11:33:35
123	APPC 	16/10/2023 11:33:57
123	NPWI 	27/10/2023 09:46:33 

RE: Derive staus date time by aligning start and end datetime

CODE

...
UNION SELECT APPT_I, STUS_C, STRT_S as STUS_T FROM yourtable WHERE STRT_S IN (SELECT MAX(STRT_S) FROM yourtable WHERE END_S IS NULL)
ORDER BY 3 
untested.

Chriss

RE: Derive staus date time by aligning start and end datetime

(OP)
Thanks Chris,

The time values are not always higher(maximum) though, it is only an example but the data may have different values

CODE

APPT_I	     STUS_C	   STRT_S			END_S
123		APPT 	02/10/2023 14:10:31	?
123		APPT 	02/10/2023 14:12:31	09/10/2023 08:00:06 -- take this record
123		DRFR 	09/10/2023 08:00:06	?
123		DRFR 	09/10/2023 08:00:06	10/10/2023 10:50:09 -- take this record
123		APIP 	10/10/2023 10:50:09	?
123		APIP 	10/10/2023 10:55:09	11/10/2023 09:41:46 -- take this record
123		APPC 	11/10/2023 09:41:46	?
123		APPC 	11/10/2023 09:40:46	16/10/2023 11:28:26 -- take this record
123		APPT 	16/10/2023 11:28:26	16/10/2023 11:33:35 -- take this record
123		APIP 	16/10/2023 11:33:35	16/10/2023 11:33:57 -- take this record
123		APPC 	16/10/2023 11:33:57	?
123		APPC 	16/10/2023 11:33:57	27/10/2023 09:46:33 -- take this record
123		NPWI 	27/10/2023 09:46:33	?                   -- take this record 

And expected result is below

CODE

APPT_I	STUS_C	STUS_T
123	APPT 	02/10/2023 14:12:31
123	DRFR 	09/10/2023 08:00:06
123	APIP 	10/10/2023 10:50:09
123	APPC 	11/10/2023 09:40:46
123	APPT 	16/10/2023 11:28:26
123	APIP 	16/10/2023 11:33:35
123	APPC 	16/10/2023 11:33:57
123	NPWI 	27/10/2023 09:46:33 

RE: Derive staus date time by aligning start and end datetime

Then you can't get the last row with SQL alone, SQL will always need a specific sorting to be able to reference a next/prevcious row with LAG or LEAD and MAX obviously also takes the last record of sorted data. PARTITION, OVER and RANK also need some sorting. That's also what I already pointed out in your other thread. There even is a LAST_VALUE() and FIRST_VALUE() functrion, but they all operate only on a defined sorting.

So if this isn't sorted by dates, you have to have any other column by which you can sort. Otherwise all these functions can't work for you. In an unsorted SELECT * FROM table you do also get records in some order, but that's not allowing to use LAST_VALUE on this result, the query has to define a specific sort order. For example SELECT Last_value(STRT_S) FROM yourtable will result in the error 'The function 'last_value' must have an OVER clause.' which already is clear from the definiion and helpt topic about LAST_VALUE().

If you ignore demands, I can't help. If you find it offensive that I have demands and questions while you think your specification is good enough, I am only the messenger, I didn't define the T-SQL as it is defined, but I can only use it as it's meant to be used. So when I demand an sort order or assume one, that's just because it is necessary.

Chriss

RE: Derive staus date time by aligning start and end datetime

Something is wrong with your highlighting, I guess. First of all I mainly took the condition NOT END_S IS NULL from "take this records" marking. You're having 10/10/2023 10:55:09 bold and "take this record", but it is not in your desired result. I think you should at first get your demands straight and then ask again.

Chriss

RE: Derive staus date time by aligning start and end datetime

Are the 4 fields (APPT_I, STUS_C, STRT_S, and END_S) the only fields in your table?
If not, what other data do you have in there? Maybe there is/are something that could be used... ponder

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close