T SQL Query Format to Build Dynamic Table

Question

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

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.

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...

Table:

Results:

I could do a query 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.

try to check this post, it is related with dynamic pivot table https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query. — Alvaro Parra, Oct 05 '18 at 19:54
One thing to keep in mind: the SQL language is very strict about knowing the number and types of columns in the result set _at query compile time_, **before** it begins to execute anything. It will **NOT** accept any kind of the query where the number of columns are determined by the data. Even `SELECT *` queries do this, because the number and types of columns in the table(s) used by the query are known. You should be okay here, because the user is creating known bounds by selecting dates from a calendar, but it's something to remember for future pivots. — Joel Coehoorn, Oct 05 '18 at 20:31
Check this https://www.red-gate.com/simple-talk/blogs/pivots-with-dynamic-columns-in-sql-server-2005/ — T.S., Oct 05 '18 at 20:43

Alvaro Parra · Answer 1 · 2018-10-05T21:21:52.340

0

What you want to do is called pivot

Try to use this code

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

You can check sql documentation on PIVOT and UNPIVOT on PIVOT, UNPIVOT SQL, Also if you want something more dynamic for your transdate fields you can check Dynamic Pivot

edited Oct 05 '18 at 21:21

answered Oct 05 '18 at 20:05

Alvaro Parra

796
2
8
23

I am getting "Invalid column name 'employeeid'" error at the top `SELECT employeeid, [2018-10-1], [2018-10-2], [2018-10-3]`. When I take out the employeeID and run it, the columns all look right. – Smitty-Werben-Jager-Manjenson Oct 05 '18 at 20:29
sorry i forgot a detail try again, it is a little tricky because you don't have something to count in your history table, that's why i am adding 1 as a dummy number – Alvaro Parra Oct 05 '18 at 20:33
It's so close... Printing exactly what I'm looking for but I am getting 0's across every row and in every column except for the employeeid column. I apologize for my lack of SQL skills. I've never heard of a pivot until today – Smitty-Werben-Jager-Manjenson Oct 05 '18 at 20:51
I edited again, I added a case for controlling 0's and other values, try again. – Alvaro Parra Oct 05 '18 at 21:22

T.S. · Answer 2 · 2018-10-06T01:52:53.793

you said: "all I really need is to know is if there is a single swipe for that day."

Answer - aggregate query

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

What to put in WHERE? - depends. If you want month period then do

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

If you want particular dates do

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

In the latter case, you will have to build this dynamically

But in this case you're facing a new issue - what if your person never went though the door? This person will not be on the list. Then you need to letf join this with a table that has all employees

NOW since I am not TSql super-guru but I like coding, I show you working code how it can be done programmatically. Below is a couple of snippets of code that can be combined into one

-- 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

And the result is....

Not the fastest or most code-efficient, but certainly fun. And, 1-this is totally dynamic. 2-if you look at the result, now going back to the problem I mentioned earlier, you can join this table to full list of employees and get entire set of data, including employees who has not swipe during the selected period.

After he knows if there was a swipe for the date(s) selected THEN he needs to ALSO display it as he has it shown. — Hassan Voyeau, Oct 05 '18 at 20:18

score 0 · Answer 3 · answered Oct 12 '18 at 20:29

So I've figured one way to get the results I'm looking for.

First I need to get the distinct employeeID's and store them into a temp table:

create table #TempTable
(
    EmployeeID varchar(25)
)

insert into #TempTable (EmployeeID)

select distinct employeeID from History order by employeeID;

From here, I am able to join my temp table onto my History table and select exactly what I was looking for:

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

Finally drop the temp table:

drop table #TempTable

For more dates, I just add a new column giving it the date as the header. Thanks for everyone's help on this issue.

score 0 · Answer 4 · answered Oct 12 '18 at 20:55

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

assume that dates are not always same

Hassan Voyeau · Answer 5 · 2018-10-09T06:55:57.013

-1

If the pivot columns are fixed you can use this

Edit - I guess it can be dynamic as well because you can dynamically create the SQL statement.

Microsoft Docs : FROM - Using PIVOT and UNPIVOT

Example

select * from History
PIVOT  
(
    COUNT(TranDate)
    FOR TranDate IN ([2018-10-01], [2018-10-02], [2018-10-03])  
) AS PivotTable;

edited Oct 09 '18 at 06:55

answered Oct 05 '18 at 20:02

Hassan Voyeau

3,383
4
22
24

T SQL Query Format to Build Dynamic Table

5 Answers5

Linked