SQL syntax/structure

timmoser · #1 (**permalink**) 11-21-03, 16:23

I have a table that looks like this:

Code:

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:

Code:

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.
Thanks

r937 · #2 (**permalink**) 11-21-03, 16:51

since the number of columns depends on the number of weeks in the range, your query will end up looking like this:

Code:

select z.acttype
     , sum( case when week_date = '2003-08-01'
            then sunday+monday+tuesday+wednesday
                    +thursday+friday+saturday
            else 0 end ) as week1        
     , sum( case when week_date = '2003-08-08'
            then sunday+monday+tuesday+wednesday
                    +thursday+friday+saturday
            else 0 end ) as week2    
  from it_tt_activity z
 where z.uid=@uid 
   and z.week_date>=@fromdate 
   and z.week_date<=@todate
group 
    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

rudy
http://r937.com/

timmoser · #3 (**permalink**) 11-21-03, 19:33

Dig you! Thanks

Internet Services

Web Development

Digital Marketing

Consumer Tech