#1
  1. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    27

    Query for two different counts


    The tasks table has a person name, open date, and close date. I'm trying to get the count of all records opened in the last 7 days and the count of records closed in the last 7 days in the same query as two separate columns, plus the name. Having trouble here. I've tried a few different ways. Any help?

    Thanks!
    Code:
    select c.respons, 
    		(select count(a.wo_num) as opened from tasks as a where a.respons = 'Matt Schutz' and a.opendate > getdate() - 7) as opened,
    		(select count(b.wo_num) as closed from tasks as b where b.respons = 'Matt Schutz' and b.clsddate > getdate() - 7) as closed
    from tasks as c
    where a.respons = b.respons and b.respons = c.respons;
    Discontent is the first necessity of progress. - Edison
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,517
    Rep Power
    543
    Try something like (just a sketch)
    sql Code:
    SELECT 
     COALESCE(openedcalls.respons, closedcalls.respons) AS respons,
     COALESCE(openedcalls.oc, 0) AS opened_calls,
     COALESCE(closedcalls.cc, 0) AS closed_calls
    FROM
     (
      SELECT
       COUNT(*) oc,
       calls.RESPONS
      FROM
       calls
      WHERE 
       opendate IS NOT NULL
      GROUP BY 
       calls.RESPONS
     ) openedcalls
    FULL OUTER JOIN
     (
      SELECT
       COUNT(*) cc,
       respons
      FROM
       CALLS
      WHERE 
       clsddate IS NOT NULL
      GROUP BY 
       respons
     ) closedcalls
    ON 
     openedcalls.respons = closedcalls.respons;
  4. #3
  5. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    27
    Beautiful! Ok, so now I want the net quantity (opened - closed) but I'm getting NULL in some cases. Hints?
    Code:
    SELECT 
     COALESCE(openedcalls.respons, closedcalls.respons, 'UNKNOWN') AS respons,
     COALESCE(openedcalls.oc, 0) AS opened_calls,
     COALESCE(closedcalls.cc, 0) AS closed_calls,
    openedcalls.oc - closedcalls.cc as net
    FROM
     (
      SELECT
       COUNT(*) oc,
       RESPONS
      FROM
       tasks
      WHERE 
       opendate > getdate() - 7
      GROUP BY 
       RESPONS
     ) openedcalls
    full OUTER JOIN
     (
      SELECT
       COUNT(*) cc,
       respons
      FROM
       tasks
      WHERE 
       clsddate > getdate() - 7
      GROUP BY 
       respons
     ) closedcalls
    ON 
     openedcalls.respons = closedcalls.respons;
    Code:
    UNKNOWN	0	1	NULL
    UNKNOWN	5	0	NULL
    Allison Boeckman	1	2	-1
    Andy Servais	2	0	NULL
    Brian Huettl	2	4	-2
    Chad Joubert	0	2	NULL
    Chris Deakin	1	0	NULL
    Discontent is the first necessity of progress. - Edison
  6. #4
  7. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,517
    Rep Power
    543
    When one of the operands is null (unknown) the result is null (unknown).
    You're missing the coalesce around the columns in your calculation.

    Infact you get nulls when one of the items is 0, which really is null, only coalesced to 0.

    Comments on this post

    • mateoc15 agrees : Thanks Pablo!
  8. #5
  9. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    27
    Originally Posted by pabloj
    When one of the operands is null (unknown) the result is null (unknown).
    You're missing the coalesce around the columns in your calculation.

    Infact you get nulls when one of the items is 0, which really is null, only coalesced to 0.
    PERRRRFECT.

    Thanks for the help and quick responses!
    Discontent is the first necessity of progress. - Edison

IMN logo majestic logo threadwatch logo seochat tools logo