weekday or Weekend

2 Dec 2005 11:21 PM

Jim Abel

I have a simple table the has 3 columns
CDATE   NCout RCount
11/26/05 423      3545
11/27/05 474      3597
11/28/05 636      3512
11/29/05 674      3587
11/30/05 692      3512
12/01/05 515      3577
I want to add another column that would identify the date as a weekday or a
weekend.
Can you help with this? The NCount comes from one datasource and r Rcount
from another. My end goal is to also add a mean and Standard Devation value
for each record but I need to separate the weekdays from the weekends first
so that when I calculate the statistical values one set is for weekdays and
the other for weekends. I figure that I would do this in stages so that I
get a clear understanding of each piece of the logic. Thanks in advance for
the help

2 Dec 2005 11:29 PM

Aaron Bertrand [SQL Server MVP]

-- assuming a week where day 1,7 = weekend (datefirst = sunday):

ALTER TABLE simple_table
    ADD IsWeekday CHAR(1)
    AS CONVERT
    (
        CHAR(1),
        CASE
            WHEN DATEPART(WD, CDATE) IN (1,7) THEN 'N'
            ELSE 'Y'
        END
    )

Show quote

"Jim Abel" <JimA***@discussions.microsoft.com> wrote in message
news:A31E45A1-3CA2-478C-9C47-FF3DFE7DD57A@microsoft.com...
>I have a simple table the has 3 columns
> CDATE   NCout RCount
> 11/26/05 423      3545
> 11/27/05 474      3597
> 11/28/05 636      3512
> 11/29/05 674      3587
> 11/30/05 692      3512
> 12/01/05 515      3577
> I want to add another column that would identify the date as a weekday or
> a
> weekend.
> Can you help with this? The NCount comes from one datasource and r Rcount
> from another. My end goal is to also add a mean and Standard Devation
> value
> for each record but I need to separate the weekdays from the weekends
> first
> so that when I calculate the statistical values one set is for weekdays
> and
> the other for weekends. I figure that I would do this in stages so that I
> get a clear understanding of each piece of the logic. Thanks in advance
> for
> the help

16 Dec 2005 4:19 AM

Prashant D.

Hi Jim
CREATE VIEW ViewName
AS
Select CDATE, NCout, RCount, datename(dw, CDATE) as DayName
From YourTableName

Where DayName column will have actual days

Prashant Deshmukh

Show quote

"Jim Abel" wrote:

> I have a simple table the has 3 columns
> CDATE   NCout RCount
> 11/26/05 423      3545
> 11/27/05 474      3597
> 11/28/05 636      3512
> 11/29/05 674      3587
> 11/30/05 692      3512
> 12/01/05 515      3577
> I want to add another column that would identify the date as a weekday or a
> weekend.
> Can you help with this? The NCount comes from one datasource and r Rcount
> from another. My end goal is to also add a mean and Standard Devation value
> for each record but I need to separate the weekdays from the weekends first
> so that when I calculate the statistical values one set is for weekdays and
> the other for weekends. I figure that I would do this in stages so that I
> get a clear understanding of each piece of the logic. Thanks in advance for
> the help