How to calculate visits by month if customers has assigned days of visits

Question

i have this table

customer sunday monday tuesday wedesneday thursday friday saturday
1        0      0       1       1          1       0       0
2        0      1       0       0          0       0       1
3        0      0       1       0          1       0       1
4        0      0       1       1          0       1       0
5        0      1       0       0          1       1       1
6        0      0       1       0          1       1       0
7        0      1       0       1          1       0       1
8        0      0       0       0          1       1       0
9        0      1       1       1          0       0       1

now i have next parameter> year and months january, february, march(or any user chooses) how scheduled visits are going to have by month?

Note: for every month no precisely start with sunday or finish with saturday, and monts doesn't have the same quantities days.

I believe i could get the first day and last day for every month choosed by user

with a loop and a select using datepart > datepart(weekday,'1/'+(some month)+'\'+year_choosed_by_user)

I believe i could get something as it

month, startday, lastday, days
1        0         2       31
2        3         3       29
3        4         6       30

where 0=sunday and 6=saturday, could it help? i'll get this #tmptable using datepart

@FlorinGhita, customers has the same scheduled visits every year — angel, Nov 30 '12 at 18:14
ok, I think I understand the question now. But I'm not good on Sql Server, sorry. — Florin Ghita, Nov 30 '12 at 18:16
@angel even with your explanation in the comments i still don't see how this is possible given the data you've provided. there's no way to discern the month from anything in the question as you've posed it. could you please post what your desired results will look like? — swasheck, Nov 30 '12 at 18:19
The question is quite unclear, I would suggest to reformulate it. Also, the sample table is not normalised, which makes querying it with SQL a very hard task. — Diego, Nov 30 '12 at 18:19
@angel sure it could ... if you had a date anywhere in your data at all. — swasheck, Nov 30 '12 at 18:31
@swasheck i believe i need is start day, finish day, and how many days has every month, wouldn't it be enough? — angel, Nov 30 '12 at 18:33
@angel What is the result that you want from a query? Can you edit your post and provide desired result? — Taryn, Nov 30 '12 at 18:39
How can you tell the month and the year of any given day in your table? at least the start day. — Mahmoud Gamal, Nov 30 '12 at 18:39

Taryn · Accepted Answer · 2012-11-30T18:50:11.543

5

I am not exactly clear what you are trying to do but the solution below, creates a list of dates, UNPIVOTs your existing data and then gets a count of how many appointments for each month:

;with dates (datevalue) as
(
  select cast('2012-10-01' as datetime)
  union all
  select dateadd(d, 1, datevalue)
  from dates
  where dateadd(d, 1, datevalue) <= cast('2012-12-01' as datetime)
) 
select count(*) TotalAppts, datename(month, d.datevalue) [Month]
from dates d
inner join 
(
  select client, value, col
  from yourtable
  unpivot
  (
    value
    for col in (Sunday, Monday, Tuesday, Wednesday, 
                Thursday, Friday, Saturday)
  ) unpiv
) src
  on datename(dw, d.datevalue) = col
where value = 1
group by datename(month, d.datevalue)

See SQL Fiddle with Demo

Result:

| TOTALAPPTS |    MONTH |
-------------------------
|          5 | December |
|        122 | November |
|        125 |  October |

edited Nov 30 '12 at 18:50

answered Nov 30 '12 at 18:42

Taryn

242,637
56
362
405

You are not filtering on `value`. Only `1` days should be counted. Otherwise fine! – Andriy M Nov 30 '12 at 18:48
good answer! i was working up the way to create the dates which is [here](http://sqlfiddle.com/#!3/8b048/4). @angel, combine the two and you should be set – swasheck Nov 30 '12 at 18:49
@angel: If you need also to split the figures by customers, just add the `client` column (or `customer` or whatever the actual equivalent is) both to the `group by` and to the main `select`. – Andriy M Nov 30 '12 at 18:57
Thanks, i haven't never seen a pivot/unpivot but the last result is the right result. – angel Nov 30 '12 at 19:11

score 0 · Answer 2 · answered Nov 30 '12 at 18:28

You might do well to employ some sort of bitmask. For example, assign the days of the week a value according to binary numbers:

Sunday     1
Monday     2
Tuesday    4
Wednesday  8
Thursday  16
Friday    32
Saturday  64

And the same for months of the year:

January       1
February      2
March         4
April         8
May          16
June         32
July         64
August      128
September   256
October     512
November   1024
December   2048

In your table structure, you need only three columns as follows:

customer_id (INT)  days (INT)   months (INT)
1                  28           0
2                  66           0
3                  84           0

Customer 1 visits on Tuesday, Wednesday and Thursday. These values equal 28 as shown in the above example. For any given combination of days of week, the resulting number is unique due to binary addition. If you deconstruct the integer 28, it has the binary form:

00011100

If you consider only the last 7 places, the 1 values correspond to Tues, Wed, Thurs just like your multiple columns.

So how does this help? You can use bitwise operators to construct queries.

If you need to determine, for example, what customers visit on Monday, you would need to OR the days column with its value, 2.

For customers that visit in May and June, you would use the value 48.

This suggestion may be useful for you in decreasing the number of columns and simplifying queries, but I'm not 100% sure what you're asking.

How to calculate visits by month if customers has assigned days of visits

2 Answers2