How to calculate Peak/ Off-peak & Work days/ weekend

I would appreciate if someone help me to achieve it:

I have a table of call records and I want to distinguish which call is during peak (0700 to 2000 hours), offpeak (2001 to 0659 hours) and whether it is during normal week day or weekend (Satureday/ Sunday). Here is my Table:

Call_ID
Datetime
CLI
Called_Destination
Duration
Peak_off-peak*
Weekday*

* These two columns do not come with data, rather I want to fill them dynamically with flags for instance if time is Peak then value put in Peak_off-Peak column= P , if offpeak then Peak_off-Peak= OP, same with Weekday=D (work day except weekends) and Weekday=W if weekend.

Thanks in advance for your help.

Kitto

Thanks. However I shall appreciate if kindly let me know what formula I can put in a computed column. For instance, Peak_off-Peak and Weekday are my computed columns, and values would be filled base on data in datatime field.

use the same formulas as in the UPDATE statement

Tried but it said "error when validiating formula".

whoa, that's weird, because i tested the UPDATE statement and it works

can we see your attempt?

Sorry my mistake, I have found it working. Thanks a lot.

One last thing, when my Weekday = Weekend (W), I want to set my [Peak_off-peak] filed to Off-peak (OP) even it is Peak time (since on weekend, off-peak rate applied for calls).

Thanks for yoru help.

well, to make that change, you would have to modify the CASE expression for [Peak_off-peak] slightly to include a test for weekday

Sorry for inconveniece, since I am newbie in SQL that's why stupid questions. This is what I tried but it says invalid column 'D'.

update CALCULATIONS1 set [Weekday] = case when datepart(dw,[dATE]) between 2 and 6 then 'D' else 'W' end
, [Peak_off-peak] = case when convert(char(5),[time],8) between '07:00:00' and '20:00:00' or [Weekday]="W" then 'P' else 'OP' end

so did you give up on the idea of computed columns? because you don't update computed columns, you just define them in a CREATE TABLE or ALTER TABLE statement

Well, I decided to use update on existing table (data filled) since I was not able to work computer columns. Can you please guide the correct syntax of Update statement so that it first check if it is Weekend then update [Peak_off-peak] to 'OP'.

Thanks.

change the OR to AND and that'll do it