[Microsoft — MS SQL] Create WoW in T-SQL
As a BI Analyst, ensure the data quality for the clients are correct and readable is my main working task. The data source is from upstream the database that IT maintain. So how to detect them quickly via SQL script. This week I just create a simple SQL script to do this task. The data table I would like to detect is in SQL server in which the data was extracted from Teradata database. Therefore, I just need to write a query via MS SQL which is a kind of T-SQL. when I wrote down the SQL for comparing the data amount with last week. I use a WoW calculation.
- Create a column with previous week aggerate count, i use 1 preceding and 1 preceding, it’s not difficult just think of the scope of your partition and the order you’d like to compare with previous one.
2. This is pretty simple, just write a division. However, I got stuck here because in SQL, we couldn’t get the result by directly write a / in the code. Need to convert the value at first. So I just added those value converted to decimal. I think that’s cause the aggerate count(*) in SQL is not quite a numeric number.
On above ways, I create a simple data set that I can observed the plant code data per stage the weekly lot count pattern and compare with previous week intuitively. Such as the lot count on 2020w02 2388 compared with lot count on w01 701, it increases around 2.407 percentage (240.7%)
Reference: