How to count pivoted column value in SQL Server

Last Reply 3 months ago By kalpesh

Posted 3 months ago

 

select *
from 
(
 select dense_rank() OVER (ORDER BY card_no) as [Sl. No.], card_no as [Card Number],employee_fname as [Employee Name],
 DeptName as [Department] ,Designation, 
 case when Att_PunchRecDate is not null then 'P' when TWeekDay='Sun' then 'W' WHEN CAST(TDate AS DATE) >= CAST(GETDATE() AS DATE) then ''  else 'A' end as val,TDateName,'' as Remark ,COUNT(CASE when Att_PunchRecDate is not null then 1  ELSE 0 END) as [Total Present] from #AttRec 
) src
pivot
(
  max(val)
  for TDateName in ( [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30])
) piv;
Posted 3 months ago Modified on 3 months ago

As Per your Sql query i make same temporary table and created same test query where it calculate the total of Absent and Present. Added the sub query within same select statement where you can use result for Pivot. Refer the test query and implement it by your logic.

SQL

CREATE TABLE #DaysInMonth([Date] DATETIME)
CREATE TABLE #employees (Card_No NVARCHAR(50),department_id INT,employee_fname VARCHAR(50),employee_code VARCHAR(50),designation_id INT)
CREATE TABLE #Tran_DeviceAttRec_New (Card_Number NVARCHAR(50),Att_PunchRecDate DATETIME,Punch_month DATETIME)
CREATE TABLE #emp_common_master(id INT,name VARCHAR(50))

INSERT INTO #DaysInMonth VALUES('2017-04-01'),('2017-04-02'),('2017-04-03'),('2017-04-04'),('2017-04-05'),('2017-04-06'),('2017-04-07'),('2017-04-08'),('2017-04-09'),('2017-04-10'),('2017-04-11'),('2017-04-12'),('2017-04-13'),('2017-04-14'),('2017-04-15'),('2017-04-16'),('2017-04-17'),('2017-04-18'),('2017-04-19'),('2017-04-20'),('2017-04-21'),('2017-04-22'),('2017-04-23'),('2017-04-24'),('2017-04-25'),('2017-04-26'),('2017-04-27'),('2017-04-28'),('2017-04-29'),('2017-04-30')
INSERT INTO #emp_common_master VALUES (1 , 'common master'),(2 , 'common master 2'),(3 , 'common master 3'),(4 , 'common master 4'),(5 , 'common master 5')
INSERT INTO #employees VALUES('111',1,'F Name1','E001',1),('112',1,'F Name2','E002',1),('113',2,'F Name3','E003',2),('114',2,'F Name4','E004',2),('115',3,'F Name5','E005',3),('116',3,'F Name6','E006',3)

