Post reply

compute results

  • April 6, 2006 at 2:12 am

    #168909

    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

     

  • April 6, 2006 at 2:59 am

    #631153

    Hi,

    Can you provide some more explination

  • April 6, 2006 at 5:27 am

    #631173

    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

  • April 6, 2006 at 5:27 am

    #631172

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

  • April 8, 2006 at 6:04 am

    #631600

    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

  • April 8, 2006 at 9:45 am

    #631609

    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)
    Intro to Tally Tables and Functions

  • April 10, 2006 at 12:50 am

    #631660

    Hi,

    Yes, Jeff solution is the best for your problem

    Daniela

  • April 11, 2006 at 1:08 am

    #631855

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

    Best regards

    Zia Khan

  • April 11, 2006 at 2:19 am

    #631860

    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