Time based welcome greetings

Debashish Mahonta

1y1.1kSQL

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?

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar1y ago

Accepted answer

Hello,

SELECT
    CAST(TourDate AS DATE) as date ,
    EmployeeName AS Name,
    STRING_AGG(CONCAT( 
	CASE
        WHEN CAST(DATEPART(HOUR, [TourDate]) AS INT) < 12 THEN 'Good Morning '
        WHEN CAST(DATEPART(HOUR, [TourDate]) AS INT) BETWEEN 12 AND 17 THEN 'Good Afternoon '
        ELSE 'Good Evening ' End +
	'From ', DeparturePlace, ' To ', ArrivalPlace), ', ')
        WITHIN GROUP (ORDER BY TourDate) AS VisitedPlace
FROM
    Tour
GROUP BY
    CAST(TourDate AS DATE), EmployeeName;

Debashish Mahonta1y ago
@Jignesh Kumar How can i do that?
Jignesh Kumar1y ago
Yo need to do change in string aggrigate function.
Debashish Mahonta1y ago
@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 Mahonta1y ago

@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?

Jignesh Kumar1y ago

Hello,

You can use DatePart function to achieve this,

CREATE VIEW GreetingView AS
SELECT 
    CASE 
        WHEN CAST(DATEPART(HOUR, [LoginDate]) AS INT) < 12 THEN 'Good Morning, ' + [Name]
        WHEN CAST(DATEPART(HOUR, [LoginDate]) AS INT) BETWEEN 12 AND 17 THEN 'Good Afternoon, ' + [Name]
        ELSE 'Good Evening, ' + [Name]
    END AS GreetingMessage
FROM Table1;

You can select from GreetingView,

SELECT * FROM GreetingView;

Amit Mohanty1y ago

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;