August 29, 2018 at 10:08 am
I am in the middle of writing an SSRS when (surprise,surprise), the customer wants this funky addition to the end of every table row. They want a square for every month of the current year and going out to 2+ years. (As a side note the example they presented me was an Excel file which they had been statically maintaining every month for the last couple of years. The PM's had finally gotten tired of updating Excel and figured they would pass the task on to me.) My thought was to CROSS APPLY a pivoted dataset onto my current base dataset. My problem is then updating the final dataset to correctly show when a project is in pre-planning, or is being worked, or should be completed. Below is my entire query, pivot work and all. I just cannot seem to figure out how to correctly update the calendar columns. PLEASE HELP!
--Declare needed variables AND remove any temp tables that will be used
DECLARE @test-2 date = '2018-08-29';
IF OBJECT_ID('tempdb..#base') IS NOT NULL BEGIN DROP TABLE #base END;
IF OBJECT_ID('tempdb..#result1') IS NOT NULL BEGIN DROP TABLE #result1 END;
IF OBJECT_ID('tempdb..#result2') IS NOT NULL BEGIN DROP TABLE #result2 END;
IF OBJECT_ID('tempdb..##SEPresults2') IS NOT NULL BEGIN DROP TABLE ##SEPresults2 END;
CREATE TABLE #result1 ([N] tinyint, MonthId tinyint, MonthVar VARCHAR(15));
CREATE TABLE #base (ProjectId int, ProjectDesc VARCHAR(25), ProjectPrePlanStart date, ProjectWorkStart date, ProjectComplete date)
DECLARE @startMonth int, @startYear int, @startDate date, @endDate date, @monthCount int, @remMonths int, @years int
DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX)
--insert the "base" data
INSERT INTO #base(ProjectId, ProjectDesc, ProjectPrePlanStart, ProjectWorkStart, ProjectComplete)
VALUES
(1,'foo','2018-05-06','2018-07-02','2018-09-15'),
(2,'bar','2018-06-19','2018-10-11','2019-02-14'),
(3,'far','2019-01-13','2019-07-20','2020-01-23'),
(4,'fiz','2020-01-27','2020-08-30','2021-04-02')
DECLARE @tblMonth TABLE (MonthId tinyint, MonthVar VARCHAR(3))
--because this will be pivoted, each month entry needs to be unique
insert into @tblMonth(MonthId,MonthVar)
VALUES (1,'1J'),(2,'2F'),(3,'3M'),(4,'4A'),(5,'5M'),(6,'6J'),(7,'7J'),(8,'8A'),(9,'9S'),(10,'10O'),(11,'11N'),(12,'12D')
--if the month is roughly 1/2 over roll over to next month. This is expected to run at the end of the current month or at the beginning.
select @startMonth = CASE WHEN DATEDIFF(DAY,@test,EOMONTH(@test)) < 15 THEN MONTH(DATEADD(MONTH,1,@test)) ELSE MONTH(@test) END
select @startYear = CASE WHEN @startMonth > 1 THEN YEAR(@test) ELSE YEAR(DATEADD(YEAR,1,@test)) END
select @startDate = CONVERT(DATE,CONVERT(CHAR(4),@startYear) + '-' + CONVERT(VARCHAR(2),@startMonth) + '-01')
select @endDate = CONVERT(CHAR(4),YEAR(DATEADD(YEAR,2,@startDate))) + '-12-31'
select @monthCount = DATEDIFF(MONTH,@startDate, @endDate)
select @remMonths = @monthCount % 12
select @years = @monthCount / 12
--check that the variables are behaving as required
--select @startMonth as startMonth, @startYear as startYear, @startDate as startDate, @endDate as endDate
-- , @monthCount as monthCount, @remMonths as remainingMonthsinYear, @years as Years
/* totally stolen from Mr. Jeff Moden: http://www.sqlservercentral.com/articles/Tally+Table/72993/ */
; WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b)
, cteTally(N) AS (
SELECT 0 UNION ALL
SELECT TOP (@monthCount) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2
)
insert into #result1(N,MonthId,MonthVar)
select * from cteTally t cross apply @tblMonth m
where (t.N = 0 and m.MonthId >= @startMonth)
UNION
select * from cteTally t cross apply @tblMonth m
where t.N <> 0 and t.N <= @years
--Update the MonthVar to show the year
UPDATE #result1
SET MonthVar = MonthVar + '-' + CONVERT(VARCHAR,@startYear)
where N = 0
UPDATE #result1
SET MonthVar = MonthVar + '-' + CONVERT(VARCHAR,@startYear+1)
where N = 1
UPDATE #result1
SET MonthVar = MonthVar + '-' + CONVERT(VARCHAR,@startYear+2)
where N = 2
select @cols = STUFF((SELECT ',' + QUOTENAME(MonthVar)
from #result1
order by N,MonthId
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
--using global temp table to be able to join the result to another table
set @query = N'
SELECT ' + @cols + N' into ##SEPresults2 from
(
select 0 as MonthId, MonthVar
from #result1
) x
pivot
(
max(MonthId)
for MonthVar in (' + @cols + N')
) p '
exec sp_executesql @query;
select * into #result2
from ##SEPresults2
DROP TABLE ##SEPresults2
select *
from #base
cross apply #result2
--Correct Response
IF OBJECT_ID('tempdb..#correctResponse') IS NOT NULL BEGIN DROP TABLE #correctResponse END;
CREATE TABLE #correctResponse
(ProjectId int, ProjectDesc varchar(25), ProjectPrePlanStart date, ProjectWorkStart date,
ProjectComplete date, [9S-2018] tinyint, [10O-2018] tinyint, [11N-2018] tinyint, [12D-2018] tinyint,
[1J-2019] tinyint, [2F-2019] tinyint, [3M-2019] tinyint, [4A-2019] tinyint, [5M-2019] tinyint,
[6J-2019] tinyint, [7J-2019] tinyint, [8A-2019] tinyint, [9S-2019] tinyint, [10O-2019] tinyint,
[11N-2019] tinyint,[12D-2019] tinyint,[1J-2020] tinyint, [2F-2020] tinyint, [3M-2020] tinyint,
[4A-2020] tinyint, [5M-2020] tinyint, [6J-2020] tinyint, [7J-2020] tinyint, [8A-2020] tinyint,
[9S-2020] tinyint, [10O-2020] tinyint,[11N-2020] tinyint,[12D-2020] tinyint)
insert into #correctResponse
SELECT 1,'foo','2018-05-06','2018-07-02','2018-09-15',2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 UNION ALL
SELECT 2,'bar','2018-06-19','2018-10-11','2019-02-14',1,1,2,2,2,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 UNION ALL
SELECT 3,'far','2019-01-13','2019-07-20','2020-01-23',0,0,0,0,1,1,1,1,1,1,1,2,2,2,2,2,2,0,0,0,0,0,0,0,0,0,0,0 UNION ALL
SELECT 4,'fiz','2020-01-27','2020-08-30','2021-04-02',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,1,1,2,2,2,2
select * from #correctResponse
Thanks!
August 30, 2018 at 7:53 am
Wow, that's a lot of code 🙂 I'm having trouble trying to find out where the data is coming from in the #CorrectResponse table. Can you provide what the values mean (2 in 9S-2018 for Foo and then subsequent 0s) as well as what the 0,1 and 2 represent?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 30, 2018 at 8:15 am
Thanks for the question! The mass of code was to make sure that I would give anybody who attempts to answer the Conundrum the full code I am working with. No assumptions needed. The data in the #correctResponse table is what I am hoping to output from the mass of code. I just can't seem to get to the "correct response" from the pivoted date columns. The "0" represents anytime the project is either before "Pre-Work" or after "ProjectComplete". The "1" represents when the project is in "Pre-Work" and the "2" represents when the project is being "Worked".
Does that answer your question or do I need to go into more detail? I have been bashing my head against this for a couple of days now and if no one has any advice I will most likely go to a cursor solution (insert shudder here). Something I would like to avoid as much as possible.
Thanks!
August 30, 2018 at 9:53 am
ok, I think I have what you need. I changed the names of the columns so the ordering would be correct (I used YYYY-MMMonthName instead of MMMonthName-YYYY). I also used Jeff Moden's tally table but made it into a table function so I could pass in start and end numbers. I've attached that as well. Attachment didn't make it, so it is below
--Declare needed variables AND remove any temp tables that will be used
DECLARE @test-2 date = '2018-08-29';
IF OBJECT_ID('tempdb..#base') IS NOT NULL BEGIN DROP TABLE #base END;
IF OBJECT_ID('tempdb..#result1') IS NOT NULL BEGIN DROP TABLE #result1 END;
IF OBJECT_ID('tempdb..#result2') IS NOT NULL BEGIN DROP TABLE #result2 END;
IF OBJECT_ID('tempdb..##SEPresults2') IS NOT NULL BEGIN DROP TABLE ##SEPresults2 END;
CREATE TABLE #result1 ([N] tinyint, MonthId tinyint, MonthVar VARCHAR(15));
CREATE TABLE #base (ProjectId int, ProjectDesc VARCHAR(25), ProjectPrePlanStart date, ProjectWorkStart date, ProjectComplete date)
DECLARE @startMonth int, @startYear int, @startDate date, @endDate date, @monthCount int, @remMonths int, @years int
DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX)
--insert the "base" data
INSERT INTO #base(ProjectId, ProjectDesc, ProjectPrePlanStart, ProjectWorkStart, ProjectComplete)
VALUES
(1,'foo','2018-05-06','2018-07-02','2018-09-15'),
(2,'bar','2018-06-19','2018-10-11','2019-02-14'),
(3,'far','2019-01-13','2019-07-20','2020-01-23'),
(4,'fiz','2020-01-27','2020-08-30','2021-04-02')
DECLARE @tblMonth TABLE (MonthId tinyint, MonthVar VARCHAR(3))
--because this will be pivoted, each month entry needs to be unique
-- Use Tally table function to populate
insert into @tblMonth(MonthId,MonthVar)
select N, right('00' + Cast(N as varchar(2)), 2) + Left(DATENAME(m, Cast(N as varchar(2)) + '/1/2018'),1)
from dbo.Tally (1,12)
--if the month is roughly 1/2 over roll over to next month. This is expected to run at the end of the current month or at the beginning.
select @startMonth = CASE WHEN DATEDIFF(DAY,@test,EOMONTH(@test)) < 15 THEN MONTH(DATEADD(MONTH,1,@test)) ELSE MONTH(@test) END
select @startYear = CASE WHEN @startMonth > 1 THEN YEAR(@test) ELSE YEAR(DATEADD(YEAR,1,@test)) END
select @startDate = CONVERT(DATE,CONVERT(CHAR(4),@startYear) + '-' + CONVERT(VARCHAR(2),@startMonth) + '-01')
select @endDate = CONVERT(CHAR(4),YEAR(DATEADD(YEAR,2,@startDate))) + '-12-31'
select @monthCount = DATEDIFF(MONTH,@startDate, @endDate)
select @remMonths = @monthCount % 12
select @years = @monthCount / 12
insert into #result1(N,MonthId,MonthVar)
select 0, N, right('00' + Cast(N as varchar(2)), 2) + Left(DATENAME(m, Cast(N as varchar(2)) + '/1/' + cast(@StartYear as varchar(4))),1)
from dbo.Tally (1,12)
where N >= 9
union all
select Y.N, T.N, right('00' + Cast(t.N as varchar(2)), 2) + Left(DATENAME(m, Cast(T.N as varchar(2)) + '/1/' + cast(@StartYear + Y.N as varchar(4))),1)
from dbo.Tally (1,12) T
cross apply dbo.Tally (1,2) Y
alter table #result1 add MonthDate date
--Update the MonthVar to show the year
UPDATE #result1
SET MonthVar = cast(@startYear + N as VARCHAR(4)) + '-' + MonthVar
, MonthDate = cast(Cast(MonthID as varchar(2)) + '/1/' + cast(@startYear + N as char(4)) as date)
--Correct Response
IF OBJECT_ID('tempdb..#Result3') IS NOT NULL BEGIN DROP TABLE #Result3 END;
select MonthId, MonthVar, MonthDate, ProjectID, ProjectDesc, ProjectPrePlanStart, ProjectWorkStart, ProjectComplete,
cast(
case when MonthDate > ProjectComplete then 0
when MonthDate < ProjectPrePlanStart then 0
when MonthDate > ProjectWorkStart then 2
when MonthDate < ProjectWorkStart then 1
else 0
end as int) ProjectStatus
INTO #Result3
from #result1 r
cross apply #base
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.MonthVar)
FROM #result3 c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ProjectID, ProjectDesc, ProjectPrePlanStart, ProjectWorkStart, ProjectComplete , ' + @cols + ' from
(
select ProjectID, ProjectDesc, ProjectPrePlanStart, ProjectWorkStart, ProjectComplete ,ProjectStatus,MonthVar
from #result3
) x
pivot
(
max([ProjectStatus])
for MonthVar in (' + @cols + ')
) p '
execute(@query)
--Correct Response
IF OBJECT_ID('tempdb..#correctResponse') IS NOT NULL BEGIN DROP TABLE #correctResponse END;
CREATE TABLE #correctResponse
(ProjectId int, ProjectDesc varchar(25), ProjectPrePlanStart date, ProjectWorkStart date,
ProjectComplete date, [9S-2018] tinyint, [10O-2018] tinyint, [11N-2018] tinyint, [12D-2018] tinyint,
[1J-2019] tinyint, [2F-2019] tinyint, [3M-2019] tinyint, [4A-2019] tinyint, [5M-2019] tinyint,
[6J-2019] tinyint, [7J-2019] tinyint, [8A-2019] tinyint, [9S-2019] tinyint, [10O-2019] tinyint,
[11N-2019] tinyint,[12D-2019] tinyint,[1J-2020] tinyint, [2F-2020] tinyint, [3M-2020] tinyint,
[4A-2020] tinyint, [5M-2020] tinyint, [6J-2020] tinyint, [7J-2020] tinyint, [8A-2020] tinyint,
[9S-2020] tinyint, [10O-2020] tinyint,[11N-2020] tinyint,[12D-2020] tinyint)
insert into #correctResponse
SELECT 1,'foo','2018-05-06','2018-07-02','2018-09-15',2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 UNION ALL
SELECT 2,'bar','2018-06-19','2018-10-11','2019-02-14',1,1,2,2,2,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 UNION ALL
SELECT 3,'far','2019-01-13','2019-07-20','2020-01-23',0,0,0,0,1,1,1,1,1,1,1,2,2,2,2,2,2,0,0,0,0,0,0,0,0,0,0,0 UNION ALL
SELECT 4,'fiz','2020-01-27','2020-08-30','2021-04-02',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,1,1,2,2,2,2
select * from #correctResponse
-- Tally Table Function
CREATE FUNCTION [dbo].[Tally]
(
@pMin BIGINT
,@pMax BIGINT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
WITH T1(F) AS
(
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1
),
T2(F) AS
(SELECT 1 FROM T1 A, T1 B),
T3(F) AS
(SELECT 1 FROM T2 A, T2 B),
T4(F) AS
(SELECT 1 FROM T3 A, T3 B),
cteTally(N) AS
(
SELECT TOP ((@pMax-(((ABS(@pMin)+@pMin)/2)))+1)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
+ ((((ABS(@pMin)+@pMin)/2))-1)
FROM T4
)
SELECT
N
FROM
cteTally T
;
GO
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 30, 2018 at 11:05 am
Thank you sir. It is much more elegant than the solution I was working on.
August 30, 2018 at 12:10 pm
logitestus - Thursday, August 30, 2018 11:05 AMThank you sir. It is much more elegant than the solution I was working on.
I'm not elegant is the right word 🙂 , but you are welcome
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply