Derive staus date time by aligning start and end datetime

[peac3]

Hi,

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

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

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,

 

[Chris Miller]

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.

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

 

[peac3]

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

APPT_I	     STUS_C	   STRT_S			END_S
123		APPT 	02/10/2023 14:10:31	?
123		APPT 	[b]02/10/2023 14:12:31[/b]	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

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

 

[Chris Miller]

...
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

 

[peac3]

Thanks Chris,

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

APPT_I	     STUS_C	   STRT_S			END_S
123		APPT 	02/10/2023 14:10:31	?
123		APPT 	[b]02/10/2023 14:12:31[/b]	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 	[b]10/10/2023 10:55:09[/b]	11/10/2023 09:41:46 -- take this record
123		APPC 	11/10/2023 09:41:46	?
123		APPC 	[b]11/10/2023 09:40:46[/b]	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

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

 

[Chris Miller]

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

 

[Chris Miller]

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

 

[Andrzejek]

Are the 4 fields ([tt]APPT_I, STUS_C, STRT_S,[/tt] and[tt] END_S[/tt]) 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...

---- Andy

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