How to calculate how many weeks

866 views sql
2

I am on SQL Server and I have a table containing values like this:

Date        |  Key
2017-06-01  |  X
2017-06-15  |  Y
2017-06-01  |  X
2017-06-05  |  X
2017-05-23  |  Z
2017-06-05  |  Z
2017-04-10  |  Z
2017-04-10  |  Y

and what I need returned by the query is the number of weeks contained by each key with the starting date being: 2017-03-27

So given our earlier example, the expected output would be:

X  |  2
Y  |  2
Z  |  3

This is because even though 01/06 and 05/06 (the two dates contained by X) are only 5 days apart they are on two different weeks (weeks being Monday to Sunday) when counting from 27/03. The number of entries related to a specific key doesn't matter, only the number of weeks dates related to that key fit into.

answered question

Weeks according to who? ISO's Monday to Sunday, or US' Sunday to Saturday etc.

Sorry, weeks are monday to sunday.

Is the week (Mon to Sunday) fixed or can change?

3 Answers

12

You can count the days after 2017-03-27 and divide by 7:

select t.*, ( datediff(day, '2017-03-27', date) / 7 ) as num_weeks
from t;

posted this
10

I think this should work:

SELECT [Key], COUNT(DISTINCT DATEPART(WEEK,[Date])) Cnt
FROM yourTable
GROUP BY [Key]

posted this
12

Another approach would be to skip the test in case of an emergency release.

For that, follow "Skipping Tests in GitLab CI" from Andi Scharfstein, and:

  • add "skip test" in the commit message triggering that emergency release
  • check a variable on the test stage

That is:

.test-template: &test-template
  stage: tests
  except:
    variables:
    - $CI_COMMIT_MESSAGE =~ /\[skip[ _-]tests?\]/i
    - $SKIP_TESTS

As you can see above, we also included the variable $SKIP_TESTS in the except block of the template.
This is helpful when triggering pipelines manually from GitLab’s web interface.
Here’s an example:

https://www.cqse.eu/en/blog/skipping-tests-gitlab-ci/skip_tests.png

posted this

Have an answer?

JD

Please login first before posting an answer.