Count no of employees date wise from single column in SQL Server

Bhavesh23
 
on Jun 30, 2022 11:32 PM
247 Views

I have one table any in single column storing date. Now i want to count day wise employee entry from that column.

Suppose column name is Inward Date, now in inward date column store records date from 01/06/2022 to 30/06/2022 days.

Now i want to count between dates how many employee has come from date 01/06/2022 to 30/06/2022 date wise like

date  1       2      3    4    5   .....  30

Count 10    15   20   25   30       102   

Issue is the date is stored in single column and having no idea how to make this.

How to do this?

Please help me.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 01, 2022 06:52 AM

Hi Bhavesh23,

Check the below query.

SQL

CREATE TABLE #Employees
(
	InwardDate DATETIME,
	EmployeeName VARCHAR(50)
)

INSERT INTO #Employees VALUES('2022-07-01','Ram Das')
INSERT INTO #Employees VALUES('2022-07-02','Arjun Das')
INSERT INTO #Employees VALUES('2022-07-01','Ram Das')
INSERT INTO #Employees VALUES('2022-07-03','Dharm Das')
INSERT INTO #Employees VALUES('2022-07-02','Ram Das')
INSERT INTO #Employees VALUES('2022-07-03','kali Das')
INSERT INTO #Employees VALUES('2022-07-05','Lila Das')
INSERT INTO #Employees VALUES('2022-07-01','Bali Das')
INSERT INTO #Employees VALUES('2022-07-05','Ram Das')
INSERT INTO #Employees VALUES('2022-07-01','Babu Das')
INSERT INTO #Employees VALUES('2022-07-06','Ram Das')
INSERT INTO #Employees VALUES('2022-07-06','Karim Das')
INSERT INTO #Employees VALUES('2022-07-06','Karim Das')

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @colsFormated AS NVARCHAR(MAX)
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(DAY(InwardDate))
            FROM #Employees
            FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,1,'')

set @query =';WITH CTE AS(SELECT EmployeeName,DAY(InwardDate) InwardDate FROM #Employees)
			SELECT * INTO #Temp FROM CTE
            SELECT ''Count'' AS ''Date'',' + @cols +' FROM #Temp         
            PIVOT
            (
                COUNT(EmployeeName)
                FOR InwardDate IN (' + @cols +')
            ) p
			DROP TABLE #Temp'

EXECUTE sp_executesql @query
DROP TABLE #Employees

Output

Date 1 2 3 5 6
Count 4 2 2 2 3