checkView.sql

USE [xxxx]
DECLARE @DepartmentId uniqueidentifier;
DECLARE @BannerId uniqueidentifier;
DECLARE @FileId uniqueidentifier;
DECLARE @StartTime datetime2(7);
DECLARE @EndTime datetime2(7);

SET @DepartmentId = '009feddb-6558-46f9-9d4b-fd9e120df2d8';
SET @BannerId = '0aa6eeb6-2fa9-4398-9459-f68db3dd7f60';
SET @StartTime = '2018/7/25';
SET @EndTime = '2018/7/26';

--印出組織資訊
Select  [Id] as '組織唯一識別號', [UnitCode] as '組織編號', [Name] as '名稱'
	From [Department]
	Where [Id] = @DepartmentId 

-- 找出對應的檔案
SET @FileId = 
	(Select FIRST_VALUE([Value]) OVER (ORDER BY [StartTime] ASC) as 'Banner對應File唯一識別號'
	From [Banner]
	Where [Id]=@BannerId)

-- 印出檔案資訊
Select [File].[Id] as 'File唯一識別號', [Category].[Name] as '分類名稱', [File].[Name] as '名稱'
	From [File] left join [Category] on [File].[CategoryId] = [Category].[Id]
	Where [File].[Id] = @FileId

Select *  From [BannerLog]
	Where
	  [BannerId] = @BannerId And	  
	  [DepartmentId] = @DepartmentId

Select distinct [Department].[Id], [Department].[Name], [FileLog].[UserId]
	From [FileLog] left join [Department] on [FileLog].[DepartmentId] = [Department].[Id]
	Where 
		[FileId] = @FileId And
		[Time] >= @StartTime And
		[Time] < @EndTime And
		[UserId] in 
			(
			 Select [Id]
			 From [User] 
			 Where [DepartmentId] = @DepartmentId
			)
GO