How can we display 2 record in one row of grid view using SQL query

Question

3.00/5 (2 votes)

See more:

Hello,
I have one table like,..
Break and column name
BreakId bigint pk,BreakTypeIn int,BreakTimeIn datetime,BreakTypeOut int,BreakTimeOut
datetime,CreateDateTime,UpdateDateTime.

on date 26/10/2016 two record are there.like
BreakId BreakTypeIn BreakTimeIn BreakTypeOut BreakTimeOut CreateDateTime
1 1 12:03 2 12:13 2016-10-19 12:03:55.350
2 1 12:44 2 12:48 2016-10-19 12:44:48.813

so First timebreak spent is 10 minutes and second is 4 minutes.
I want to display this record in gridview in same row like that..

VB.NET

StarofDay BreakIn  BreakOut BreakSpent BreakIn  BreakOut BreakSpent OutTime  TimeSpent
12:52PM	  12:03	    12:13   00:10	 12:44    12:48   00:04     4:21PM	03:15

Sub Total	                                                                03:15
Grand Total	                                                                03:15

how can we get this kind output in gridview .

What I have tried:

SQL

CONVERT(varchar(5),ISNULL(DATEADD(minute, DATEDIFF(minute, AttendanceTimeIn, ISNULL(AttendanceTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))), 0),0)-
         ISNULL(DATEADD(minute, DATEDIFF(minute, breakTimeIn, ISNULL(breakTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))), 0),0)  AS [TimeSpentWork],
DATEDIFF(minute, AttendanceTimeIn, ISNULL(AttendanceTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30'')))-
          ISNULL(DATEDIFF(minute, breakTimeIn, ISNULL(breakTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))),0) ) AS [TimeSpentMinutesWork]

Posted 27-Oct-16 21:27pm

Member 12097108

Updated 1-Nov-16 0:27am

Add a Solution

Comments

Wendelius 28-Oct-16 10:52am

What is the common factor for the breaks on a single row? Is it for example theat they are on the same date?

What happens if there is only one break or if there are three breaks or more for the same day? How should these be shown?

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)

Member 10992268 · Answer 1 · 2016-11-01T00:27:00

Solution 1

Select * From ( Select distinct row_number() over(order by "Column Name"
(
Select temp1.Column_Name + ',' AS [text()]
From #TEMP temp1
Where "Condition"
ORDER BY "Column Name"
For XML PATH ('')
) ["Display Name"]
From #TEMP temp2
) [Main]

Posted 1-Nov-16 0:27am

Member 10992268

v2

Comments

Member 12097108 2-Nov-16 1:28am

Hey..I am not able to understood this query can you please give me the example.