Results 1 to 3 of 3

Thread: Grouping Data

  1. #1
    Join Date
    Jul 2015
    Posts
    1

    Unanswered: Grouping Data

    I'm VERY new to SQL - started learning yesterday when I was tasked with learning BIRT. So I get a 2 for 1 - learn BIRT and SQL.

    I'm pulling data out of our Sales db (ContactWise) to calculate Conversion rates. To do that I need to put the different Statuses a job can be under into "stages" to make it easier. Right now we have 32 different Statuses - way too many, I know. For example we have 4 different Proposal stages - Proposal-Hot, Proposal-Cold, Proposal-Long Process, Proposal-New.

    I would like to be able to group the Statuses into 5 or so stages - Lead, Prospect, Proposal, Contract, Sold.

    This is the statement I have. If someone could help me group the Proposals together, I think I could figure out how to get the other groups.
    (The Status_Changed is a date/time field - I'd love to also know how to strip it down to just 01/01/2012, instead of January 01, 2012 12:00 AM)

    Thanks!
    Sarah

    Code:
    SELECT CW_OPPORTUNITIES.OO_STATUS, 
           CW_OPP_PROPERTY_24.OPV_DATA, --This is Sales_Person
           CW_CONTACT.CO_DISPLAYNAME, 
           CW_OPPORTUNITIES.OO_STATUS_CHANGED
    FROM CW_OPPORTUNITIES CW_OPPORTUNITIES
         , CW_OPP_PROPERTY_24 CW_OPP_PROPERTY_24
         , CW_OPPORTUNITY_STATUS CW_OPPORTUNITY_STATUS
         , CW_CONTACT CW_CONTACT
    WHERE 
          (CW_OPP_PROPERTY_24.OPV_OO_KEY = CW_OPPORTUNITIES.OO_KEY)
           AND 
          (CW_OPPORTUNITY_STATUS.OS_KEY = CW_OPPORTUNITIES.OO_OS_KEY)
           AND 
          (CW_CONTACT.CO_KEY = CW_OPPORTUNITIES.OO_NA_KEY)
          )
    ORDER BY CW_OPP_PROPERTY_24.OPV_DATA, 
             CW_OPPORTUNITIES.OO_STATUS, 
             CW_CONTACT.CO_DISPLAYNAME

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I would create new tables that maps your statuses to your stages like...

    Code:
    Create table stage(stageid int primary key clustered, stagename varchar(50))
    
    Create table foo(OOStatus int, Stageid int)
    And then I would join to these tables.

    Good luck and welcome to the club.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The following code snippet builds on what Thrasymachus suggested but it gives you something a bit more concrete to work/play with as you try to figure this out.
    Code:
    CREATE TABLE #stage
       (stage       CHAR(1)     NOT NULL
       PRIMARY KEY (stage)
    ,  description  VARCHAR(20) NOT NULL
    )
    
    INSERT INTO #stage (stage, description)
       VALUES
       ('1', 'One'),   ('2', 'Two')
    ,  ('3', 'Three'), ('4', 'Four')
    ,  ('5', 'Five'),  ('6', 'Six')
    
    CREATE TABLE #code2stage
    (  code         CHAR(1)     NOT NULL
       PRIMARY KEY (code)
    ,  stage        CHAR(1)     NOT NULL
       FOREIGN KEY (stage) REFERENCES #stage (stage)
    )
    
    INSERT INTO #code2stage (code, stage)
       VALUES
          ('A', '1'), ('B', '1'), ('C', '1'), ('D', '1'), ('E', '1')
    ,     ('F', '2'), ('G', '2'), ('H', '2'), ('I', '2'), ('J', '2')
    ,     ('K', '3'), ('L', '3'), ('M', '3'), ('N', '3'), ('O', '3')
    ,     ('P', '4'), ('Q', '4'), ('R', '4'), ('S', '4'), ('T', '4')
    ,     ('U', '5'), ('V', '5'), ('W', '5'), ('X', '5'), ('Y', '5')
    ,     ('Z', '6')
    
    CREATE TABLE #stuff
    (  number       INT         NOT NULL
    ,  code         CHAR(1)		NOT NULL
       FOREIGN KEY (code) REFERENCES #code2stage (code)
    )
    
    INSERT INTO #stuff (number, code)
       SELECT z.number, Char(65 + z.number % 26)
          FROM master.dbo.spt_values AS z
    	  WHERE  'P' = z.type
    
    SELECT Count(*), s.stage, s.description
       FROM #stuff AS a
       JOIN #code2stage AS c
          ON (c.code = a.code)
       JOIN #stage AS s
          ON (s.stage = c.stage)
       GROUP BY s.stage, s.description
       ORDER BY s.stage, s.description
    
    SELECT *
       FROM #stuff
    
    DROP TABLE #stuff
    DROP TABLE #code2stage
    DROP TABLE #stage
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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