Week by Week report

 

Week By Week Report Analysis

If you are a Data Warehouse Analyst, it might be a request from your manager, director or CEO to do a report to see the growth of a KPI during the time.
How KPI “X” evolve during Years, Months, Dates , etc.
The tricky part comes when you must do a week-by-week analysis.

Let’s see below the steps that must be performed by Data Warehouse Analyst.

Environment 

SQL Server  2019 Developer Edition.

SQL Server Management Studio v18.10

Database: DWREAD

Schema: FORMULA_STAG

Table: RACES

The table is from Kuggle  Formula 1 World Championship (1950 - 2020)  👈

Steps

We go to MS SQL Database and execute the following statement:

SELECT @@DATEFIRST;

This statement shows us that the first day of the week is Sunday (default). 

So, we understand that the week is [Sunday, Monday, …, Saturday]. 

All the dates between Sunday and Saturday is grouped into one week.

Our table has a date column: date
If we want to identify in which week belongs each date we use the datepart () function.

• datepart(week,a.date ) : return integer , which Week belongs the a.date .
• datepart(year, a.date) : return integer , which Year belongs the a.date .

SELECT  cast( datepart(YEAR, a.date) as varchar(8)) + 

        '/' + 

        cast(datepart(week,a.date ) as varchar(8)) [yyyy/ww],
a.date [date]
FROM [FORMULA_STAG].[RACES] a
WHERE a.year = 2021
ORDER BY [yyyy/ww], [date]

* in order to succeed the concatenation should apply cast on each datepart *

The result set  of the above query is the following:

weeks & dates