sql server - Counting Columns with conditions, assigning values based on count -

i have table call logs. need assign time slots next call based on time slot phone number reachable in.

the relevant columns of table are:

phone number | calltimestamp

calltimestamp datetime object.

i need calculate following:

• time slot: timestamp, need calculate count each time slot (eg. 0800-1000, 1001-1200, etc.) each phone number. now, if count greater 'n' particular time slot, need assign time slot number. otherwise, select default time slot.
• weekday slot: same above, weekdays.
• priority: count of how many times number reached

here's have gone solving these issues:

priority

to calculate number of times phone number called straight forward. if number exists in call log, know called. in case, following query give me call count each number.

select distinct(phonenumber), count(phonenumber) tblcalllog group phonenumber 

however, problem need change values in field count(phonenumber) based on value in column itself. how go achieving this? (eg. if count(phonenumber) gives me value > 20, need change 5).

time slot / weekday

this i'm stumped , looking "database" way of doing things.

unfortunately, can't out of iterative process of thinking. example, if aggregating phone number (say '123456') , in time slot (say between 0800-1000 hrs), can write query this:

declare @t1start time = '08:00:00.0000' declare @t2end time = '10:00:00.0000'  select count(calltimestamp) tblcalllog  phonenumber = '123456' , format(calltimestamp, 'hh:mm:ss') >= @t1start , format(calltimestamp, 'hh:mm:ss') < @t2end 

now, go through each , every distinct phone number in table, count values each time slot , assign slot value phone number. however, there has way not involve me iterating through database.

so, looking suggestions on how solve this.

thanks

you can use datepart function week day slot. calculate time slot can try dividing number of minutes beginning of day , dividing size of time slot. return slot number. can use either case statement translate proper string or table can store slot descriptions.

select      phonenumber     , datepart(weekday, l.calltimestamp) dayofweekslot     , datediff(minute, convert(date, l.calltimestamp), l.calltimestamp) / 120 twohourslot /*you can change number of minutes different slot size*/     , count(*) count tblcalllog l group phonenumber     , datepart(weekday, l.calltimestamp)      , datediff(minute, convert(date, l.calltimestamp), l.calltimestamp) / 120