从表中获取当前月份的当天数 [英] To fetch the number of days present for a current month from a table

问题描述

我需要从表中获取特定员工的天数。因为我也应该采用当前日期并找到该月的开始日期，并且当天它应该显示当前计数。

I need to fetch the number of days a particular employee is present from a table. Since i should also take the current date and find the starting day of the month and for that days it should display the present count.

推荐答案

您好，

这里是一个样本。

Hello ,
here is a sample .

--First create one temporary table .
CREATE TABLE #TMP  
(  
   NAME VARCHAR(50),  
   DATE DATETIME ,  
   PRESENT_STATUS VARCHAR(10)  
)  

--insert data 
INSERT INTO #TMP VALUES ('A','2016-07-01','PRESENT')  
INSERT INTO #TMP VALUES ('M','2016-07-01','PRESENT')  
INSERT INTO #TMP VALUES ('A','2016-07-02','ABSENT')  
INSERT INTO #TMP VALUES ('M','2016-07-02','ABSENT')  
INSERT INTO #TMP VALUES ('A','2016-07-03','PRESENT')  
INSERT INTO #TMP VALUES ('M','2016-07-03','PRESENT')  
INSERT INTO #TMP VALUES ('A','2016-07-04','ABSENT')  
INSERT INTO #TMP VALUES ('M','2016-07-04','PRESENT')  


--NOW DECLARE ONE DATETIME FOR START DATE OF THE MONTH
DECLARE @STARTDATE DATETIME
SET @STARTDATE=(SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))



--NOW SIMPLE SELECT THE COUNT OF PRESENT STATUS
SELECT COUNT(1) AS PRESENT_COUNT , NAME FROM #TMP WHERE PRESENT_STATUS='PRESENT' 
AND DATE BETWEEN @STARTDATE AND @ENDATE --HERE @ENDATE WILL BE THE LAST DATE
GROUP BY NAME

谢谢

Thanks

这篇关于从表中获取当前月份的当天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！