How to spread durations (in days) across years using PIVOT in SQL Server (Gantt chart)
How can you use PIVOT to calculate the number of days in each year using PIVOT.
My SQL Server Udemy courses are:
70-461, 70-761 Querying Microsoft SQL Server with T-SQL: https://rebrand.ly/querying-microsoft...
98-364: Database Fundamentals (Microsoft SQL Server): https://rebrand.ly/database-fundamentals
70-462 SQL Server Database Administration (DBA): https://rebrand.ly/sql-server-dba
Microsoft SQL Server Reporting Services (SSRS): https://rebrand.ly/sql-server-ssrs
SQL Server Integration Services (SSIS): https://rebrand.ly/sql-server-ssis
SQL Server Analysis Services (SSAS): https://rebrand.ly/sql-server-ssas-mdx
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): https://rebrand.ly/microsoft-powerpiv...
----
Suppose you have an activity starting on 30 December 2022 and ending on 3 January 2023. There are 2 days in 2022 and 3 days in 2023.
In this video, we'll look at how you can use PIVOT to reduce having to duplicate code.
PIVOT allows you to change years in a column to years in various columns going across.
----
If you are doing this as a Practice Activity, please use the following code to start. Please change LESSTHAN and GREATERTHAN to the less-than and greater-than signs.
DROP TABLE IF EXISTS Dates
CREATE TABLE Dates
(StartDate date,
EndDate date);
INSERT INTO Dates (StartDate, EndDate)
VALUES
('2016-01-05', '2020-03-31'),
('2018-03-20', '2018-05-21'),
('2017-05-10', '2017-05-10'),
('2017-01-01', '2017-01-01'),
('2017-12-31', '2017-12-31');
With MyDates as (
SELECT StartDate, EndDate
, DateDIFF(day
, case when StartDate GREATER THAN='2017-01-01' then StartDate else '2017-01-01' end
, case when EndDate LESSTHAN '2018-01-01' then DateAdd(day,1,EndDate) else '2018-01-01' end) as DaysIn2017
FROM Dates
)
SELECT StartDate, EndDate
, Case WHEN DaysIn2017 LESSTHAN 0 then null else DaysIn2017 end as DaysIn2017
FROM MyDates
