views:

215

answers:

5

I have the following example code:

create table Details(
  name varchar(20),
  age int,
  weight int,
  recordDate Datetime)

--insert data

..query:

SELECT a.name,
       a.age,
       a.recordDate,
       a.weight - (SELECT b.weight
                     FROM Details
                    WHERE b.recordDate = dateadd(dd, -1, a.recordDate) as subtable)        
  FROM Details a
GROUP BY WITH ROLLUP (a.recordDate, a.name, a.age)

I want to see the weight difference between RecordDates for each person and then record total weight different for that person and also for the age group and then grand weight gain/loss. This is not my actual table but just an example.

Problem: It was complaining about subquery - then I had to use it as table variable: subtable.

Now it is complaining:

Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'as'.
Msg 319, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

What am I missing?

A: 
SELECT a.name,a.age,a.recordDate,a.weight-(SELECT b.weight
                                           FROM Details
                                           WHERE b.recordDate=dateadd(dd,-1,a.recordDate))
FROM Details a
GROUP BY (a.recordDate,a.name,a.age)
WITH ROLLUP
tster
Error:Msg 102, Level 15, State 1, Line 5Incorrect syntax near ','.Msg 319, Level 15, State 1, Line 6Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Manjot
not as simple as that because the syntax is still wrong, and even fixing up by putting Details b on the subquery, the fourth column is invalid it's not in the group by
Deeksy
+1  A: 

Don't use AS keyword. You can just directly write {(select * from blah) a}

Zinx
Did it but it was of no help
Manjot
A: 

OK, so the problem is that WITH ROLLUP isn't really the answer you're looking for. This is for creating subtotals not running totals which is what you're after, so using it will give you the total for different combinations of dates rather than a running total, which is what you're after. In the beginning, the query that you want to just get a total that gives you name, age, date and weight loss compared to yesterday is as follows:

select
      a.name
     ,a.age
     ,a.recordDate
     ,(SELECT b.weight from Details b WHERE b.recordDate = dateadd(dd,-1,a.recordDate)) - a.weight as weightLossForToday
   from details a

Keep in mind that this query will only work if you have exactly 1 record every day. If you have 2 records for a single day or the entries aren't exactly 1 day apart (ie. they include time), then it won't work. In order to get a running total working, you'll need to follow the approach from a question like this.

That should do the trick.

Deeksy
Actually, scratch that, @rexem answer is better for this purpose. Misunderstood the question.
Deeksy
thanks.I was lookign for ROLLUP becuase I wanted to see weight loss between consecutive days and also over a period for an individual, over a period for an age group and then grand Weight loss.Not sure how I can change your query for that
Manjot
+2  A: 

Typo:

a.weight - (SELECT b.weight
              FROM Details
             WHERE b.recordDate = dateadd(dd, -1, a.recordDate)

..."b" is being used as a table alias, but it's not actually defined as one.

Next issue is that your GROUP BY doesn't include a.weight, and there's no aggregate function associated with it. Here's my re-write of your query:

  SELECT a.name,
         a.age,
         a.recordDate,
         SUM(a.weight - t.weight) 'weight'
    FROM DETAILS a
    JOIN (SELECT b.recordDate,
                 b.weight
            FROM DETAILS b) t ON t.recordDate = DATEADD(dd, -1, a.recordDate)
GROUP BY (a.recordDate, a.name, a.age) WITH ROLLUP
OMG Ponies
Msg 102, Level 15, State 1, Line 8Incorrect syntax near ';'.Msg 319, Level 15, State 1, Line 9Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Manjot
I can't debug what I can't see - all I can tell from that is that you are using a CTE, and the problem regards a ";".
OMG Ponies
Your query absolutely makes sense to me..... not sure why SQL is still complaining
Manjot
@RBarryYoung's solution worked. I still have no idea why SQl reported syntax error for your solution.Thanks again
Manjot
+1  A: 

Try it like this

SELECT
 a.name,
 a.age,
 a.recordDate,
 SUM(a.weight - b.weight) as WeightDiff
FROM Details a
JOIN Details b 
 ON (b.age        = a.age
 AND b.name       = a.name
 AND b.recordDate = dateadd(dd, -1, a.recordDate)
  )
GROUP BY a.age, a.name, a.recordDate WITH ROLLUP
RBarryYoung
Thank you very much
Manjot