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:
----
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:

[preciousmedupe]

Hey how do you fix a code 945 on my laptop. I just need help with that my sql won't log in because of that error.

[jacekk]

Hello Philip thanks for this practice activity, below is my way before I watched your video till the end :
WITH CTE_DailyData as (
SELECT *,
case when EmployeeDate >= '2024-01-28' and EmployeeDate < '2024-02-04' then 'Week 1'
when EmployeeDate >= '2024-02-04' and EmployeeDate < '2024-02-11' then 'Week 2'
when EmployeeDate >= '2024-02-11' and EmployeeDate < DATEADD(DAY, 7, '2024-02-11') then 'Week 3'
end as [WeekDescription]
FROM DailyData
)
SELECT W.WeekCommencing
, SUM(EmployeeCount) as [EmployeeCount]
, D.WeekDescription
FROM CTE_DailyData as D
INNER JOIN WeeklyData as W
ON D.WeekDescription = w.WeekDescription
GROUP BY D.WeekDescription, W.WeekCommencing