T SQL查询格式建立动态表 [英] T SQL Query Format to Build Dynamic Table

问题描述

我需要帮助创建查询以生成我正在寻找的结果.

I need help creating a query to generate the results I am looking for.

我正在创建一个应用程序，用于根据现有数据库表跟踪员工出勤情况.用户从日历中选择一个或一组日期(比如 10/1/18、10/2/18 和 10/3/18代码>).他们点击提交，我需要生成一个表格，如果他们当天在那里，我需要生成一个表格，显示每个员工在日期列中带有复选标记.

I'm creating an application to track employee attendance based off an existing database table. The user selects a date or group of dates from a calendar (say 10/1/18, 10/2/18 and 10/3/18). They click submit and I need to generate a table that displays every employee with a check mark in the date column if they were there that day.

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

The table is called History and has 2 main columns: EmployeeID; and TransactionDate. Every time an employee walks through a door (including the entrance) a History transaction is created (NFC badges) which adds a new row with the Employee's ID and a date/time stamp. Each employee likely has several swipes per day but all I really need is to know is if there is a single swipe for that day. I'm posting pictures of what the table looks like and what the query results and my table needs to look like...

表格:

结果:

我可以做如下查询:

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

并以编程方式对其进行排序，但我觉得有一种更有效的方式来查询我正在寻找的结果.

and sort it out programmatically but I feel there is a far more efficient way to query for the results I'm looking for.

非常感谢任何帮助.如果我需要给出更好的解释，请告诉我.

Any help is greatly appreciated. Let me know if I need to give a better explanation.

推荐答案

with using @T.S.创建表:

with using @T.S. created table :

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'); 

declare @dts as varchar(max) =''
declare @dtsSelect as varchar(max) =''
select  @dts =  @dts + ',[' +dt +']',
             @dtsSelect =  @dtsSelect + ',Case WHEN [' +dt+']>0 then 1 else 0 end ['+dt+']'
From (select distinct cast(dt as varchar(100)) dt from recs) recs
//we use some variables to create columns should apear in pivot list and 
// also to check if we have a value for column then put 1 instead
set @dts = Stuff(@dts,1,1,'')
set @dtsSelect = Stuff(@dtsSelect,1,1,'')
//delete the first comma

declare @sql nvarchar(max) = '
select id,'+@dtsSelect+' from recs 
pivot(Count(dt) For dt in('+@dts+'))PV

'
exec sp_executesql @stme = @sql

假设日期并不总是相同

这篇关于T SQL查询格式建立动态表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！