How to get this design format based on my table data

Question

User-807418713 posted

Hello

This is my MS SQL Table with data

USE [PayRoll]
GO
/****** Object:  Table [dbo].[EMP_Attendance_DateWise]    Script Date: 03/28/2021 16:14:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EMP_Attendance_DateWise](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Attendance_Date] [datetime] NULL,
	[Designation] [varchar](50) NULL,
	[Emp_Code] [varchar](150) NULL,
	[Emp_Name] [varchar](250) NULL,
	[Shift] [nchar](10) NULL,
	[InTime] [varchar](50) NULL,
	[OutTime] [varchar](50) NULL,
	[Status] [varchar](50) NULL,
 CONSTRAINT [PK_EMP_Attendance_DateWise] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[EMP_Attendance_DateWise] ON
INSERT [dbo].[EMP_Attendance_DateWise] ([ID], [Attendance_Date], [Designation], [Emp_Code], [Emp_Name], [Shift], [InTime], [OutTime], [Status]) VALUES (286, CAST(0x0000ACC200000000 AS DateTime), N'STAFF', N'1006', N'AA', N'          ', N'', N'', N'A')
INSERT [dbo].[EMP_Attendance_DateWise] ([ID], [Attendance_Date], [Designation], [Emp_Code], [Emp_Name], [Shift], [InTime], [OutTime], [Status]) VALUES (288, CAST(0x0000ACC200000000 AS DateTime), N'STAFF', N'1007', N'BB', N'          ', N'9:19', N'19:22', N'P')
INSERT [dbo].[EMP_Attendance_DateWise] ([ID], [Attendance_Date], [Designation], [Emp_Code], [Emp_Name], [Shift], [InTime], [OutTime], [Status]) VALUES (289, CAST(0x0000ACC200000000 AS DateTime), N'STAFF', N'1008', N'CC', N'          ', N'', N'', N'A')
INSERT [dbo].[EMP_Attendance_DateWise] ([ID], [Attendance_Date], [Designation], [Emp_Code], [Emp_Name], [Shift], [InTime], [OutTime], [Status]) VALUES (290, CAST(0x0000ACC200000000 AS DateTime), N'STAFF', N'1009', N'DD', N'          ', N'9:49', N'19:04', N'P')
INSERT [dbo].[EMP_Attendance_DateWise] ([ID], [Attendance_Date], [Designation], [Emp_Code], [Emp_Name], [Shift], [InTime], [OutTime], [Status]) VALUES (313, CAST(0x0000ACC300000000 AS DateTime), N'STAFF', N'1006', N'AA', N'          ', N'09:15', N'14:00', N'P')
INSERT [dbo].[EMP_Attendance_DateWise] ([ID], [Attendance_Date], [Designation], [Emp_Code], [Emp_Name], [Shift], [InTime], [OutTime], [Status]) VALUES (314, CAST(0x0000ACC300000000 AS DateTime), N'STAFF', N'1007', N'BB', N'          ', N'9:19', N'19:22', N'P')
INSERT [dbo].[EMP_Attendance_DateWise] ([ID], [Attendance_Date], [Designation], [Emp_Code], [Emp_Name], [Shift], [InTime], [OutTime], [Status]) VALUES (315, CAST(0x0000ACC300000000 AS DateTime), N'STAFF', N'1008', N'CC', N'          ', N'09:10', N'19:00', N'P')
INSERT [dbo].[EMP_Attendance_DateWise] ([ID], [Attendance_Date], [Designation], [Emp_Code], [Emp_Name], [Shift], [InTime], [OutTime], [Status]) VALUES (316, CAST(0x0000ACC300000000 AS DateTime), N'STAFF', N'1009', N'DD', N'          ', N'9:49', N'19:04', N'P')
INSERT [dbo].[EMP_Attendance_DateWise] ([ID], [Attendance_Date], [Designation], [Emp_Code], [Emp_Name], [Shift], [InTime], [OutTime], [Status]) VALUES (317, CAST(0x0000ACC400000000 AS DateTime), N'STAFF', N'1006', N'AA', N'          ', N'', N'', N'WeeklyOff')
INSERT [dbo].[EMP_Attendance_DateWise] ([ID], [Attendance_Date], [Designation], [Emp_Code], [Emp_Name], [Shift], [InTime], [OutTime], [Status]) VALUES (318, CAST(0x0000ACC400000000 AS DateTime), N'STAFF', N'1007', N'BB', N'          ', N'', N'', N'WeeklyOff')
INSERT [dbo].[EMP_Attendance_DateWise] ([ID], [Attendance_Date], [Designation], [Emp_Code], [Emp_Name], [Shift], [InTime], [OutTime], [Status]) VALUES (319, CAST(0x0000ACC400000000 AS DateTime), N'STAFF', N'1008', N'CC', N'          ', N'', N'', N'WeeklyOff')
INSERT [dbo].[EMP_Attendance_DateWise] ([ID], [Attendance_Date], [Designation], [Emp_Code], [Emp_Name], [Shift], [InTime], [OutTime], [Status]) VALUES (320, CAST(0x0000ACC400000000 AS DateTime), N'STAFF', N'1009', N'DD', N'          ', N'', N'', N'WeeklyOff')
INSERT [dbo].[EMP_Attendance_DateWise] ([ID], [Attendance_Date], [Designation], [Emp_Code], [Emp_Name], [Shift], [InTime], [OutTime], [Status]) VALUES (321, CAST(0x0000ACC500000000 AS DateTime), N'STAFF', N'1006', N'AA', N'          ', N'09:10', N'16:00', N'P')
INSERT [dbo].[EMP_Attendance_DateWise] ([ID], [Attendance_Date], [Designation], [Emp_Code], [Emp_Name], [Shift], [InTime], [OutTime], [Status]) VALUES (322, CAST(0x0000ACC500000000 AS DateTime), N'STAFF', N'1007', N'BB', N'          ', N'9:19', N'19:22', N'P')
INSERT [dbo].[EMP_Attendance_DateWise] ([ID], [Attendance_Date], [Designation], [Emp_Code], [Emp_Name], [Shift], [InTime], [OutTime], [Status]) VALUES (323, CAST(0x0000ACC500000000 AS DateTime), N'STAFF', N'1008', N'CC', N'          ', N'09:45', N'10:45', N'P')
INSERT [dbo].[EMP_Attendance_DateWise] ([ID], [Attendance_Date], [Designation], [Emp_Code], [Emp_Name], [Shift], [InTime], [OutTime], [Status]) VALUES (324, CAST(0x0000ACC500000000 AS DateTime), N'STAFF', N'1009', N'DD', N'          ', N'', N'', N'A')
SET IDENTITY_INSERT [dbo].[EMP_Attendance_DateWise] OFF

I want Resultset like this below

If I Pass Date 01-Feb-2021 and 04-Feb-2021 I want resulet like this format

 

Emp Code :	1006	Emp Name	AA
Status	01 FEB - A	02 FEB - P	03-FEB – WO	04-FEB - P
InTime		09:15		09:10
OutTime		14:00		16:00
Duration		04:45		06:50

 

Emp Code :	1007	Emp Name	BB
Status	01 FEB – P	02 FEB - P	03-FEB – WO	04-FEB - P
InTime	09:19	09:19		09:19
OutTime	19:22	19:22		19:22
Duration	10:03	10:03		10:03

 

Emp Code :	1008	Emp Name	CC
Status	01 FEB – A	02 FEB - P	03-FEB – WO	04-FEB - P
InTime		09:10		09:45
OutTime		19:00		10:45
Duration		09:50		01:00

 

 

Emp Code :	1009	Emp Name	DD
Status	01 FEB – P	02 FEB - P	03-FEB – WO	04-FEB - A
InTime	09:49	09:49
OutTime	19:04	19:04
Duration	09:15	09:15

 

How to do so

Thanking you

Answer 1

User-939850651 posted

Hi Gopi.MCA,

How to do so

Based on the information you provided, I still have some confusion about it.

What is the meaning of Duration in the status and how its data is obtained, for example (Duration 10:03), I did not see the corresponding data in the data table.

Could you describe more details?

Best regards,

Xudong Peng

Answer 2

User-807418713 posted

Hello

Here duration is Total InTime - Out Time = Total Hours ( Druations)

example 09:19 - 19:22 = Duration is (10:03) that is  10 hours, 3 minutes

thanking you

Answer 3

User-939850651 posted

Hi Gopi.MCA,

Thanks for the explanation, you can try a query like this:

drop table if exists #temp

DECLARE @START DATE ='2021-02-01'
DECLARE @END DATE ='2021-02-04'

;with cte as (
select [Attendance_Date],FORMAT(CONVERT(date, [Attendance_Date], 105),'dd-MMM')+'-'+ iif([Status]='WeeklyOff','WO',[Status]) [Status]
,[Emp_Code],[Emp_Name],InTime,OutTime
,trim(cast(DATEDIFF(MINUTE,InTime,OutTime)/60 as char(2)))+':'+right('0'+trim(cast(DATEDIFF(MINUTE,InTime,OutTime)%60 as char(2))),2) Duration
from [EMP_Attendance_DateWise]
WHERE [Attendance_Date] BETWEEN @START AND @END)
select [Status],Emp_Code,Emp_Name,InTime,OutTime,IIF(Duration='0:00','',Duration) Duration 
,ROW_NUMBER() OVER (PARTITION BY [Attendance_Date] ORDER BY [Emp_Code]) RN
into #temp 
from  cte

;WITH CTEAA AS (
Select Emp_Code,Emp_Name,COL,[01-Feb-A],[02-Feb-P],[03-Feb-WO],[04-Feb-P] from (
    select Emp_Code,Emp_Name,STATUS ,COL,VAL from #temp  
    CROSS APPLY (VALUES ('InTime',InTime),
    ('OutTime',OutTime),
    ('Duration',Duration))CS (COL,VAL)
	WHERE RN=1)T
    PIVOT (MAX(VAL) FOR STATUS IN ([01-Feb-A],[02-Feb-P],[03-Feb-WO],[04-Feb-P]))PVT)
,CTEAA1 AS (
SELECT 'Emp_Code' COL1,Emp_Code COL2,'Emp_Name' COL3,Emp_Name COL4,'' COL5 FROM CTEAA
UNION
SELECT 'Status','01-Feb-A','02-Feb-P','03-Feb-WO','04-Feb-P'
UNION
SELECT COL,[01-Feb-A],[02-Feb-P],[03-Feb-WO],[04-Feb-P] FROM CTEAA
)
SELECT * FROM CTEAA1 
ORDER BY CASE WHEN  COL1='Emp_Code' THEN 0 WHEN  COL1='Status' THEN 1 WHEN COL1='InTime' THEN 2 WHEN COL1='OutTime' THEN 3 ELSE 4 END


;WITH CTEBB AS (
Select Emp_Code,Emp_Name,COL,[01-Feb-P],[02-Feb-P],[03-Feb-WO],[04-Feb-P] from (
    select Emp_Code,Emp_Name,STATUS ,COL,VAL from #temp  
    CROSS APPLY (VALUES ('InTime',InTime),
    ('OutTime',OutTime),
    ('Duration',Duration))CS (COL,VAL)
	WHERE RN=2)T
    PIVOT (MAX(VAL) FOR STATUS IN ([01-Feb-P],[02-Feb-P],[03-Feb-WO],[04-Feb-P]))PVT)
,CTEBB1 AS (
SELECT 'Emp_Code' COL1,Emp_Code COL2,'Emp_Name' COL3,Emp_Name COL4,'' COL5 FROM CTEBB
UNION
SELECT 'Status','01-Feb-P','02-Feb-P','03-Feb-WO','04-Feb-P'
UNION
SELECT COL,[01-Feb-P],[02-Feb-P],[03-Feb-WO],[04-Feb-P] FROM CTEBB
)
SELECT * FROM CTEBB1 
ORDER BY CASE WHEN  COL1='Emp_Code' THEN 0 WHEN  COL1='Status' THEN 1 WHEN COL1='InTime' THEN 2 WHEN COL1='OutTime' THEN 3 ELSE 4 END

 
 ;WITH CTECC AS (
Select Emp_Code,Emp_Name,COL,[01-Feb-A],[02-Feb-P],[03-Feb-WO],[04-Feb-P] from (
    select Emp_Code,Emp_Name,STATUS ,COL,VAL from #temp  
    CROSS APPLY (VALUES ('InTime',InTime),
    ('OutTime',OutTime),
    ('Duration',Duration))CS (COL,VAL)
	WHERE RN=3)T
    PIVOT (MAX(VAL) FOR STATUS IN ([01-Feb-A],[02-Feb-P],[03-Feb-WO],[04-Feb-P]))PVT)
,CTECC1 AS (
SELECT 'Emp_Code' COL1,Emp_Code COL2,'Emp_Name' COL3,Emp_Name COL4,'' COL5 FROM CTECC
UNION
SELECT 'Status','01-Feb-P','02-Feb-P','03-Feb-WO','04-Feb-P'
UNION
SELECT COL,[01-Feb-A],[02-Feb-P],[03-Feb-WO],[04-Feb-P] FROM CTECC
)
SELECT * FROM CTECC1 
ORDER BY CASE WHEN  COL1='Emp_Code' THEN 0 WHEN  COL1='Status' THEN 1 WHEN COL1='InTime' THEN 2 WHEN COL1='OutTime' THEN 3 ELSE 4 END

;WITH CTEDD AS (
Select Emp_Code,Emp_Name,COL,[01-Feb-P],[02-Feb-P],[03-Feb-WO],[04-Feb-A] from (
    select Emp_Code,Emp_Name,STATUS ,COL,VAL from #temp  
    CROSS APPLY (VALUES ('InTime',InTime),
    ('OutTime',OutTime),
    ('Duration',Duration))CS (COL,VAL)
	WHERE RN=4)T
    PIVOT (MAX(VAL) FOR STATUS IN ([01-Feb-P],[02-Feb-P],[03-Feb-WO],[04-Feb-A]))PVT)
,CTEDD1 AS (
SELECT 'Emp_Code' COL1,Emp_Code COL2,'Emp_Name' COL3,Emp_Name COL4,'' COL5 FROM CTEDD
UNION
SELECT 'Status','01-Feb-P','02-Feb-P','03-Feb-WO','04-Feb-A'
UNION
SELECT COL,[01-Feb-P],[02-Feb-P],[03-Feb-WO],[04-Feb-A] FROM CTEDD
)
SELECT * FROM CTEDD1 
ORDER BY CASE WHEN  COL1='Emp_Code' THEN 0 WHEN  COL1='Status' THEN 1 WHEN COL1='InTime' THEN 2 WHEN COL1='OutTime' THEN 3 ELSE 4 END

Result:

If I still understand anything wrong, please point it out.

Best regards,

Xudong Peng

Answer 4

User-807418713 posted

Hello

Thanks for your code

nut here we cant write each date for each employee like this 01-FEB-A

it has to come automtivally using pivot cox we can pass any date for example i can pass 13-MAr-2021 to 19-MAR-2021 also any date i can pass so we cant fix static like the above whats the other solution for this dynamic dates

Answer 5

User-939850651 posted

Hi Gopi.MCA,

it has to come automtivally using pivot cox we can pass any date for example i can pass 13-MAr-2021 to 19-MAR-2021 also any date i can pass so we cant fix static like the above whats the other solution for this dynamic dates

If you just need to transpose the row value into a column, you can use PIVOT, but with the different parameters you pass in, the column name will also change (not fixed), and it's not even sure how many columns there will be.

Therefore, I am afraid that T-SQL cannot be used to achieve this requirement.

Best regards,

Xudong Peng

Answer 6

User-807418713 posted

HELLO

Thank you for your reply

For example i have shown that data of 4 employee it can be 1000 of employee and we will fetect data month wise or week wise of any month it should come dynamic using pivot or something like that

Please help with dynamic query

Thanking You

s kind and humble request thank you and thanks again