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 nseindia.com 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
    7105
     
    preet, Apr 14, 2007
    #1
    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:

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


    ---- 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(table.date) = calendar.year

    MC

    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
    #2
    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)
    Go

    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'
    Begin
    Insert BusinessCalendar Values
    (@dt,
    Case
    When datepart(dw,@dt) Between 2 and 6
    Then 1 Else 0
    End)
    End

    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
    #3
  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
    #4
  5. preet

    MC Guest

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


    MC
     
    MC, Apr 22, 2007
    #5
  6. preet

    preet Guest

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

    PK is the date column

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

    does not advance to row 2

    maybe there is no step.
     
    preet, Apr 23, 2007
    #6
  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
     
    MC, Apr 27, 2007
    #7
  8. preet

    preet Guest

    JACKPOT


    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 aspfaq.com. 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 (
    dt SMALLDATETIME PRIMARY KEY CLUSTERED,
    isWeekDay AS CONVERT(BIT, CASE
    WHEN DATEPART(dw, dt) IN (1,7) THEN 0
    ELSE 1 END),
    isWorkDay BIT DEFAULT 1
    );
    GO

    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.

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

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

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

    MC Guest

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


    MC
     
    MC, Apr 28, 2007
    #9
  10. preet

    preet Guest

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

    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
    #10
  11. preet

    preet Guest

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


    the query used is

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


    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
    #11
  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:

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


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


    MC
     
    MC, May 4, 2007
    #12
  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,

    year(@start),

    datepart(qq,@start),

    datepart(mm,@start),

    datepart(dd,@start),

    datepart(dw,@start),

    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

     
    MC, May 4, 2007
    #13
  14. preet

    preet Guest

    I guess I have not been able to explain properly

    Let me try with a skeleton query


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


    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
    #14
  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:

    select
    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
    OpenPriceOnMonday,
    max(case when datename(dw,b.dt) = 'Friday' then b.dt else null end) as
    DateFriday,
    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
    from
    acc a
    inner join federalcalendar b on a.dt = b.dt
    where
    b.isweekday = 1
    group by
    b.week

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


    MC
     
    MC, May 5, 2007
    #15
  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
     
    MC, May 5, 2007
    #16
    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.