lesson 5.
Lesson learned:
Calculate less. Calculate once ( if possible)
Why
Every calculating takes resources ( time, memory, electricity( there are some else?))
Howto
Say, you need use today's date. Indeed there is a function t get it. ( NOW() at T-SQL) instead of calling it over and over again calculate once, put to variable, use the variable. Yes the variable will not change but it will save resources
How I learned
Code review by my team lead.
Best way to jump from "code works" level to "well-written code" :)

Lesson 4
Lesson learned: Try out transaction before committing it using begin tran keywords in T-SQL
Why transaction is about changing the db. you want to try out how it works before actually ding it
How to write " begin tran" before your command.
run including these words

1. you will see num of rows affected ( it can give you some hint)

2.you can work with modified version of db around 20 min

Note -the db becomes inaccessible to other users meanwhile

3.print and run "commit" to save the changes, "rollback" to undo

4.if nothing done ( in step 3) rollback is automatically done by system in like 20 minutes

How I learned
If I have nothing to do at work, I tell that to the teamlead and sit nearby and learn from her work :)

Reply to the post by @lamed:

Yes, exactly. It's a feature, not bug.

looks like the difference between feature and bug is if it used correctly if the behavior is expected

Lesson 3.
Lesson learned
Write in easy to edit way. Especially useful to select fields in SQL , parts of concated messages.

Why
Someone is known to write with pencil with eraser on it. We have commenting / Erace options when we write code but the ease of use can differ...

How to
For sql select fields, write each one from new line and place "," before:

select
field1
,field2
,field3

so you can "comment" it at one commenting.

or concating using VBA

concated_string= _
"concated" _
& "string" _
& "smth" _
& "else"

How I learned
I think all my learning were about emotions, but here it were my ones toward code I first saw in my organization.
"," prior the item of the list looked weird.
But it's super useful and I happy I learned it :)

@tigra, thank you for the links.
Now I can study it deeper.
Indeed I tried to implement suppression but this was not enough and some compession was also needed in order to gain k-anonimity.
Interesting that wiki speaks about hashing but I can't see how it can be used to statistical data.As the users do want to have some, even generalized, picture.

Lesson 2
Lesson learned
Average of averages is not the average of initial values
Be careful with aggregating of aggregated. Sum of sum is sum of initial values, but not the average of averages.
Why
As groups have different sizes, if you do average their averages you calculate them equal-weighted, ignore their size.
How to
Calculate overall quality/ average as you calculate it for each individual grooup and not averge the averages!
You can use calculated field in pivot (Sum/ Num) to calculate the average or ( sum of good/ total sum ) to quality.
Using the type of calculation-average in pivot will be mistake ...
How I learned
Again, angry calls. :) .
We have to calculate quality parameters for each department and to each district. And again the average quality of district is not the average of quality of its departments. As they are not equall....

Continue Reading →

да, добавь мне
db and excel lessons learned

Continue Reading →

This blog will be about lessons learned at DB (dobetter stands for db) and MS Excel ( and whatever tech I will learn)...