生成动态表的t SQL查询格式

Question

我需要帮助创建一个查询，以生成我正在寻找的结果。

我正在创建一个基于现有数据库表来跟踪员工出勤情况的应用程序。用户从日历(例如10/1/18、10/2/18和10/3/18)中选择日期或日期组。它们单击submit，我需要生成一个表，如果每个员工当天都在那里，则在日期列中显示每个员工的复选标记。

该表名为History，有两个主要列: EmployeeID；和TransactionDate。每当员工走过一扇门(包括入口)，就会创建一个历史事务(NFC徽章)，该事务会添加一个带有员工ID和日期/时间戳的新行。每个员工可能每天都有几次刷卡，但我真正需要知道的是，那天是否有一次刷卡。我正在张贴表的外观和查询结果的图片，我的表需要看起来像.

表：

​

结果：

​

我可以进行如下查询：

select employeeid, TranDate from History 
where (CAST(trandate as DATE) = '2018-10-1' or CAST(trandate as DATE) = '2018-10-2' or CAST(trandate as DATE) = '2018-10-3' ) 
order by employeeid, TranDate

并且以编程的方式进行分类，但是我觉得有一种更有效的方法来查询我正在寻找的结果。

任何帮助都是非常感谢的。如果我需要更好的解释，请告诉我。

Answer 1

你想做的事叫做枢轴

尝试使用以下代码

SELECT employeeid
      ,case when [2018-10-1] > 0 then '1' else '' end as [2018-10-1]
      ,case when [2018-10-2] > 0 then '1' else '' end as [2018-10-2]
      ,case when [2018-10-3] > 0 then '1' else '' end as [2018-10-3]
FROM(
SELECT employeeid
     , TranDate
     , 1 as num 
FROM History 
WHERE (CAST(trandate as DATE) = '2018-10-1' 
       or CAST(trandate as DATE) = '2018-10-2' 
       or CAST(trandate as DATE) = '2018-10-3' ) 
)
PIVOT(
COUNT(num)
FOR TranDate IN ([2018-10-1],[2018-10-2],[2018-10-3])
) as pvt

您可以在枢轴，UNPIVOT SQL上检查枢轴上的sql文档和UNPIVOT上的sql文档，如果您想要更动态地处理过期字段，则可以检查动态轴。

Answer 2

你说：“我真正需要知道的是，那天是否有一次滑动。”

答案-聚合查询

select employeeid, TranDate, count(TranDate) as count
from History 
where -- look below
Group by employeeid, TranDate

在WHERE里放什么？-看情况。如果你想要月期，那就去做

TranDate between '2018-10-1' and '2018-10-31 23:59:59.999'

如果你想要特定的日期

CAST(trandate as DATE) in ('2018-10-1', '2018-10-11', '2018-10-11')

在后一种情况下，您必须动态地构建这个

但是在这种情况下，你面临着一个新的问题--，如果你的人从来不出门怎么办？，这个人不会出现在名单上。然后，您需要使用一个包含所有员工的表对此进行letf join。

NOW，因为我不是TSql超级大师，但我喜欢编码，我向您展示工作代码是如何编程实现的。下面是几段代码片段，可以组合成一个

-- SETUP
create table recs (id int, dt date );
insert into recs values(1, '2018-10-1');
insert into recs values(1, '2018-10-1');
insert into recs values(2, '2018-10-2');
insert into recs values(2, '2018-10-2');
insert into recs values(2, '2018-10-3');
insert into recs values(3, '2018-10-3');
insert into recs values(3, '2018-10-3');
insert into recs values(3, '2018-10-4');
insert into recs values(3, '2018-10-1'); 


-- Prepare Date Grid table
DECLARE crs_dt CURSOR FOR SELECT dt FROM recs group by dt order by dt;
declare @createTbl varchar(1200) = 'create table tbl (id int, ';
declare @fetched varchar(20);

OPEN crs_dt;  
FETCH NEXT FROM crs_dt into @fetched;  

WHILE @@FETCH_STATUS = 0  
BEGIN  
   set @createTbl = @createTbl + '['+@fetched+'] int'
   FETCH NEXT FROM crs_dt into @fetched;
   if @@FETCH_STATUS = 0
   begin set @createTbl = @createTbl + ','; end
END  

set @createTbl = @createTbl + ')';

CLOSE crs_dt;  
DEALLOCATE crs_dt;

select @createTbl; -- just a test
execute (@createTbl)
SELECT * FROM tbl; -- just a test


-- LOAD date grid table with data
DECLARE crs_rec CURSOR FOR 
SELECT id, dt, FORMAT ( dt, 'yyyy-MM-dd' ) colName, 
(case count(dt)  when 0 then 0 else 1 end) cnt
FROM recs group by id, dt order by dt;

declare @createInsert varchar(1200);
declare @id int, @dt date, @colName varchar(20),@yesNo int;

OPEN crs_rec;  
FETCH NEXT FROM crs_rec into @id, @dt, @colName, @yesNo;  

WHILE @@FETCH_STATUS = 0  
BEGIN
   if exists(select 1 from tbl where id = @id)
       set @createInsert = 'update tbl set ['+@colName+']='+cast(@yesNo as varchar(1))+ ' where id='+ cast(@id as varchar(1000));
   else  
       set @createInsert = 
           'insert into tbl (id,['+@colName+']) values ('+cast(@id as varchar(1000))+','+cast(@yesNo as varchar(1))+')';
   select @createInsert; -- just a test
   execute (@createInsert);

   FETCH NEXT FROM crs_rec into  @id, @dt, @colName, @yesNo;
END  
CLOSE crs_rec;  
DEALLOCATE crs_rec;

commit;

-- Lets Load data 
SELECT * FROM tbl

结果是..。

​

​

不是最快的，也不是最高效的，但肯定很有趣。这完全是动态的。2-如果您查看结果，现在回到我前面提到的问题，您可以将此表加入到完整的员工列表中，并获得完整的数据集，包括在选定期间没有滑动的员工。

Answer 3

所以我想出了一种方法来获得我想要的结果。

首先，我需要获得不同的员工I，并将它们存储在临时表中：

create table #TempTable
(
    EmployeeID varchar(25)
)

insert into #TempTable (EmployeeID)

select distinct employeeID from History order by employeeID;

在这里，我可以将临时表加入到我的历史表中，并准确地选择我想要的内容：

SELECT distinct M.EmployeeID AS Employee, 
(SELECT CASE WHEN EXISTS
(SELECT 1 FROM HISTORY WHERE TAGID = M.EmployeeID AND (CAST(TRANDATE AS DATE) = '10/12/2018')) 
THEN CAST (1 AS BIT) ELSE CAST (0 AS BIT) END) AS '10/12/2018'           
FROM #TempTable AS M left JOIN HISTORY AS H ON M.EmployeeID = H.TAGID where m.EmployeeID = '000000000000000000000001' order BY Employee

最后，删除temp表：

drop table #TempTable

对于更多的日期，我只是添加了一个新的列，将日期作为标题。谢谢大家在这个问题上的帮助。