Sql split into 30 minute time interval

Question

0.00/5 (No votes)

See more:

Hi,

Purpose: I work in Hospitality Industry. I want to understand at what time the Restaurant is full and what time it is less busy. I have the opening and closing times, I want to split it 30 minute interval period.

I would really appreciate if you could ease help me.

Thanking you in advance

Table

Check# Open	CloseTime
25484	17:34	18:06
25488	18:04	21:22

Quote:
Result
Check # Open CloseTime
25484 17:34 18:00
25484 18:00 18:30
25488 18:04 18:30
25488 18:30 19:00
25488 19:00 19:30
25488 19:30 20:00
25488 20:00 20:30
25488 20:30 21:00
25488 21:00 21:30

What I have tried:

I am new to SQL. I am good at Excel, but due to its limitations i want to use SQL. I just know the basics in SQL.

Posted 22-Apr-22 23:09pm

VN Hari

Updated 26-Apr-22 11:17am

Add a Solution

2 solutions

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)

Maciej Los · Accepted Answer · 2022-04-26T11:17:00

Depending on database provider, you can use Common Table Expressions[^], which can be used in recursive mode.
SQL Server:
WITH common_table_expression (Transact-SQL) - SQL Server | Microsoft Docs[^]
SQL Server Common Table Expressions (CTE)[^]

For example:

SQL

;WITH CTE AS
(
  --initial data
  SELECT 1 AS Lvl, CheckNo, CAST(OpenDate AS DATETIME) + CAST(OpenTime AS DATETIME) AS OT, 
    DATEADD(MI, 30, CAST(OpenDate AS DATETIME) + CAST(OpenTime AS DATETIME)) AS CT,
    CAST(OpenDate AS DATETIME) + CAST(CloseTime AS DATETIME) AS OCT
  FROM InitialData
  --recursive part
  UNION ALL
  SELECT Lvl + 1 AS Lvl, CheckNo, CT AS OT, DATEADD(mi, 30, CT), OCT
  FROM CTE
  WHERE DATEADD(mi, 30, OT) < OCT
)
SELECT Lvl, CheckNo, OT AS OpenTime, CT AS CloseTime 
FROM CTE
ORDER BY CheckNo, Lvl

Sample output:

Lvl 	CheckNo 	OpenTime 	 	 	 	 	CloseTime
1 	 	25484 	 	2022-04-26 17:34:00.000 	2022-04-26 18:04:00.000
1 	 	25484 	 	2022-04-26 18:06:00.000 	2022-04-26 18:36:00.000
2 	 	25484 	 	2022-04-26 18:36:00.000 	2022-04-26 19:06:00.000
3 	 	25484 	 	2022-04-26 19:06:00.000 	2022-04-26 19:36:00.000
4 	 	25484 	 	2022-04-26 19:36:00.000 	2022-04-26 20:06:00.000
5 	 	25484 	 	2022-04-26 20:06:00.000 	2022-04-26 20:36:00.000
6 	 	25484 	 	2022-04-26 20:36:00.000 	2022-04-26 21:06:00.000
7 	 	25484 	 	2022-04-26 21:06:00.000 	2022-04-26 21:36:00.000

SQL Server 2019 | db<>fiddle[^]

Good luck!

OriginalGriff · Accepted Answer · 2022-04-22T23:11:00

While we are more than willing to help those that are stuck, that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

If you are having problems getting started at all, then this may help: How to Write Code to Solve a Problem, A Beginner's Guide[^]

Sql split into 30 minute time interval

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0