compute results

  • Hi All,

    Is there any way to declare variable for a computed results set or we can insert the results into temp table.

    Thanks in advance

    Zia

     

  • Hi,

    Can you provide some more explination

  • I hope I understood correctly: you can make the calculs and insert the result set into a temporary table using insert as for a usual table. Please write more about the problem


    Daniela

  • Do you need to temporarily keep a dataset came by a query?

    You can do this, using a temporary table:

    CREATE TABLE #tempTable ( Value INT, i INT identity(1,1) )

    USE myDB

    GO

    INSERT INTO #tempTable (Value)

    SELECT (price*1.1) + 3

    FROM Items

    WHERE price IS NOT NULL and type = 'XXXX'

    ORDER BY price ASC

    ------------
    When you 've got a hammer, everything starts to look like a nail...

  • Hi Daniela,

    I have this query in which I am getting 2 results sets the second result is the compute values for the select statment.And my objective is to create a web job to display the both results in html but for some reason its showing me the results of select statments that way I ended up to use the #temptable

     select distinct

     operator Name,

     convert(varchar(8),starttime,11) Date,

     sum(KeyStrokes) Keys,

     sum(ActKeyStrokes) ActKeys,

     sum(NumOfLines) Lines,

     sum(ActNumofLines) ActLines,

     sum(actkeystrokes)/sum(actnumoflines) Keys_Line,

     cast(((cast(sum(actkeystrokes) as real))/(cast(sum(keystrokes) as real))*100) as int) Accuracy

    from

     datacapstats

    where

     convert(varchar(8),starttime,11) > convert(varchar(8),getdate()-14,11)

     and

     convert(varchar(8),starttime,11) < convert(varchar(10),getdate(),11)

    group by

     operator,convert(varchar(8),starttime,11)

    order by

     operator,convert(varchar(8),starttime,11)

    compute

     avg(Sum(keystrokes)),

     avg(sum(actkeystrokes)),

     avg(cast(((cast(sum(actkeystrokes) as real))/(cast(sum(keystrokes) as real))*100) as int))

     by

     operator

    If there is any way to make the both results set in html or what would be the nice way to solve this prob.......

    Thanks

    Zia

  • Zia,

    Instead of using COMPUTE, consider using WITH ROLLUP or WITH CUBE.  There's some great documentation on how to exploit the related GROUPING sub-totals for ROLLUP and CUBE in Books OnLine under the documentation for WITH CUBE.  That will allow the SELECT (and your compute formulas) to be returned as a single result set.  And, no, these do not require OLAP.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi,

    Yes, Jeff solution is the best for your problem


    Daniela

  • Thanks guys I have done that by using temp table ...

    Best regards

    Zia Khan

  • You really don't need a temptable for this. While it may work now, it's unnecessary much extra work for the server, and there is a slight chance that it may not work so well in the future. Please do have a look at the suggested WITH ROLLUP or WITH CUBE options before settling with a temptable solution. If they look strange to you, don't be afraid to ask.

    /Kenneth

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply