Get maximum time from a stuff function in SQL

Question

1.00/5 (1 vote)

See more:

Hi ,

I have a stuff function to get the times and I need to get the highest time from that stuff function . Could someone help me please . Tried everything but couldn't find a solution .

EG :

What I have tried:

SQL

SELECT distinct Autoid , TimeSlotId,
				STUFF
				((
					SELECT DISTINCT ', ' + CAST(convert(varchar, mtt.TimeFrom, 0) AS nvarchar(200)) + ' - ' + CAST(convert(varchar, mtt.TimeTo, 0) AS nvarchar(200))
					FROM MST_TBL_BookingConferenceRoom EQ
					LEFT OUTER JOIN MST_TBL_TimeSlot mtt on ',' + mtb.TimeSlotId + ',' like '%,' + cast(mtt.Autoid as nvarchar(200)) + ',%'
					WHERE ',' + mtb.TimeSlotId + ',' like '%,' + cast(mtt.Autoid as nvarchar(200)) + ',%'
					FOR XML PATH('')
				), 1, 2, '') AS TimeBooked
FROM MST_TBL_BookingConferenceRoom  mtb
					LEFT JOIN PPMS_TBL_EmployeeMaster As Em ON mtb.EmployeeID = em.Autoid
					
					LEFT JOIN PPMS_TBL_Designation Des on em.DesignationId = Des.DesignationId
				WHERE 1=1
					
			ORDER BY Autoid DESC

Result :

1. AMC37858 , 10.00 am - 10:30 am,09:30 am - 10.00 am
2. AMC37857 , 09.00 am - 09:30 am,08:30 am - 08.00 am
3. AMC37856 , 06.30 pm - 07:30 pm

I need to get the maximum time from the stuffed times in another column in same query .
Any help will be really appreciated .Thanks in Advance .

Posted 20-Feb-23 19:03pm

Member 12926744

Updated 20-Feb-23 22:15pm

Herman<T>.Instance

v2

Add a Solution

Comments

PIEBALDconsult 21-Feb-23 8:28am

Yeah, avoid string manipulation in SQL. As well as repeated converting and casting. Oh, and distinct, nah. That is all a cry for help.
Do all that in application code instead.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2023-02-20T22:15:00

Simple answer: don't.

SQL is no good at string processing. Rather than trying to extract a value from a string with a load of values stuffed in it, extract the original value directly.

SQL

SELECT DISTINCT 
    Autoid,
    TimeSlotId,
    STUFF((
        SELECT DISTINCT ', ' + CAST(CONVERT(varchar, mtt.TimeFrom, 0) AS nvarchar(200)) + ' - ' + CAST(CONVERT(varchar, mtt.TimeTo, 0) AS nvarchar(200))
        FROM MST_TBL_BookingConferenceRoom EQ
        LEFT OUTER JOIN MST_TBL_TimeSlot mtt ON ',' + mtb.TimeSlotId + ',' LIKE '%,' + cast(mtt.Autoid AS nvarchar(200)) + ',%'
        WHERE ',' + mtb.TimeSlotId + ',' LIKE '%,' + cast(mtt.Autoid AS nvarchar(200)) + ',%' 
        FOR XML PATH('')
    ), 1, 2, '') AS TimeBooked,
    (
        SELECT MAX(CASE WHEN mtt.TimeFrom > mtt.TimeTo THEN mtt.TimeFrom ELSE mtt.TimeTo END)
        FROM MST_TBL_BookingConferenceRoom EQ
        LEFT OUTER JOIN MST_TBL_TimeSlot mtt ON ',' + mtb.TimeSlotId + ',' LIKE '%,' + cast(mtt.Autoid AS nvarchar(200)) + ',%'
        WHERE ',' + mtb.TimeSlotId + ',' LIKE '%,' + cast(mtt.Autoid AS nvarchar(200)) + ',%' 
    ) As MaxTime
FROM
    MST_TBL_BookingConferenceRoom mtb
    LEFT JOIN PPMS_TBL_EmployeeMaster AS Em ON mtb.EmployeeID = em.Autoid
    LEFT JOIN PPMS_TBL_Designation Des ON em.DesignationId = Des.DesignationId
WHERE
    1 = 1
ORDER BY
    Autoid DESC
;

Of course, this assumes your TimeFrom and TimeTo columns are using a proper data type; if you've stored them as strings, then you won't get the expected results, since '10' is less than '9' using string comparison.

Also, your joins suggest that you're storing multiple values in a single field, which is almost never a good idea. As you can probably see now from the complexity of the query required to join your tables. It would be better to redesign your database to normalize the data.