Time based welcome greetings

Question

I have a table named @table1 which have two field one LoginDate and another Name field.

Date	Name
01/10/2024 10:00:00 AM	Mr. Deb Kumar
1/10/2024 12:30:00 PM	Mr. Srinibash

I want to create a view @table2 like below;

Greetings	Name
Good Morning	Mr. Deb Kumar
Good Afternoon	Mr. Srinibash

I want if Date field's Time value is between 6:00:00 to 12:00:00 then Greetings will be Good Morning and if Time value is between 4:00:00 to 5:00:00 then Greetings will be Good Afternoon and so on. How can i do this?

Jignesh Kumar · Answer

Hello SELECT CAST(TourDate AS DATE) as date  EmployeeName AS Name STRING_AGG(CONCAT( CASE WHEN CAST(DATEPART(HOUR [TourDate]) AS INT)

Amit Mohanty · Answer

Check this SELECT CASE WHEN CAST(LoginDate AS TIME) BETWEEN '06:00:00' AND '12:00:00' THEN 'Good Morning' WHEN CAST(LoginDate AS TIME) BETWEEN '12:00:01' AND '16:00:00' THEN 'Good Afternoon' WHEN CAST(LoginDate AS TIME) BETWEEN '16:00:01' AND '20:00:00' THEN 'Good Evening' ELSE 'Good Night' END AS Greetings Name FROM @table1;

Jignesh Kumar · Answer

Hello You can use DatePart function to achieve this CREATE VIEW GreetingView AS SELECT CASE WHEN CAST(DATEPART(HOUR [LoginDate]) AS INT) You can select from GreetingView SELECT * FROM GreetingView;

Jignesh Kumar · Answer

Yo need to do change in string aggrigate function.

Debashish Mahonta · Answer

@Jignesh Kumar sorry for late reply. Your above code is working fine. But I got one problem. I want to add 'And' before the last concate word and full stop symbol at the end. Example: concate word1 word2 word3 and last word.

Debashish Mahonta · Answer

@Jignesh Kumar How can i do that?

Debashish Mahonta · Answer

@Amit Mohanty Thanks for quick reply. I concated two column into one column which TourDate is same and GroupBy Date from below tableTour: TourDate EmployeeName DeparturePlace ArrivalPlace 29/09/2024 11:00:00 AM Mr. Devid Dhaka Khulna 29/09/2024 2:30:00 PM Mr. Devid Khulna Dhaka 01/10/2024 06:00:00 AM Mr. Devid Rangpur Dhaka 01/10/2024 06:30:00 PM Mr. Devid Dhaka Rangpur Code Used: SELECT CAST(TourDate AS DATE) as date  EmployeeName AS Name STRING_AGG(CONCAT('From ' DeparturePlace ' To ' ArrivalPlace) ' ') WITHIN GROUP (ORDER BY TourDate) AS VisitedPlace FROM Tour GROUP BY CAST(TourDate AS DATE) EmployeeName; Result TourDate Name VisitedPlace 29/09/2024 Mr. Devid From Dhaka To Khulna and Khulna to Dhaka. 01/10/2024 Mr. Sam From Rangpur To Dhaka and Dhaka To Rangpur. I want to add 'At Morning' 'At Evening' etc text to start of VisitedPlace text like Below; TourDate Name VisitedPlace 29/09/2024 Mr. Devid At Morning From Dhaka To Khulna and At Afternoon Khulna To Dhaka 01/10/2024 Mr. Sam At Morning From Rajshahi To Rangpur and At Evening Rangpur To Rajshahi How can i do this?