Column headers from a date range and populating a matrix
-
Hello everyone…
I have an interesting problem and, while I can think of a couple of horrible solutions, I’d like the opinion and the suggestions of more experienced colleagues.
I attach a script that creates a sample test table (reduced to essentials) and inserts some test data. (Cannot attach script... will insert as SQL Code at the end of the post)...
The business case is as follow:
-- the user provides a date range (not to exceed 14 days)
-- the output displays a table where the column headers are the employee number(s) and the dates between the date range. The values are either blanks or the code "W" - where appropriate. In few words the output shows the days employees are on a Work assignment in that date range.I am not even sure I can explain correctly… but… given the test data, the task is to create output similar to the attached images.
Example: Date Range: from 9/4/17 to 9/10/17
Example: Date Range: from 9/15/17 to 9/24/17
It is quite possible I am overthink the problem... and I am sure that, as usual, someone here will have that most logical solution that escapes me...Thanks in advance for any help.
Giorgio
----- Here is the script to create the test table and sample data
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[TEST_TABLE](
[id] [int] IDENTITY(1,1) NOT NULL,
[employee_id] [int] NULL,
[assignment_date] [date] NULL,
[assignment_status] [varchar](5) NULL,
CONSTRAINT [PK_TEST_TABLE] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GO
SET IDENTITY_INSERT [dbo].[TEST_TABLE] ON
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (1, 100001, CAST(N'2017-09-01' AS Date), N'X')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (2, 100001, CAST(N'2017-09-02' AS Date), N'C')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (3, 100001, CAST(N'2017-09-03' AS Date), N'A')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (4, 100001, CAST(N'2017-09-14' AS Date), N'W')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (5, 100001, CAST(N'2017-09-15' AS Date), N'W')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (6, 100001, CAST(N'2017-09-16' AS Date), N'A')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (7, 100001, CAST(N'2017-09-18' AS Date), N'A')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (8, 100001, CAST(N'2017-09-19' AS Date), N'W')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (9, 100001, CAST(N'2017-09-24' AS Date), N'A')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (10, 100001, CAST(N'2017-09-25' AS Date), N'A')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (11, 100001, CAST(N'2017-09-26' AS Date), N'A')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (12, 100001, CAST(N'2017-09-27' AS Date), N'W')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (13, 100001, CAST(N'2017-09-28' AS Date), N'A')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (14, 100001, CAST(N'2017-09-29' AS Date), N'A')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (15, 100002, CAST(N'2017-09-04' AS Date), N'W')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (16, 100002, CAST(N'2017-09-06' AS Date), N'U')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (17, 100002, CAST(N'2017-09-08' AS Date), N'A')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (18, 100002, CAST(N'2017-09-10' AS Date), N'W')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (19, 100002, CAST(N'2017-09-12' AS Date), N'W')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (20, 100002, CAST(N'2017-09-15' AS Date), N'A')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (21, 100002, CAST(N'2017-09-16' AS Date), N'A')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (22, 100002, CAST(N'2017-09-18' AS Date), N'A')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (23, 100002, CAST(N'2017-09-21' AS Date), N'W')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (24, 100002, CAST(N'2017-09-22' AS Date), N'W')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (25, 100002, CAST(N'2017-09-23' AS Date), N'W')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (26, 100002, CAST(N'2017-09-26' AS Date), N'A')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (27, 100002, CAST(N'2017-09-28' AS Date), N'A')
GO
INSERT [dbo].[TEST_TABLE] ([id], [employee_id], [assignment_date], [assignment_status]) VALUES (28, 100002, CAST(N'2017-09-30' AS Date), N'X')
GO
SET IDENTITY_INSERT [dbo].[TEST_TABLE] OFF
GO -
How will the data be presented? It may be best to use something like a Pivot table in Excel or whatever reporting tool to do the pivoting for you. It is possible to achieve this in dynamic SQL, but obviously not as easy.
-
Here is a dynamic sql solution...
IF OBJECT_ID('tempdb..#TEST_TABLE', 'U') IS NOT NULL
DROP TABLE #TEST_TABLE;CREATE TABLE #TEST_TABLE (
id int NOT NULL,
employee_id int NULL,
assignment_date date NULL,
assignment_status varchar(5) NULL,
CONSTRAINT PK_TEST_TABLE PRIMARY KEY CLUSTERED (id)
);INSERT #TEST_TABLE (id, employee_id, assignment_date, assignment_status)
SELECT 1, 100001, CAST(N'2017-09-01' AS Date), N'X' UNION ALL
SELECT 2, 100001, CAST(N'2017-09-02' AS Date), N'C' UNION ALL
SELECT 3, 100001, CAST(N'2017-09-03' AS Date), N'A' UNION ALL
SELECT 4, 100001, CAST(N'2017-09-14' AS Date), N'W' UNION ALL
SELECT 5, 100001, CAST(N'2017-09-15' AS Date), N'W' UNION ALL
SELECT 6, 100001, CAST(N'2017-09-16' AS Date), N'A' UNION ALL
SELECT 7, 100001, CAST(N'2017-09-18' AS Date), N'A' UNION ALL
SELECT 8, 100001, CAST(N'2017-09-19' AS Date), N'W' UNION ALL
SELECT 9, 100001, CAST(N'2017-09-24' AS Date), N'A' UNION ALL
SELECT 10, 100001, CAST(N'2017-09-25' AS Date), N'A' UNION ALL
SELECT 11, 100001, CAST(N'2017-09-26' AS Date), N'A' UNION ALL
SELECT 12, 100001, CAST(N'2017-09-27' AS Date), N'W' UNION ALL
SELECT 13, 100001, CAST(N'2017-09-28' AS Date), N'A' UNION ALL
SELECT 14, 100001, CAST(N'2017-09-29' AS Date), N'A' UNION ALL
SELECT 15, 100002, CAST(N'2017-09-04' AS Date), N'W' UNION ALL
SELECT 16, 100002, CAST(N'2017-09-06' AS Date), N'U' UNION ALL
SELECT 17, 100002, CAST(N'2017-09-08' AS Date), N'A' UNION ALL
SELECT 18, 100002, CAST(N'2017-09-10' AS Date), N'W' UNION ALL
SELECT 19, 100002, CAST(N'2017-09-12' AS Date), N'W' UNION ALL
SELECT 20, 100002, CAST(N'2017-09-15' AS Date), N'A' UNION ALL
SELECT 21, 100002, CAST(N'2017-09-16' AS Date), N'A' UNION ALL
SELECT 22, 100002, CAST(N'2017-09-18' AS Date), N'A' UNION ALL
SELECT 23, 100002, CAST(N'2017-09-21' AS Date), N'W' UNION ALL
SELECT 24, 100002, CAST(N'2017-09-22' AS Date), N'W' UNION ALL
SELECT 25, 100002, CAST(N'2017-09-23' AS Date), N'W' UNION ALL
SELECT 26, 100002, CAST(N'2017-09-26' AS Date), N'A' UNION ALL
SELECT 27, 100002, CAST(N'2017-09-28' AS Date), N'A' UNION ALL
SELECT 28, 100002, CAST(N'2017-09-30' AS Date), N'X';-- SELECT * FROM #TEST_TABLE tt;
--======================================================================
DECLARE
@sql NVARCHAR(4000) = N'',
@DateCols NVARCHAR(4000) = N'',
@DeBug BIT = 0; -- 0=execute dynamic sql ... 1=PRINT dynamic sql...SELECT
@DateCols = CONCAT(@DateCols, ',
[', CONVERT(NCHAR(10), tt.assignment_date, 101), '] = MAX(CASE WHEN tt.assignment_date = ''', tt.assignment_date, ''' THEN tt.assignment_status END)')
FROM
#TEST_TABLE tt
GROUP BY
tt.assignment_date
ORDER BY
tt.assignment_date;-- print @DateCols;
SET @sql = CONCAT(N'
SELECT
tt.employee_id',
@DateCols, N'
FROM
#TEST_TABLE tt
GROUP BY
tt.employee_id;');IF @DeBug = 1
BEGIN
PRINT (@sql);
END;
ELSE
BEGIN
EXEC sys.sp_executesql @sql;
END; - Try:
DECLARE @t table(
[id] [int] IDENTITY(1,1) NOT NULL,
[employee_id] [int] NULL,
[assignment_date] [date] NULL,
[assignment_status] [varchar](5) NULL,
PRIMARY KEY CLUSTERED ( [id] )
);
INSERT @t ( [employee_id], [assignment_date], [assignment_status] )
VALUES
(100001, '20170901', 'X')
, (100001, '20170902', 'C')
, (100001, '20170903', 'A')
, (100001, '20170914', 'W')
, (100001, '20170915', 'W')
, (100001, '20170916', 'A')
, (100001, '20170918', 'A')
, (100001, '20170919', 'W')
, (100001, '20170924', 'A')
, (100001, '20170925', 'A')
, (100001, '20170926', 'A')
, (100001, '20170927', 'W')
, (100001, '20170928', 'A')
, (100001, '20170929', 'A')
, (100002, '20170904', 'W')
, (100002, '20170906', 'U')
, (100002, '20170908', 'A')
, (100002, '20170910', 'W')
, (100002, '20170912', 'W')
, (100002, '20170915', 'A')
, (100002, '20170916', 'A')
, (100002, '20170918', 'A')
, (100002, '20170921', 'W')
, (100002, '20170922', 'W')
, (100002, '20170923', 'W')
, (100002, '20170926', 'A')
, (100002, '20170928', 'A')
, (100002, '20170930', 'X');
, Max([20170901]) [20170901]
, Max([20170902]) [20170902]
, Max([20170903]) [20170903]
, Max([20170914]) [20170914]
, Max([20170915]) [20170915]
, Max([20170916]) [20170916]
, Max([20170918]) [20170918]
, Max([20170919]) [20170919]
, Max([20170924]) [20170924]
, Max([20170925]) [20170925]
, Max([20170926]) [20170926]
, Max([20170927]) [20170927]
, Max([20170928]) [20170928]
, Max([20170929]) [20170929]
, Max([20170904]) [20170904]
, Max([20170906]) [20170906]
, Max([20170908]) [20170908]
, Max([20170910]) [20170910]
, Max([20170912]) [20170912]
, Max([20170915]) [20170915]
, Max([20170916]) [20170916]
, Max([20170918]) [20170918]
, Max([20170921]) [20170921]
, Max([20170922]) [20170922]
, Max([20170923]) [20170923]
, Max([20170926]) [20170926]
, Max([20170928]) [20170928]
, Max([20170930]) [20170930]
FROM @t
PIVOT (Max(assignment_status) FOR assignment_date IN([20170901]
, [20170902]
, [20170903]
, [20170914]
, [20170915]
, [20170916]
, [20170918]
, [20170919]
, [20170924]
, [20170925]
, [20170926]
, [20170927]
, [20170928]
, [20170929]
, [20170904]
, [20170906]
, [20170908]
, [20170910]
, [20170912]
, [20170921]
, [20170922]
, [20170923]
, [20170930])) p
GROUP BY p.employee_id; -
This is trivial in SSRS.
Connect to your datasource, create your dataset, drop in a matrix.
People are on rows, Dates are on columns. Add a parameter for the start Date and EndDate. Filter the dataset based on the dates. (Might require a table of dates, in case you want to show all the dates whether people were available or not.Use an expression like this in the intersection
=IIF(Not IsNothing(MAX(Fields!Assignment_Status.Value)),"W","") -
Everyone,
Thanks for all suggestions and possible solutions. I should be able to manage and dynamically build the string I need (the business case is much more complex and convoluted... the final query will depend on a minimum of 25 (max 72) input parameters)...
Giorgio
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply