Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003

    Unanswered: SQL syntax/structure

    I have a table that looks like this:
    CREATE TABLE dbo.it_tt_activity 
        rowid       numeric(10,0) IDENTITY,
        uid         char(10)      NOT NULL,
        acttype     char(25)      NULL,
        activity    char(100)     NOT NULL,
        description char(100)     NOT NULL,
        entry_date  smalldatetime NOT NULL,
        week_date   smalldatetime NOT NULL,
        sunday      numeric(9,1)  NOT NULL,
        monday      numeric(9,1)  NOT NULL,
        tuesday     numeric(9,1)  NOT NULL,
        wednesday   numeric(9,1)  NOT NULL,
        thursday    numeric(9,1)  NOT NULL,
        friday      numeric(9,1)  NOT NULL,
        saturday    numeric(9,1)  NOT NULL 
    What I need to do is display the data for each user like so...
    uid acttype activity description week1, week2, week3, week4, week5...

    The week_date is the date for Sunday each week. The week1, week2... fields are sums for an acttype, activity and description for each week.

    This is what I have so far:
    DECLARE @uid VARCHAR(10)
    DECLARE @fromdate smalldatetime
    DECLARE @todate smalldatetime
    SELECT @uid='23061'
    SELECT @fromdate='08/01/2003'
    SELECT @todate='10/31/2003'
    SELECT z.acttype,
            week1=ISNULL((SELECT SUM(a.sunday+a.monday+a.tuesday+a.wednesday+a.thursday+a.friday+a.saturday)
             FROM it_tt_activity a 
             WHERE a.week_date=z.week_date
                     AND a.acttype=z.acttype                                 
                     AND a.uid=z.uid),0)
      FROM it_tt_activity z
      WHERE z.uid=@uid AND z.week_date>=@fromdate AND z.week_date<=@todate
      GROUP BY z.acttype
    This is not even giving me the proper answer for one uid in one week. I am getting a Cartesian result.

    Please let me know what you think.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    since the number of columns depends on the number of weeks in the range, your query will end up looking like this:
    select z.acttype
         , sum( case when week_date = '2003-08-01'
                then sunday+monday+tuesday+wednesday
                else 0 end ) as week1        
         , sum( case when week_date = '2003-08-08'
                then sunday+monday+tuesday+wednesday
                else 0 end ) as week2    
      from it_tt_activity z
     where z.uid=@uid 
       and z.week_date>=@fromdate 
       and z.week_date<=@todate
        by z.acttype
    now the challenge is simply to take the date range, generate the correct number of weekly expressions, and execute the resulting sql


  3. #3
    Join Date
    Jan 2003
    Dig you! Thanks

Posting Permissions

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