Producing a view with a 'set' output

[EnglishTwit]

Hi, and apologies if this is a long explanation, and any help greatfully received.

I have a list of stores that are visited twice in a period, and a result calculated for each visit. (the result is not important to this question).

There are thirteen periods in a year, and there are 4 weeks each period.

If each store has all it's visits, then 25 to 26 visits will be performed.

In this case, each visit could be assigned Visit 1, Visit 2, etc through to Visit 26. I am calling these visits Corporate Visits, as these visits happen within set periods.

However, if a store opens half way through the year, then whilst it's first visit (relative to the store) is visit 1, it will actually be visit 13 (or 14).

I can't seem to resolve the following problem.

There are 52 weeks in a year. Therefore, if I divide the week number by 2, then this would give me a corporate week number I could put the score in. So, week 52/2 is visit 26, 50/2 is visit 25, 40/2 is visit 20, and so on.

However, week 51/2 is 25.5, 49/2 is 24.5.

The answer is not rounding, as if you round week 5/2, you get visit 3, which is the same as week 6/2.

I have also tried ranking. With all 25/26 visits, this works nicely giving a rank of 1 to 26 which I could use as corporate visit number. But, if a store opens half way through the year, it will only rank 1 to 13, which would not match the corporate visit schedule. (if a store opened in the last 4 weeks and had 2 visits, it would rank them 1 and 2, rather than making them visits 25 and 26).

Discuss and enjoy!

 

[jdraw]

It seems your problem stems from the definition or policy related to Corporate Visit Number.
You seem to hold it sacred - but the reality of a store's opening in Dec and having 2 visits
(Visit 1 and Visit 2) is a fact - regardless of "Corporate Visit Number".

As a suggestion, why not identify the store, and the visit date and any particulars specific to the visit.
Then a query to determine Number of Visits to the Store by Quarter(Period).

You are confusing the Time/Date/Period in which a Visit occurs with the Count of the Number of Visits to a Store.

Hope this is helpful.

 

[EnglishTwit]

Hi,

Thanks for the reply - I had a conversation with a colleague here and we came to the same conclusion.

We are going to build a summary table in SQL Server that will have place holders for all possible 26 visits, and only fill the place holders with data based around periods and weeks. (I say we, I'm just going to make the tea to be honest)

So, your thoughts have helped us confirm we are thinking along the right lines.