Column headers from a date range and populating a matrix

• Giorgio Vidali

  Ten Centuries

  Points: 1116

  More actions

  September 1, 2017 at 7:29 am

  #338910

  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
GO

  CREATE 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

       

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  September 1, 2017 at 10:22 am

  #1957867

  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.

• Jason A. Long

  SSC-Insane

  Points: 23780

  More actions

  September 1, 2017 at 11:47 am

  #1957879

  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;

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  September 5, 2017 at 4:08 pm

  #1958233

  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');

  SELECT employee_id
  , 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;

  

• pietlinden

  SSC Guru

  Points: 63428

  More actions

  September 5, 2017 at 5:51 pm

  #1958239

  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","")

• Giorgio Vidali

  Ten Centuries

  Points: 1116

  More actions

  September 6, 2017 at 6:46 am

  #1958331

  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