INSERT INTO #Tran_DeviceAttRec_New VALUES('111','2017-04-01','2017-04-01'),('111','2017-04-02','2017-04-02'),('111','2017-04-03','2017-04-03'),('111','2017-04-04','2017-04-04'),('111','2017-04-05','2017-04-05'),('111','2017-04-06','2017-04-06'),('111','2017-04-07','2017-04-07'),('111','2017-04-08','2017-04-08'),('111','2017-04-10','2017-04-10'),('111','2017-04-11','2017-04-11'),('111','2017-04-12','2017-04-12'),('111','2017-04-13','2017-04-13'),('111','2017-04-14','2017-04-14'),('111','2017-04-15','2017-04-15'),('111','2017-04-17','2017-04-17'),('111','2017-04-18','2017-04-18'),('111','2017-04-19','2017-04-19'),('111','2017-04-20','2017-04-20')
,('112','2017-04-01','2017-04-01'),('112','2017-04-02','2017-04-02'),('112','2017-04-03','2017-04-03'),('112','2017-04-04','2017-04-04'),('112','2017-04-05','2017-04-05'),('112','2017-04-06','2017-04-06'),('112','2017-04-07','2017-04-07'),('112','2017-04-08','2017-04-08'),('112','2017-04-10','2017-04-10'),('112','2017-04-11','2017-04-11'),('112','2017-04-12','2017-04-12'),('112','2017-04-13','2017-04-13'),('112','2017-04-14','2017-04-14'),('112','2017-04-15','2017-04-15'),('112','2017-04-17','2017-04-17'),('112','2017-04-18','2017-04-18'),('112','2017-04-19','2017-04-19'),('112','2017-04-20','2017-04-20')
,('113','2017-04-01','2017-04-01'),('113','2017-04-02','2017-04-02'),('113','2017-04-03','2017-04-03'),('113','2017-04-04','2017-04-04'),('113','2017-04-05','2017-04-05'),('113','2017-04-06','2017-04-06'),('113','2017-04-07','2017-04-07'),('113','2017-04-08','2017-04-08')
,('114','2017-04-01','2017-04-01'),('114','2017-04-02','2017-04-02'),('114','2017-04-03','2017-04-03'),('114','2017-04-04','2017-04-04'),('114','2017-04-05','2017-04-05'),('114','2017-04-06','2017-04-06'),('114','2017-04-07','2017-04-07'),('114','2017-04-14','2017-04-14'),('114','2017-04-15','2017-04-15'),('114','2017-04-17','2017-04-17'),('114','2017-04-18','2017-04-18')
,('115','2017-04-01','2017-04-01'),('115','2017-04-02','2017-04-02'),('115','2017-04-03','2017-04-03'),('115','2017-04-13','2017-04-13'),('115','2017-04-14','2017-04-14'),('115','2017-04-15','2017-04-15'),('115','2017-04-17','2017-04-17'),('115','2017-04-18','2017-04-18'),('115','2017-04-19','2017-04-19'),('115','2017-04-20','2017-04-20')
,('116','2017-04-01','2017-04-01'),('116','2017-04-02','2017-04-02'),('116','2017-04-05','2017-04-05'),('116','2017-04-06','2017-04-06'),('116','2017-04-07','2017-04-07'),('116','2017-04-08','2017-04-08'),('116','2017-04-10','2017-04-10'),('116','2017-04-11','2017-04-11'),('116','2017-04-12','2017-04-12'),('116','2017-04-13','2017-04-13'),('116','2017-04-14','2017-04-14'),('116','2017-04-15','2017-04-15'),('116','2017-04-17','2017-04-17'),('116','2017-04-18','2017-04-18'),('116','2017-04-19','2017-04-19'),('116','2017-04-20','2017-04-20')


DECLARE @StartDate DATETIME = '04/04/2017'
		,@EndDate DATETIME = '04/30/2017'
		,@CardNo NVARCHAR(50) = ''
		,@Depid INT = 0

DECLARE @date DATETIME;
SET @date = DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0);

WITH DaysInMonth
AS (SELECT @date AS DATE
	UNION ALL
	SELECT DATEADD(dd, 1, DATE)
	FROM #DaysInMonth
	WHERE MONTH(Date) = MONTH(@date)
	OR Date <= CONVERT(DATE, @EndDate))

SELECT  DATENAME(DAY, [Date]) AS [TDateName]
		,LEFT(DATENAME(WEEKDAY, [Date]), 3) AS [TWeekDay]
		,CONVERT(DATE, [Date]) AS [TDate]
INTO #Temp_days
FROM #DaysInMonth
WHERE (@StartDate = '' OR Date >= @StartDate)
AND (@EndDate = '' OR Date <= @EndDate)

 SELECT *
 INTO #AttRec
 FROM  (SELECT tblnew.TDateName
			   ,tblnew.employee_code
			   ,tblnew.employee_fname
			   ,tblnew.card_no
			   ,tblnew.TWeekDay
			   ,tblnew.TDate
			   ,atttbl.Card_Number
			   ,atttbl.Att_PunchRecDate
			   ,atttbl.Punch_month
			   ,tblnew.DeptName
			   ,tblnew.Designation
          FROM (SELECT *
                 FROM (SELECT employee_code
							  ,employee_fname
							  ,card_no
							  , UPPER(dp.name) AS DeptName
							  ,UPPER(desig.name) AS Designation
                       FROM  #employees ep
                       INNER JOIN #emp_common_master dp ON dp.id = ep.department_id
                       LEFT JOIN #emp_common_master desig ON desig.id = ep.designation_id
                       WHERE (card_no = @CardNo OR @CardNo = '')
                       AND (dp.id = @Depid OR @Depid = 0)) tblemp
                  CROSS APPLY #Temp_days) tblnew
           LEFT JOIN (SELECT DISTINCT T.Card_Number
									  ,T.Att_PunchRecDate
									  ,T.Punch_month
                                FROM  #Tran_DeviceAttRec_New T
                                INNER JOIN (SELECT * FROM #employees
                                            WHERE (card_no = @CardNo OR @CardNo = '')) E
								ON T.Card_Number = E.card_no
								LEFT JOIN #emp_common_master D ON D.id = E.department_id
                      ) atttbl
			ON CONVERT(VARCHAR(30), atttbl.Punch_month, 103) = CONVERT(VARCHAR(30), tblnew.TDate, 103)
	        AND atttbl.Card_Number = tblnew.card_no
		) tblfinalrecord;
                   
 DECLARE @Days VARCHAR(MAX)= '';
 SELECT @Days = @Days + ' ' + '[' + CONVERT(VARCHAR(20), [TDateName]) + '],'
 FROM   #Temp_days;
        --Remove last ,
 SET @Days = CASE @Days
               WHEN NULL THEN NULL
               ELSE (CASE LEN(@Days)
                     WHEN 0 THEN @Days
                     ELSE LEFT(@Days, LEN(@Days) - 1)
                     END)
             END;
--SELECT @Days += ',[Total]';
 DECLARE @query NVARCHAR(MAX)

 SELECT @query = 'SELECT *
                         ,(SELECT COUNT(*) FROM #AttRec as AT WHERE AT.card_no = [Card Number] AND (AT.Att_PunchRecDate IS NOT NULL OR (CAST(AT.TDate AS DATE) < CAST(GETDATE() AS DATE) AND TWeekDay <> ''Sun'' AND AT.Att_PunchRecDate IS NULL))) AS Total					 
						 ,(SELECT COUNT(*) FROM #AttRec as AT WHERE AT.card_no = [Card Number] AND (Att_PunchRecDate is not null AND TWeekDay<> ''Sun'')) AS TotalPresent
						 ,(SELECT COUNT(*) FROM #AttRec as AT WHERE AT.card_no = [Card Number] AND  (TWeekDay<>''Sun'' AND Att_PunchRecDate is null  AND CAST(TDate AS DATE) < CAST(GETDATE() AS DATE))) AS TotalAbsent					 						

				  FROM (SELECT dense_rank() OVER (ORDER BY card_no) as [Sl. No.]
						       ,card_no as [Card Number]
						       ,employee_fname as [Employee Name]
						       ,DeptName as [Department]
						       ,Designation
						       ,case when Att_PunchRecDate is not null then ''P''
						       when TWeekDay=''Sun'' then ''W''
						       WHEN CAST(TDate AS DATE) >= CAST(GETDATE() AS DATE) then '''' else ''A'' end as val
						       ,TDateName
						       ,'''' as Remark					
						FROM #AttRec) src
				  pivot
				   (
					  MAX(val)
					  for TDateName in (' + @Days + ')
				   ) piv;'
 EXEC sp_executesql @query         
 DROP TABLE #Temp_days
 DROP TABLE #AttRec

DROP Table #DaysInMonth
DROP TABLE #employees
DROP TABLE #Tran_DeviceAttRec_New
DROP TABLE #emp_common_master

OUTPUT

Sl. No. Card Number Employee Name Department Designation Remark 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 Total TotalPresent TotalAbsent
1 111 F Name1 COMMON MASTER COMMON MASTER   P P P P P W P P P P P P W P P P P     W             W 15 15 0
2 112 F Name2 COMMON MASTER COMMON MASTER   P P P P P W P P P P P P W P P P P     W             W 15 15 0
3 113 F Name3 COMMON MASTER 2 COMMON MASTER 2   P P P P P W A A A A A A W A A A A     W             W 15 5 10
4 114 F Name4 COMMON MASTER 2 COMMON MASTER 2   P P P P A W A A A A P P W P P A A     W             W 15 8 7
5 115 F Name5 COMMON MASTER 3 COMMON MASTER 3   A A A A A W A A A P P P W P P P P     W             W 15 7 8
6 116 F Name6 COMMON MASTER 3 COMMON MASTER 3   A P P P P W P P P P P P W P P P P     W             W 15 14 1
I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html?aff_id=108