SQL - creating a report about last door usage

Question

I am trying to make a report which shows last time per day in which certain door in our building was open.

What i got so far is a function that return date (varchar(10) a time (varchar(5)) when i give it a @datestart = date from i want to start a report (end date is set to GETDATE()) and @id_door (we store date and times from locks under certain id for every door). Looks like this: LAST_DOOR_TIME (@DATESTART, @ID_DOOR) Output:

+-------------+--------+
|   DATE      | TIME   | 
+-------------+--------+
|  28.06.2017 | 22:30  | 
+-------------+--------+
|  29.06.2017 | 21:30  | 
+-------------+--------+

My desire is to select * from @output table. Which i am creating like this:

DECLARE @output TABLE (ID int IDENTITY(1,1) not null, Date VARCHAR(10)
 null, Door1 VARCHAR(5) NULL, Door2 VARCHAR(5) NULL, .... PRIMARY KEY (ID))

DECLARE @DATESTART DATE = 'some date'
DECLARE @DATEEND DATE = GETDATE()

WHILE ( @DATESTART < @DATEEND )
BEGIN
    INSERT INTO @output (Date) VALUES( @DATESTART )
    SELECT @DATESTART = DATEADD(DAY, 1, @DATESTART )
END

+----+-------------+-------+--------+
| id |    Date     | Door1 | Door2  |
+----+-------------+-------+--------+
|  1 |  28.06.2017 |       |        |
+----+-------------+-------+--------+ 
|  2 |  29.06.2017 |       |        |
+----+-------------+-------+--------+ 
|  3 |  30.06.2017 |       |        |
+----+-------------+-------+--------+

Also i am usig a loop that select all the doors with theirs id and use it in WHILE condition and in every pass through it change @ID_DOOR in the function.

What i really strugle with is how to insert a output from a function into @output table so i match a right column and date.

Thanks for any suggestion.

Ok, my source tables are: Amb_door - table that holds times when doors open.

+-------------+----------------------+
|   ID_INT    | DATE_NAV             | 
+-------------+----------------------+
|  1          | 2012-06-28 11:00:00  | 
+-------------+----------------------+
|  2          | 2012-06-28 11:10:00  | 
+-------------+----------------------+

next i got internal_doc

+-------------+--------+-------------+---------+
|   ID_INT    | ID_EMP | ID_BUILDING | ID_DOOR |
+-------------+--------+-------------+---------+
|  1          | 1      | 3           |  10     |
+-------------+--------+-------------+---------+
|  2          | 1      | 3           |  10     |
+-------------+--------+-------------+---------+

In order to get a relevant information out of this i have to join these two on int_id to get a right door and right time together. I put output of this join into @Temp table and then use select with inner join to get a max time per day out of it.

Select DATE=convert(varchar(10),a.DATE_NAV,104), TIME=convert(varchar(5),R.MAXTIME,108) 
from
(select max(DATE_NAV) as maxtime from @Temp group by day([DATE_NAV])) R
inner join  @Temp a on R.MAXTIME=A.DATE_NAV
order by R.MAXTIME asc

Thats all inside of that function LAST_DOOR_TIME.

Please post your source table, I think you've gone down a rabbit hole that you didn't need to. It looks like you're treating this like a procedural programming problem rather than a relational database problem. — JeffUK, Jun 30 '17 at 11:59

JeffUK · Accepted Answer · 2017-07-03T11:59:15.980

With these source tables:

declare @AMB_DOOR table(id_INT int,date_nav datetime)

insert into @AMB_DOOR values (1, '2016-01-01 16:50')
insert into @AMB_DOOR values (2, '2016-01-01 17:20')
insert into @AMB_DOOR values (3, '2016-01-01 16:53')
insert into @AMB_DOOR values (4, '2016-01-01 17:21')

declare @INTERNAL_DOC table(id_INT int,id_EMP int,id_BUILDING int,id_DOOR int)

insert into @INTERNAL_DOC values (1,1,2,10)
insert into @INTERNAL_DOC values (2,1,3,10)
insert into @INTERNAL_DOC values (3,1,2,20)
insert into @INTERNAL_DOC values (4,1,3,20)

You can get what you need as follows

 select 
      id_door,
      convert(date, date_nav) as [Date], 
      max(convert(varchar(8), convert(time, date_nav))) as Maxtime

  from
      @AMB_DOOR t1 left join @INTERNAL_DOC t2 on t1.id_int=t2.id_INT
  group by 
      id_door, 
      convert(date, date_nav)

Gives

ID_DOOR DATE        MAXTIME
10      2016-01-01  17:20:00
20      2016-01-01  17:21:00

If you then want to pivot it, I'd recommend using your reporting tool to do that if possible, as dynamic pivots in pure TSQL are quite complicated. https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — JeffUK, Jul 03 '17 at 12:15

SQL - creating a report about last door usage

1 Answers1