Professional Web Applications Themes

Help creating a Stored Proc which turns data from one row per day to one row per week - Microsoft SQL / MS SQL Server

Hi. I'm trying to create a stored proc which uses a table containing a breakdown of activities listed by date, time, user, Sum(Activity Type 1), sum(Activity Type 2), sum(Activity type 3) to create a summary by week in the form of: UserName, Mon_ActType1, Mon_ActType2, Mon_ActType3, Tue_ActType1, Tue_ActType2, ...etc (more detailed summary of input and output below) I know i can do this with an abundance of nested selects and 'do..while' loops in ASP, however this is an unacceptable approach and i feel that a stored proc would be a more appropriate solution. Q1: Am i correct in my thought on ...

  1. #1

    Default Help creating a Stored Proc which turns data from one row per day to one row per week

    Hi. I'm trying to create a stored proc which uses a table containing a
    breakdown of activities listed by date, time, user, Sum(Activity Type 1),
    sum(Activity Type 2), sum(Activity type 3) to create a summary by week in
    the form of:

    UserName, Mon_ActType1, Mon_ActType2, Mon_ActType3, Tue_ActType1,
    Tue_ActType2, ...etc

    (more detailed summary of input and output below)

    I know i can do this with an abundance of nested selects and 'do..while'
    loops in ASP, however this is an unacceptable approach and i feel that a
    stored proc would be a more appropriate solution.

    Q1: Am i correct in my thought on using a stored proc?
    Q2: Can anyone show me how i might approach this in a stored proc? - my
    thought is to produce an array, but dont know t-SQL well enough to create
    it.



    Source Table: vActivities (there is one row per Day)
    ProfID (int)
    UsrName (Varchar)
    ActDate (datetime)
    ActType1 (int)
    ActType1 (int)
    ActType1 (int)


    Required Output: by inputting a 'week begin date' the following should be
    produced:

    UserName, Mon_ActType1, Mon_ActType2, Mon_ActType3, Tue_ActType1,
    Tue_ActType2,



    Any help would be appreciated.

    Thanks

    Kevin


    kevin Guest

  2. #2

    Default Re: Help creating a Stored Proc which turns data from one row per day to one row per week

    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    Here is a totally unsupported guess:

    SELECT username,
    SUM(CASE WHEN act_type =1
    AND DATEPART (DW, act_date) = 2 -- Monday
    THEN score
    ELSE 0 END) AS mon_act_type1,
    SUM(CASE WHEN act_type =2
    AND DATEPART (DW, act_date) = 2 -- Monday
    THEN score
    ELSE 0 END) AS mon_act_type1,
    ..
    FROM Activities
    WHERE act_date BETWEEN start_of_week
    AND DATEADD (DD, 6, start_of_week)

    GROUP BY username;

    --CELKO--


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

Similar Threads

  1. cfc and stored proc
    By mcoop in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: April 13th, 05:09 PM
  2. Very slow stored proc
    By Együd Csaba in forum PostgreSQL / PGSQL
    Replies: 6
    Last Post: December 23rd, 11:35 PM
  3. Stored Proc problem
    By mitaka in forum ASP Database
    Replies: 1
    Last Post: May 3rd, 05:11 PM
  4. ASP vs Stored Proc vs UDF
    By Brad in forum ASP Database
    Replies: 11
    Last Post: October 28th, 01:46 AM
  5. stored proc and tcp/ip
    By Helmut Wöss in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: June 30th, 08:13 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139