Best way to generate a report?

Transact-SQL
1

Morning all,

I have been trying to get my head around how to achieve this. I have a 2 tables, 1 that stores documents with date added the second stores when users open the documents.

CREATE TABLE [dbo].[Policys](
	[PP_ID] [int] NOT NULL,
	--[PP_Document] [image] NOT NULL, REMOVED THIS LINE FOR TESTING, IT STORES THE PHYSICAL DOCUMENT.
	[PP_DocName] [nvarchar](100) NOT NULL,
	[PP_DateAdded] [datetime2](3) NOT NULL
	);
	
INSERT INTO Policys (PP_ID, PP_DocName, PP_DateAdded)
	VALUES
	('1000', 'Test Doc 1', '2023-02-01'),
	('1001', 'Test Doc 2', '2023-02-03'),
	('1002', 'Test Doc 3', '2023-02-05');
	
	
CREATE TABLE [dbo].[Users_PolicyRead](
	[UPPR_ID] [int] IDENTITY(1000,1) NOT NULL,
	[UPPR_Username] [nvarchar](20) NOT NULL,
	[UPPR_PPID] [int] NOT NULL,
	[UPPR_DateRead] [datetime2](3) NOT NULL
	);
	
INSERT INTO Users_PolicyRead (UPPR_Username, UPPR_PPID, UPPR_DateRead)
	VALUES
	('John', '1000', '2023-02-15'),
	('John', '1001', '2023-02-15'),
	('Fran', '1000', '2023-02-20'),
	('Bob', '1002', '2023-02-21');

So in the expample above John read Test Doc 1 & Test Doc 2 on 15.02.23, Fran read Test Doc 1 on 20.02.23 & Bob read Test Doc 3 on 21.02.23.

What i'm trying to output is basically a report of who read, or hasn't read the documents. Something like this perhaps:

Document          John                    Fran                    Bob
-----------------------------------------------------------------------------------
Test Doc 1          15/02/23             20/02/23              Not Read
Test Doc 2          15/02/23             Not Read             Not Read
Test Doc 3          Not Read            Not Read             21/02/23

Or if you have any better ideas how to duisplay the data....??

Any help / pointer greatly apreciated....

Many thanks

Dave

2

hi

hope this helps

i have yet to work out how to display 'Not Read'

i am using Dynamic Pivot

create data script

CREATE TABLE [dbo].[Policys](
[PP_ID] [int] NOT NULL,
--[PP_Document] [image] NOT NULL, REMOVED THIS LINE FOR TESTING, IT STORES THE PHYSICAL DOCUMENT.
[PP_DocName] nvarchar NOT NULL,
[PP_DateAdded] datetime2 NOT NULL
);

INSERT INTO Policys (PP_ID, PP_DocName, PP_DateAdded)
VALUES
('1000', 'Test Doc 1', '2023-02-01'),
('1001', 'Test Doc 2', '2023-02-03'),
('1002', 'Test Doc 3', '2023-02-05');

CREATE TABLE [dbo].[Users_PolicyRead](
[UPPR_ID] [int] IDENTITY(1000,1) NOT NULL,
[UPPR_Username] nvarchar NOT NULL,
[UPPR_PPID] [int] NOT NULL,
[UPPR_DateRead] datetime2 NOT NULL
);

INSERT INTO Users_PolicyRead (UPPR_Username, UPPR_PPID, UPPR_DateRead)
VALUES
('John', '1000', '2023-02-15'),
('John', '1001', '2023-02-15'),
('Fran', '1000', '2023-02-20'),
('Bob', '1002', '2023-02-21');

DECLARE @cols   AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

SET @cols = STUFF((select distinct ',' + QUOTENAME(c.UPPR_Username) from Users_PolicyRead c FOR XML PATH(''), 
              TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')
    
SET @query = 'select pp_docname, ' + @cols + ' from 
                  (
                select  pp_docname ,UPPR_Username , CONVERT(varchar,UPPR_DateRead,103)   UPPR_DateRead from Users_PolicyRead a join policys b on a.UPPR_PPID = b.PP_ID
                   ) x
                 pivot 
                    (
                 max(UPPR_DateRead) for UPPR_Username in (' + @cols + ')
                    ) p '

EXECUTE(@query)

image
image878×683 21.4 KB

3

Ooh i like it, thank you, very eligant :smiley:

I was hoping i could be cheeky and add:

ISNULL(CONVERT(varchar,UPPR_DateRead,103), 'Not Read') As UPPR_DateRead

But it doesn't seem to like it...I'm guessing you probably already knew that :smiley:

4

hi

i had to work on the 'Not Read'

here it is

DECLARE @cols   AS NVARCHAR(MAX),
        @cols1  AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

SET @cols = STUFF((select distinct ',' + QUOTENAME(c.UPPR_Username) from Users_PolicyRead c FOR XML PATH(''), 
              TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

SET @cols1 = STUFF((select distinct ',' + QUOTENAME(c.UPPR_Username) + ' AS '+ c.UPPR_Username+'''' from Users_PolicyRead c FOR XML PATH(''), 
              TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

SET @cols1 =  replace(   replace(@cols1,'[','isnull([')  ,'AS ',',''Not Read'') AS ''')


SET @query = 'select pp_docname, ' + @cols1 + ' from 
                  (
                select  pp_docname ,UPPR_Username , CONVERT(varchar,UPPR_DateRead,103)   UPPR_DateRead from Users_PolicyRead a join policys b on a.UPPR_PPID = b.PP_ID
                   ) x
                 pivot 
                    (
                    max(UPPR_DateRead) for UPPR_Username in (' + @cols + ')
                    ) p '
exec   (@query)

image

5

Best way to create a report: I would suggest SSRS: the reporting services that are included in your SQL server license. In a paginated report with a tablix you can show the results from a simple select into a sophisticated report without putting extra strain on the SQL resources.

1 Like
6

So my question would be, what happens if you're trying to track, say, 50 people and their "reads"? What do you want to do then?

The real question is, what are you trying to find out?