TSQL- Rollup SQL 2005?

Try it like this SELECT a. Name, a. Age, a.

RecordDate, SUM(a. Weight - b. Weight) as WeightDiff FROM Details a JOIN Details be 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.

Thank you very much – Manjot Oct 21 '09 at 0:14.

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.

Msg 102, Level 15, State 1, Line 8 Incorrect syntax near ';'. Msg 319, Level 15, State 1, Line 9 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.

– Manjot Oct 20 '09 at 23:51 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 Oct 20 '09 at 23:54 Your query absolutely makes sense to me..... not sure why SQL is still complaining – Manjot Oct 20 '09 at 23:56 @RBarryYoung's solution worked. I still have no idea why SQl reported syntax error for your solution.

Thanks again – Manjot Oct 20 '09 at 0:15.

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

Did it but it was of no help – Manjot Oct 20 '09 at 23:30.

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

Actually, scratch that, @rexem answer is better for this purpose. Misunderstood the question. – Deeksy Oct 20 '09 at 23:50 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 Oct 20 '09 at 23:55.

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.

Error: Msg 102, Level 15, State 1, Line 5 Incorrect syntax near ','. Msg 319, Level 15, State 1, Line 6 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.

– Manjot Oct 20 '09 at 23:24 not as simple as that because the syntax is still wrong, and even fixing up by putting Details be on the subquery, the fourth column is invalid it's not in the group by – Deeksy Oct 20 '09 at 23:25.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.