Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2009
    Posts
    32

    Unanswered: SUM of two subqueries

    Table 'dbo.tblonenet_sdt_proj_hold' Schema as follows:

    cust_id int
    dateon DATETIME
    dateoff DATETIME

    There are two possibilites

    1) Its no longer on hold and so a dateon and dateoff value will be in the table.

    2) Its still on hold and so dateoff will be NULL.

    I want to add the total time its been on hold between the two dates and there could be multiple entires for cust_id 802

    SELECT Hon,Hoff FROM

    (SELECT sum(dbo.GetBusinessdays(dbo.tblonenet_sdt_proj_hol d.dateon,getdate()))
    FROM dbo.tblonenet_sdt_proj_hold
    WHERE cust_id='802' AND dateoff IS NULL) AS HON

    LEFT OUTER Join

    (SELECT sum(dbo.GetBusinessdays(dbo.tblonenet_sdt_proj_hol d.dateon,dbo.tblonenet_sdt_proj_hold.dateoff))
    FROM dbo.tblonenet_sdt_proj_hold
    WHERE cust_id='802' AND dateoff IS NOT NULL) AS HOFF
    ON cust_id=cust_id


    * dbo.GetBusinessdays FYI is a function that removes weekends.

    The resulting error in SQL Management Studio 2005 is 'No column was specified for column 1 of 'HON'.'

    Please help.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you are going to calculate a field in a referenced subquery, you need to give it a name.

    So, this calculated value CANNOT be referenced:
    Code:
    SELECT sum(dbo.GetBusinessdays(dbo.tblonenet_sdt_proj_hol d.dateon,getdate()))
    FROM dbo.tblonenet_sdt_proj_hold
    WHERE cust_id='802' AND dateoff IS NULL
    ...but this calculate value CAN be referenced:
    Code:
    SELECT sum(dbo.GetBusinessdays(dbo.tblonenet_sdt_proj_hol d.dateon,getdate())) as TotalBusinessDays
    FROM dbo.tblonenet_sdt_proj_hold
    WHERE cust_id='802' AND dateoff IS NULL
    ...and you'll have the same problem in your 2nd subquery.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...also, this will fail for several reasons:
    Code:
    ON cust_id=cust_id
    First, cust_id is not an included column in either of your subqueries.
    Second, the query engine will have no idea which cust_id you are referring to, since they are name the same and you did not specify exactly which dataset they belong to. So this will work, provided you include cust_id in each of your subqueries and include it in a GROUP BY clause:
    Code:
    ON HON.cust_id=HOFF.cust_id
    I think you need a refresher on basic SQL before you dive into queries as complicated as these....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jul 2009
    Posts
    32
    Thanks Blindman you are of cause absolutly correct with cust_id=cust_id not really sure what i was thinking its been a long day.

    I have amended the sql and it now looks like:
    SELECT Hon+Hoff FROM
    (SELECT sum(dbo.GetBusinessdays(dbo.tblonenet_sdt_proj_hol d.dateon,getdate())AS Hon)
    FROM dbo.tblonenet_sdt_proj_hold
    WHERE dbo.tblonenet_sdt_proj_hold.cust_id='802' AND dateoff IS NULL)
    LEFT OUTER Join
    (SELECT sum(dbo.GetBusinessdays(dbo.tblonenet_sdt_proj_hol d.dateon,dbo.tblonenet_sdt_proj_hold.dateoff) AS HOFF)
    FROM dbo.tblonenet_sdt_proj_hold
    WHERE dbo.tblonenet_sdt_proj_hold.cust_id='802' AND dateoff IS NOT NULL)
    ON HON.cust_id=HOFF.cust_id
    Returning the error ''sum' is not a recognized function name.' for each of the subqueries.

    I havent included dbo.tblonenet_sdt_proj_hold.cust_id in the SELECT of the subqueries as surely that would then effect the outcome of HOFF and HON... Is it not possible to include a column in your WHERE clause if its not returned in the SELECT query itself?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It has been a long day. You placed your alias inside your parenthesis. Count them:
    sum(dbo.GetBusinessdays(dbo.tblonenet_sdt_proj_hol d.dateon,getdate())AS Hon)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Write, debug, and execute each of these subqueries independently before attempting to join them.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jul 2009
    Posts
    32
    Just wanted to say thank you Blindman... your assistance has been invaluable in solving my issue.

    This was part of a larger SQL query which is now functioning perfectly and ticks all the boxes.

    Thanks again for your time and patience.

    Steve

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Though, I'm not sure if Microsoft SQL Server suppors nesting of funcions.
    If it supported nesting of funcions, the following query might return same results.
    Code:
    SELECT SUM(
              dbo.GetBusinessdays(
                 dateon
               , COALESCE( dateoff , getdate() )
              )
           ) AS total_time
     FROM  dbo.tblonenet_sdt_proj_hold AS t
     WHERE cust_id = '802'
    ;

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That syntax is supported. The OP will need to verify that it duplicates his required business logic.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •