Forum Replies Created

Viewing 15 posts - 3,571 through 3,585 (of 4,085 total)

  • RE: Adding either a "1" or "2" in a column (using Select, Union All, and three tables)

    TDSapp (10/6/2011)

    CELKO (10/5/2011)

    What I would really like to do is return a table that has a customer column, and then one column for each status. The status column would...

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

    October 6, 2011 at 11:34 am

    #1391349

  • RE: Extension of Cross-Tabs, with MAX, MIN

    ulteriorm (10/6/2011)

    O..o It would have been incredibly simple if i had to return just the min and max on year.:-P

    I think it IS that simple. Your expected results...

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

    October 6, 2011 at 10:10 am

    #1391297

  • RE: parse XML file

    sqlfriends (10/6/2011)

    It looks like we should use below from your previous post, will this improve performance

    select ids.id.value('@id', 'int') as id

    from @xml.nodes('/root/r') ids(id)

    XML is case sensitive, so that should be

    select...

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

    October 6, 2011 at 9:26 am

    #1391268

  • RE: Calculating Incorrect Times from Two Tables without RBAR

    kramaswamy (10/5/2011)

    Row 1: From 8:00AM to 9:55 AM. Was supposed to work from 8:00AM to 9:45 AM -- 10 minutes

    Row 2: From 10:11 AM to 11:28 AM. Was supposed to...

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

    October 6, 2011 at 8:49 am

    #1391237

  • RE: Filter returned records based on MAX/Last date found

    You don't need to join on Table_1 for this particular query, but that could just be an artifact of the simplified data.

    In any case, you might want to group your...

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

    October 6, 2011 at 7:54 am

    #1391203

  • RE: query question

    Actually, if you want to return anything other than the amount--which you probably do, since you asked for details--you should look at the Row_Number() function.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

    October 5, 2011 at 2:50 pm

    #1390965

  • RE: Finding "Max" Value from subquery

    I did look at using an APPLY rather than a subquery, but the execution plans for both were essentially the same. The only difference I found was that the...

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

    October 5, 2011 at 11:23 am

    #1390851

  • RE: need help

    The standard way is something like the following:

    SELECT DISTINCT [State]

    , Stuff(

    (SELECT ', ' + City

    FROM Table1 AS t2

    WHERE t1.[State] = t2.[State]

    ORDER BY City

    FOR XML PATH('')

    )

    , 1 -- start position

    ,...

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

    October 5, 2011 at 9:54 am

    #1390786

  • RE: Finding "Max" Value from subquery

    For performance issues, it really helps to have the actual and/or estimated execution plans.

    One thing you can try on your own is turning on XML statistics before the rest of...

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

    October 4, 2011 at 3:36 pm

    #1390429

  • RE: Help Resolving This: Msg 130, Level 15, State 1, Line 3 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

    Sean Lange (10/4/2011)

    Yes like mentioned you cannot perform multiple aggregations on a single column. You can however split this into two pieces in a subquery and perform your calculation outside....

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

    October 4, 2011 at 1:14 pm

    #1390332

  • RE: Help Resolving This: Msg 130, Level 15, State 1, Line 3 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

    Using the distributive properties of multiplication over addition, your formula

    sum(cast(l.labor as decimal(18,2)) * count(distinct d.sn) / 1000 * 23.37) as appLabor

    is equivalent to the following formula

    sum(cast(l.labor as decimal(18,2))) * count(distinct...

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

    October 4, 2011 at 12:51 pm

    #1390311

  • RE: Trying to avoid RBAR

    Kenneth Fisher-475792 (10/3/2011)

    Actually no, in this case a reporting tool won't work. We get the occasional spreadsheet sent to us by upper management with the task of "filling in...

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

    October 3, 2011 at 1:32 pm

    #1389777

  • RE: Comparing columns in two tables for unequal values

    mapperhd (10/2/2011)

    Thanks for the replies.

    However, I need to keep only the columns that are not equal.

    The problem with this is that it forces you to violate the first normal form...

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

    October 3, 2011 at 1:07 pm

    #1389763

  • RE: Trying to avoid RBAR

    Kenneth Fisher-475792 (10/3/2011)

    drew.allen (9/30/2011)

    Kenneth Fisher-475792 (9/30/2011)

    I'm looking for a query to take the following table

    This is a report and you should be using a reporting tool to generate it. ...

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

    October 3, 2011 at 9:45 am

    #1389624

  • RE: How to find the nth record based on specific condition

    Prasanthi Reddy (10/2/2011)

    Hi,

    I have a table which contains on and off status of a meter. Feederstatus = 0 means On and feederstatus=1 means Off.

    You do realize that this is exactly...

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

    October 3, 2011 at 8:33 am

    #1389564

Viewing 15 posts - 3,571 through 3,585 (of 4,085 total)