How can we store two or more times value in column in SQL server

Question

0.00/5 (No votes)

See more:

Hello,I have column in table like TeaTimeIn,TeaTimeOut.
very first time
TeaTimeIn = 12:10 pm,
TeaTimeOut= 12:20 pm,
So TeaBreakTimeSpent is 10 minutes.

second time we take new tea break that time our earlyear TeaBreakTimeSpent is removed and take new value.but I want like this If we take number of teabreak the first time teabreatime duration will be store using query if we take another tea break so that time earlyear timeduration and new time duration will be added and display sum of this time duration.
like..
first time

TeaTimeIn = 12:10 pm,
TeaTimeOut= 12:20 pm,
So TeaBreakTimeSpent is 10 minutes.

Second time

TeaTimeIn = 3:30 pm,
TeaTimeOut= 3:35 pm,
So TeaBreakTimeSpent is 5 minutes.

third time
TeaTimeIn = 6:10 pm,
TeaTimeOut= 6:20 pm,
So TeaBreakTimeSpent is 10 minutes.

so all teabreaktimespent display like 25 minutes.
If it possible to store first time of TeaBreakTimeSpent dynamically in query and second time of TeaBreakTimeSpent dynamically in query and third time of TeaBreakTimeSpent dynamically in query and after sum all timeDuration of Teabreak and display it.

What I have tried:

SQL

SELECT CONVERT(VARCHAR, CreateDateTime,105) AS [LoginDate],
CONVERT(varchar(15),CAST(StartTimeIn AS TIME),100) AS [STime],
CONVERT(varchar(15),CAST(EndTimeOut AS TIME),100) AS [ETime],
CONVERT(varchar(15),CAST(MealTimeIn AS TIME),100) AS [MTime],
CONVERT(varchar(15),CAST(MealTimeOut AS TIME),100) AS [MOUT],
CONVERT(varchar(15),CAST(TeaTimeIn AS TIME),100) AS [TTime],
CONVERT(varchar(15),CAST(TeaTimeOut AS TIME),100) AS [TOUT],
CONVERT(varchar(5), DATEADD(minute, DATEDIFF(minute, StartTimeIn, ISNULL(EndTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))), 0), 114) AS [TimeSpent]  ,
DATEDIFF(minute, StartTimeIn, ISNULL(EndTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))) AS [TimeSpentMinutes],

CONVERT(varchar(5), DATEADD(minute, DATEDIFF(minute, MealTimeIn, ISNULL(MealTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))), 0), 114) AS [MealTimeSpent]  ,
DATEDIFF(minute, MealTimeIn, ISNULL(MealTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))) AS [MealTimeSpentMinutes],

CONVERT(varchar(5), DATEADD(minute, DATEDIFF(minute, TeaTimeIn, ISNULL(TeaTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))), 0), 114) AS [TeaTimeSpent]  ,
DATEDIFF(minute, TeaTimeIn, ISNULL(TeaTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))) AS [TeaTimeSpentMinutes],

CONVERT(varchar(5), DATEADD(minute, DATEDIFF(minute, OtherTimeIn, ISNULL(OtherTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))), 0), 114) AS [OtherTimeSpent]  ,
DATEDIFF(minute, OtherTimeIn, ISNULL(OtherTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))) AS [OtherTimeSpentMinutes],

 CONVERT(varchar(5),ISNULL(DATEADD(minute, DATEDIFF(minute, StartTimeIn, ISNULL(EndTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))), 0),0)-
        (ISNULL(DATEADD(minute, DATEDIFF(minute, MealTimeIn, ISNULL(MealTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(),'+05:30'))), 0),0) +
         ISNULL(DATEADD(minute, DATEDIFF(minute, TeaTimeIn, ISNULL(TeaTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))), 0),0) +
	     ISNULL(DATEADD(minute, DATEDIFF(minute, OtherTimeIn, ISNULL(OtherTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))), 0),0)),114) AS [TimeSpentWork],

		 DATEDIFF(minute, StartTimeIn, ISNULL(EndTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')))-
         (ISNULL(DATEDIFF(minute, MealTimeIn, ISNULL(MealTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))),0)+
          ISNULL(DATEDIFF(minute, TeaTimeIn, ISNULL(TeaTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))),0)+
          ISNULL(DATEDIFF(minute, OtherTimeIn, ISNULL(OtherTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))),0) ) AS [TimeSpentMinutesWork]

FROM  
DailyTimeRecord

Posted 12-Oct-16 0:59am

Member 12097108

Updated 12-Oct-16 1:22am

Add a Solution

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)

OriginalGriff · Answer 1 · 2016-10-12T01:22:00

Don't try to store multiple values in the same column, instead use a separate table which refers back to the primary table.
So if you have an Employees table:

ID      INT
Name    NVARCHAR

Add a Breaks table:

C#

ID      INT
EmpID   INT, FOREIGN KEY to Employees.ID
BkStart DATETIME
BkEnd   DATETIME

You can then use a JOIN to retrieve all the break for a given date:

C#

SELECT e.Name, b.BkStart, b.BkEnd FROM Employees e
JOIN Breaks b ON b.EmpID = e.ID
WHERE b.BkStart BETWEEN '2016-10-11' AND '2016-10-12'

And as I told you two hours ago: don't store dates and times in NVARCHAR - always use an appropriate datatype. NVARCHAR may make your life simple to start with but it makes your life a lot, lot more complex as soon as you start to do anything with the data.