TSQL- Rollup SQL 2005

I have the following example code:

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

--insert data

..query:

  • What is the best database structure In this scenario?
  • Validate DateTime String in SQL Server 2005
  • Reliability of Indexed views
  • How to retrieve upload/current time in each batch file uploaded in sql server
  • SQL Server Migration Assistance created tables with “dbo.”
  • Can LINQ-to-SQL omit unspecified columns on insert so a database default value is used?
  • 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?

    5 Solutions collect form web for “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 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 
    

    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
    

    Don’t use AS keyword. You can just directly write {(select * from blah) 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.

    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 
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.