grouping data on weekly basis

Discussion in 'microsoft.public.sqlserver.datamining' started by preet, Apr 14, 2007.

  1. preet

    preet Guest

    What I wish to do is to sort data on week basis, calculate the highs and
    lows of the week as a whole, how do i do it?

    This data is taken from historic data of site, so if this
    sounds confusing u can download from there to check.

    I have data in this format

    ACC EQ 2007-04-13
    00:00:00 726.35000000000002 729.85000000000002 750.0 726.0 748.300000000
    00007 822711.0 6112.9023189999998
    ACC EQ 2007-04-12
    00:00:00 732.45000000000005 728.0 733.89999999999998 720.25 726.35000000
    000002 440844.0 3210.4995345000002
    ACC EQ 2007-04-11
    00:00:00 747.30000000000007 745.0 747.10000000000002 711.5 732.450000000
    00005 759632.0 5609.9049940000004
    ACC EQ 2007-04-10
    00:00:00 741.64999999999998 743.0 753.5 733.5 747.30000000000007 929355.
    0 6931.2962205000003
    ACC EQ 2007-04-09
    00:00:00 721.80000000000007 729.0 744.30000000000007 726.5 741.649999999
    99998 688120.0 5074.5381635000003
    ACC EQ 2007-04-05
    00:00:00 714.35000000000002 714.70000000000005 735.95000000000005 710.20
    000000000005 721.80000000000007 1377973.0 9994.7093645000004
    ACC EQ 2007-04-04
    00:00:00 704.30000000000007 680.0 719.70000000000005 678.10000000000002
    714.35000000000002 2941263.0 20742.453524
    ACC EQ 2007-04-03
    00:00:00 704.45000000000005 708.0 718.0 692.14999999999998 704.300000000
    00007 1054327.0 7428.0063499999997
    ACC EQ 2007-04-02
    00:00:00 735.25 720.0 729.89999999999998 698.35000000000002 704.45000000
    000005 637955.0 4576.0338105000001
    ACC EQ 2007-03-30
    00:00:00 734.75 738.80000000000007 745.95000000000005 731.0 735.25 81966
    0.0 6050.6284114999999
    ACC EQ 2007-03-29
    00:00:00 734.70000000000005 734.0 742.0 727.0 734.75 1459171.0 10704.409
    preet, Apr 14, 2007
    1. Advertisements

  2. preet

    MC Guest

    First, I would use calendar table with property 'week' or something like
    that. Point would be to have a mapping of date - week so you can without
    additional calculation in query know the week. All that is left at that
    point is goruping by week and selecting max() and min() values. Query would
    be something like:

    calendar.week, max(table.value) as MaxValue, min(table.value) as
    inner join calendar on =
    group by

    ---- in this case, calendar table needs to have only two columns date and
    week. However, if week value is not unique over years (and you want it to
    be), you can add year column and then add in the join:
    AND year( = calendar.year


    PS. If you dont have a calendar table google for it and youll find more then
    a couple of scripts for generating it
    MC, Apr 14, 2007
    1. Advertisements

  3. preet

    preet Guest

    Got this on the net, let me know if this is correct

    1. Create the calendar table:

    Create Table BusinessCalendar
    (BDate smalldatetime Primary Key, BType tinyint)

    2. Load dates into table:

    --This will handle business dates and weekends but not holidays.
    Change date range as needed.

    Declare @dt smalldatetime
    Set @dt='Jan 1 1990'
    While @dt<='Dec 31 2010'
    Insert BusinessCalendar Values
    When datepart(dw,@dt) Between 2 and 6
    Then 1 Else 0

    3. Add holiday dates:

    You will need to update the table to mark the holidays. You could
    write a script to update standard holidays or manually update the table.
    If you don't need to account for holidays then omit this update.

    4. Create script to count days:

    After you have finished it will be easy to count business (or
    work) days with a simple query. This query could be included in a stored
    procedure or, if running SQL 2000, in a user-defined function.

    Declare @startdate smalldatetime, @enddate smalldatetime
    Set @startdate='dec 12 2002'
    Set @enddate='jan 7 2003'

    Select BDays=Count(*)
    From BusinessCalendar
    Where BType=1
    And BDate Between @startdate And @enddate
    preet, Apr 22, 2007
  4. preet

    preet Guest

    I get the following error on step 2 above

    1 row(s) affected)

    Server: Msg 2627, Level 14, State 1, Line 5
    Violation of PRIMARY KEY constraint 'PK_BusinessCalendar'. Cannot insert
    duplicate key in object 'BusinessCalendar'.
    The statement has been terminated.
    Server: Msg 2627, Level 14, State 1, Line 5
    Violation of PRIMARY KEY constraint 'PK_BusinessCalendar'. Cannot insert
    duplicate key in object 'BusinessCalendar'.
    The statement has been terminated.
    preet, Apr 22, 2007
  5. preet

    MC Guest

    Check whats the PK (probably date). Perhaps you started the script twice or

    MC, Apr 22, 2007
  6. preet

    preet Guest

    Create Table BusinessCalendar
    (BDate smalldatetime Primary Key, BType tinyint)

    PK is the date column

    I ran the script just once, deleted the values and tried again, same

    does not advance to row 2

    maybe there is no step.
    preet, Apr 23, 2007
  7. preet

    MC Guest

    Yeah, it seems the script doesnt increment the date in the loop. I would
    suggest finding another script or adding a step to this one.

    MC, Apr 27, 2007
  8. preet

    preet Guest


    SQL Recipe: Federal government working days between two dates.

    If you’d ever like to programmatically determine the number of federal
    working days between two dates, here’s a quick and dirty solution I
    cooked up the other day in Microsoft SQL Server. It can be useful in
    monitering deadlines and evaluating bureaucratic processing speeds (say,
    for example, a log of Freedom of Information Act requests).

    It’s a three-step process.

    1. Create a calendar table that distinguishes working days from
    weekends and holidays.
    2. Create a user-defined function that will use that calendar to
    count the number of working days between two dates.
    3. Count the days.

    Below you can find a calendar creation script I adapted from one
    published on Besides distinguishing weekdays from weekends,
    it marks off all federal holidays — as specificed by the U.S. Office of
    Personnel Management — from Jan. 1, 2000 to Dec. 31, 2010.

    First we create the calendar table. It will have three fields, one for
    the date and then two binary fields, one that will automatically
    determine the weekdays using the DATEPART function, and another that we
    will use later to designate working days.

    CREATE TABLE dbo.FederalCalendar (
    WHEN DATEPART(dw, dt) IN (1,7) THEN 0
    ELSE 1 END),
    isWorkDay BIT DEFAULT 1

    Next we need to populate the date field, dt, with the range of days in
    our calendar. In this case, it will be from Jan. 1, 2000 through Dec.
    31, 2010.

    SET @dt = '20000101';
    WHILE @dt<= '20101231'
    INSERT dbo.FederalCalendar(dt) SELECT @dt;
    SET @dt = @dt + 1;

    Then winnow down our working days field by eliminating the weekends.

    UPDATE dbo.FederalCalendar
    SET isWorkDay = 0
    WHERE isWeekday = 0;
    preet, Apr 28, 2007
  9. preet

    MC Guest

    Great. Now, since you have a calendar table, does the grouping on weeks

    MC, Apr 28, 2007
  10. preet

    preet Guest

    select, max(acc.high) as MaxValue, min(acc.low) as
    inner join businesscalendar on =
    where businesscalendar.btype = 1
    group by

    does not return any rows

    ACC table has columns -- [ date, prevclose, open, high, low, close ]

    businesscalendar table has columns -- [ date, btype ] where btype = 1 is
    mon to friday and btype = 0 is sat sun.

    where am i wrong here ?
    preet, May 4, 2007
  11. preet

    preet Guest

    A correction in the above post, the table used was wrong

    the query used is

    federalcalendar.dt, max(acc.high) as MaxValue, min(acc.low) as
    inner join federalcalendar on = federalcalendar.dt
    where federalcalendar.isweekday = 1 and federalcalendar.ismonday = 1
    group by

    now the column names of acc table are the same as above.

    the columns of federalcalendar are
    [ dr, isweekday, isworkday, ismonday ]

    what i wish to do is to get the max high for the entire week and the min
    low for the entire week and display it against a single date value of
    the week preferably monday.

    how do i do this.
    preet, May 4, 2007
  12. preet

    MC Guest

    You dont group by date, you group by week. You need to have a 'week' column
    in the calendar.

    So, something like:

    federalcalendar.week, max(acc.high) as MaxValue, min(acc.low) as
    inner join federalcalendar on = federalcalendar.dt
    --- where federalcalendar.isweekday = 1 and federalcalendar.ismonday = 1
    dont think you need this
    group by

    If you want the monday, you could probably get away with min(date), it
    should be the mondays date...

    MC, May 4, 2007
  13. preet

    MC Guest

    This script will generate all the data you need. Just insert the results in
    table (change start date and enddate):

    declare @start datetime, @end datetime

    set @start = '2006-01-01'

    set @end = '2010-01-01'


    with calendar(date,isweekday, y, q,m,d,dw,monthname,dayname,w) as


    select @start ,

    case when datepart(dw,@start) in (1,7) then 0 else 1 end,






    datename(month, @start),

    datename(dw, @start),

    datepart(wk, @start)

    union all

    select date + 1,

    case when datepart(dw,date + 1) in (1,7) then 0 else 1 end,

    year(date + 1),

    datepart(qq,date + 1),

    datepart(mm,date + 1),

    datepart(dd,date + 1),

    datepart(dw,date + 1),

    datename(month, date + 1),

    datename(dw, date + 1),

    datepart(wk, date + 1) from calendar where date + 1< @end


    select * from calendar option(maxrecursion 32767)


    MC, May 4, 2007
  14. preet

    preet Guest

    I guess I have not been able to explain properly

    Let me try with a skeleton query

    b.dt, a.openprice on monday, max(a.highprice) as MaxValue,
    min(a.lowprice) as
    MinValue, a.closeprice on friday
    acc a
    inner join federalcalendar b on a.dt = b.dt
    where b.isweekday = 1 and b.ismonday = 1
    group by

    so here the first column should return 1 date from the entire week
    representing the week.

    openprice is to be selected as the open price on the first trading day
    of the week. represented by min(dt)

    close price is to be selected as the close price on the last trading day
    of the week represented by max(dt)

    highprice is to be calculated as high of the highprice column of the
    week and same for lowprice.

    That ...... is my problem.

    Further, I intend to proceed the same way for gouping monthly data.
    preet, May 4, 2007
  15. preet

    MC Guest

    Aaaahhh :). You still need a week column and group by week (since you're
    looking for high and low on week). So, add a week atribute (you have the
    select now) and then lets try with something like:

    max(case when b.ismonday = 1 then b.dt else null end ) as DateMonday,
    max(case when b.ismonday = 1 then a.openprice else null end) as
    max(case when datename(dw,b.dt) = 'Friday' then b.dt else null end) as
    max(case when datename(dw,b.dt) = 'Friday' then ClosePrice else null
    end) as ClosePriceFriday
    max(a.highprice) as MaxValueOfWeek,
    min(a.lowPrice) as MinValueOfWeek
    acc a
    inner join federalcalendar b on a.dt = b.dt
    b.isweekday = 1
    group by

    I have hardcoded monday and friday since it seems like you want them on
    monday and friday.

    MC, May 5, 2007
  16. preet

    MC Guest

    Oh yeah, add a week column to the select part. Just to know for which week
    you're getting the info ;).

    MC, May 5, 2007
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.