Click here to Skip to main content
15,882,388 members
Please Sign up or sign in to vote.
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
Updated 20-Feb-23 22:15pm
v2
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

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.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900