Practice Activity - Joining SQL Server daily data to weekly data
How can you connect two tables with different levels of granularity.
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...
----
How can you connect daily data to weekly data. You need to have something common between them, such as the Week Commencing date.
Joining data together is a common task in data analysis, and it's essential for finding patterns and insights in your data. By joining the data, you will create a single data set that contains the data from both days. This practice activity will help you learn how to join the data effectively.
In this video, we'll look at how we can import weekly data into daily data, and then how to summarise it back to the weekly level.
The starting code is:
DROP TABLE IF EXISTS DailyData
GO
CREATE TABLE DailyData
(EmployeeDate datetime,
EmployeeCount int);
INSERT INTO DailyData
VALUES ('2024-02-01', 5), ('2024-02-02', 2), ('2024-02-03', 5), ('2024-02-04', 7), ('2024-02-05', 8), ('2024-02-06', 7), ('2024-02-07', 4)
, ('2024-02-08', 1), ('2024-02-09', 6), ('2024-02-10', 1), ('2024-02-11', 3), ('2024-02-12', 4), ('2024-02-13', 3), ('2024-02-14', 6)
DROP TABLE IF EXISTS WeeklyData
GO
CREATE TABLE WeeklyData
(WeekCommencing date,
WeekDescription varchar(20))
INSERT INTO WeeklyData
VALUES ('2024-01-28', 'Week 1'), ('2024-02-04', 'Week 2'), ('2024-02-11', 'Week 3')
SELECT * FROM DailyData D
SELECT * FROM WeeklyData W
SELECT * --, EmployeeDate - (DATEPART(WEEKDAY, EmployeeDate) - 1)
FROM DailyData D
LEFT JOIN WeeklyData W
ON W.WeekCommencing = EmployeeDate - (DATEPART(WEEKDAY, EmployeeDate) - 1)
SELECT WeekCommencing, WeekDescription, SUM(EmployeeCount) AS TotalEmployeeCount
FROM DailyData D
LEFT JOIN WeeklyData W
ON W.WeekCommencing = EmployeeDate - (DATEPART(WEEKDAY, EmployeeDate) - 1)
GROUP BY WeekCommencing, WeekDescription
ORDER BY WeekCommencing, WeekDescription
----
Links to my website are:
70-461, 70-761 Querying Microsoft SQL Server with T-SQL: http://idodata.com/querying-microsoft...
98-364: Database Fundamentals (Microsoft SQL Server): http://idodata.com/database-fundament...
SQL Server Essential in an Hour: http://idodata.com/sql-server-essenti...
70-462 SQL Server Database Administration (DBA): http://idodata.com/sql-server-databas...
DP-300: Administering Relational Databases: http://idodata.com/dp-300-administeri...
Microsoft SQL Server Reporting Services (SSRS): http://idodata.com/microsoft-sql-serv...
SQL Server Integration Services (SSIS): http://idodata.com/sql-server-integra...
SQL Server Analysis Services (SSAS): http://idodata.com/sql-server-ssas-mu...
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): https://rebrand.ly/microsoft-powerpiv...
1Z0-071 Oracle SQL Developer – certified associate: http://idodata.com/iz0-071-oracle-sql...
SQL for Microsoft Access: http://idodata.com/sql-for-microsoft-...
DP-900: Microsoft Azure Data Fundamentals: http://idodata.com/dp-900-microsoft-a...
Смотрите видео Practice Activity - Joining SQL Server daily data to weekly data онлайн, длительностью часов минут секунд в хорошем качестве, которое загружено на канал SQL Server 101 06 Июль 2023. Делитесь ссылкой на видео в социальных сетях, чтобы ваши подписчики и друзья так же посмотрели это видео. Данный видеоклип посмотрели 665 раз и оно понравилось 29 посетителям.
