Thread: Grouping Data
07-24-15, 14:18 #1Registered User
- Join Date
- Jul 2015
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)
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
07-24-15, 14:45 #2Annie's Dog Walker
Provided Answers: 6
- Join Date
- Nov 2004
- on the wrong server
I would create new tables that maps your statuses to your stages like...
Create table stage(stageid int primary key clustered, stagename varchar(50)) Create table foo(OOStatus int, Stageid int)
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.
07-24-15, 15:55 #3Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
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 #stageIn theory, theory and practice are identical. In practice, theory and practice are unrelated.