/*
I'm trying to write a query that uses a list of dates as a header
and lists, lets say, employees and if they were in training on that day.
I've made a start, but I can't get a pivot working
Note: This, by necessity, is a made up scenario similar to what I'm trying to do.
/@@@@@@@@@@@/
Hopeful Result:
/@@@@@@@@@@@/
12/1/201912/2/201912/3/201912/4/2019 12/5/2019
AliceYES YES
BettYES YES
Maya YES
/@@@@@@@@@@@/
example table
During the Winter semester (12/01/2019 - 12/31/2019) who was in training
/@@@@@@@@@@@/
EmployeeTrainingDtgTrainingType
Alice 12/1/2019Red
Alice 12/1/2019Green
Alice 12/4/2019White
Bett 12/1/2019Red
Bett 12/2/2019Green
Maya 12/3/2019Red
Maya 12/3/2019Green
/@@@@@@@@@@@/
I've started with a temp table to hold the dates
/@@@@@@@@@@@/
ex:
*/
;with ctedaterange(begDtg, endDtg)
as
(
select begDtg, dateadd(d, -1, endDtg) as endDtg /*sub a day so qry doesn't return an extra day*/
from trainingSemesters t
where semesterName = 'Winter'
union all
select dateadd(dd, 1, begDtg), endDtg
from cetdaterange where begDTG <= endDtg
)
select c.begDtg
into #tempDateList
from cteDateRange c
/*
/@@@@@@@@@@@/
scripts
/@@@@@@@@@@@/
*/
create table [dbo].[trainingEmployee] (
[_id] [int] IDENTITY(1,1) NOT NULL,
[employee] [nchar](10) NULL,
[trainingDtg] [date] NULL,
[trainingType] [nchar](10) NULL
) ON [PRIMARY]
GO
insert into trainingEmployee(employee, trainingDtg, trainingType)
values ('Alice', '2019-12-01', 'Red')
insert into trainingEmployee(employee, trainingDtg, trainingType)
values ('Alice', '2019-12-01', 'Green')
insert into trainingEmployee (employee, trainingDtg, trainingType)
values ('Alice', '2019-12-04', 'White')
insert into trainingEmployee(employee, trainingDtg, trainingType)
values ('Bett', '2019-12-01', 'Red')
insert into trainingEmployee(employee, trainingDtg, trainingType)
values ('Bett', '2019-12-02', 'Green')
insert into trainingEmployee(employee, trainingDtg, trainingType)
values ('Maya', '2019-12-03', 'Red')
insert into trainingEmployee(employee, trainingDtg, trainingType)
values ('Maya', '2019-12-03', 'Green')
create table [dbo].[trainingSemester] (
[_id] [int] IDENTITY(1,1) NOT NULL,
[begDtg] [date] NULL,
[endDtg] [date] NULL,
[semesterName] [nchar](10) NULL
) ON [PRIMARY]
GO
insert into trainingSemester(begDtg, endDtg, semesterName)
values ('2019-12-01', '2019-12-31', 'Winter')
insert into trainingSemester(begDtg, endDtg, semesterName)
values ('2019-06-01', '2019-06-30', 'Summer')
Code-Blooded
December 10, 2019 at 8:54 pm
Could you please post directly usable data, i.e. CREATE TABLE and INSERT statements for the example table data.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 10, 2019 at 9:46 pm
Yes...I've added them to the bottom of the OP.
Thanks, D
Code-Blooded
Let me know if this code looks good, and we can convert it into more dynamic code. I didn't want to spend time doing that until I had the core code working the way you wanted it to first.
DECLARE @end_date date
DECLARE @semester_name nvarchar(10)
DECLARE @sql nvarchar(max)
DECLARE @start_date date
SET @semester_name = 'Winter'
SELECT TOP (1) @start_date = begDtg, @end_date = endDtg
FROM dbo.trainingSemester
WHERE semesterName = @semester_name
ORDER BY begDtg DESC
SELECT employee,
ISNULL([2019-12-01], '') AS [2019-12-01],
ISNULL([2019-12-02], '') AS [2019-12-02],
ISNULL([2019-12-03], '') AS [2019-12-03],
ISNULL([2019-12-04], '') AS [2019-12-04],
ISNULL([2019-12-05], '') AS [2019-12-05]
FROM (
SELECT employee, trainingDtg, trainingType
FROM dbo.trainingEmployee
WHERE trainingDtg BETWEEN @start_date AND @end_date
) AS tE
PIVOT ( MAX(tE.trainingType) FOR trainingDtg IN ([2019-12-01], [2019-12-02],
[2019-12-03], [2019-12-04], [2019-12-05]) ) AS pvt
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 13, 2019 at 3:22 pm
This works great for me.
How would I go about grabbing the dates dynamically?
Thanks!, D
Code-Blooded
December 13, 2019 at 6:52 pm
This works great for me.
How would I go about grabbing the dates dynamically?
Thanks!, D
With the understanding that I have no love for the Pivot operator, please see the following...
https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2019 at 7:46 pm
Here it is.
DECLARE @end_date date
DECLARE @semester_name nvarchar(10)
DECLARE @sql nvarchar(max)
DECLARE @sql2 nvarchar(max)
DECLARE @start_date date
SET @semester_name = 'Winter'
SELECT TOP (1) @start_date = begDtg, @end_date = endDtg
FROM dbo.trainingSemester
WHERE semesterName = @semester_name
ORDER BY begDtg DESC
;WITH
cte_tally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
)
SELECT @sql = STUFF((
SELECT ',ISNULL([' + CONVERT(varchar(10), DATEADD(DAY, t.number, @start_date), 120) +
'], '''') AS [' + CONVERT(varchar(10), DATEADD(DAY, t.number, @start_date), 120) + ']'
FROM cte_tally1000 t
WHERE t.number BETWEEN 0 AND DATEDIFF(DAY, @start_date, @end_date)
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
, 1, 1, '')
--PRINT @sql
;WITH
cte_tally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
)
SELECT @sql2 = STUFF((
SELECT ',[' + CONVERT(varchar(10), DATEADD(DAY, t.number, @start_date), 120) + ']'
FROM cte_tally1000 t
WHERE t.number BETWEEN 0 AND DATEDIFF(DAY, @start_date, @end_date)
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
, 1, 1, '')
--PRINT @sql2
SET @sql = '
SELECT employee, ' + @sql + '
FROM (
SELECT employee, trainingDtg, trainingType
FROM dbo.trainingEmployee
WHERE trainingDtg BETWEEN @start_date AND @end_date
) AS tE
PIVOT ( MAX(tE.trainingType) FOR trainingDtg IN (' + @sql2 + ') ) AS pvt'
PRINT @sql
EXEC sp_executesql @sql, N'@start_date date, @end_date date', @start_date, @end_date
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply