August 29, 2015 at 12:14 pm
I have a field in a table which was VERY poorly designed, but that is a matter for another day. Long story short, this field contains, in most instances, where the sale was obtained, the sales man name, and a comment about the sale. A few records have garbage data in the field as the salesman name was not obtained so we want to attribute the info to 'unknown'
Is it possible in SQL Server 2008 to write a query that will display the saleinfo for each salesman then a total row under the salesman? Something similar to this
Internet Mark .... statistics here
Phone Mark - applied for credit .... statistics here
Phone Mark - customer referral ..... statistics here
Marks Sales Totals .... statistics here
Next salesman data would go here
but break that down by each salesman and attribute the garbage data like 85623, albaca, racava to salesman 'Unknown'
This is garbage data that should suffice to achieve my desired end result. I know this is unable to occur with a simple select. I even tried a few CTE queries but couldn't get the syntax accurate due to saleinfo basically being a catch all column I was unsure of how to only extrapolate the data I needed
Create Table sales
(
saleid int
,saleinfo varchar(200)
,salestatus varchar(200)
)
Insert Into sales VALUES
(1, 'Phone Mark customer referral', 'Done'), (2, 'Phone Mark customer referral ', 'Done'), (3, 'Phone Mark - applied for credit', 'Holding Pattern')
,(4, 'Internet Mark', 'Done'), (5, 'Internet Mark', 'Holding Pattern'), (6, 'Internet Mark', 'Holding Pattern')
,(7, 'Phone Stan', 'Holding Pattern'), (8, 'Phone Stan', 'Done'), (9, 'Phone Stan', 'Holding Pattern')
,(10, 'Internet Stan - lives to far to drive', 'Done'), (11, 'Internet Stan', 'Done'), (12, 'Phone Vic', 'Done')
,(13, 'Phone Vic', 'Holding Pattern'), (14, '85623', 'Done'), (15, 'albaca', 'Done'), (16, 'racava', 'Done')
August 29, 2015 at 8:54 pm
JoseMenendez (8/29/2015)
I have a field in a table which was VERY poorly designed, but that is a matter for another day. Long story short, this field contains, in most instances, where the sale was obtained, the sales man name, and a comment about the sale. A few records have garbage data in the field as the salesman name was not obtained so we want to attribute the info to 'unknown'Is it possible in SQL Server 2008 to write a query that will display the saleinfo for each salesman then a total row under the salesman? Something similar to this
Internet Mark .... statistics here
Phone Mark - applied for credit .... statistics here
Phone Mark - customer referral ..... statistics here
Marks Sales Totals .... statistics here
Next salesman data would go here
but break that down by each salesman and attribute the garbage data like 85623, albaca, racava to salesman 'Unknown'
This is garbage data that should suffice to achieve my desired end result. I know this is unable to occur with a simple select. I even tried a few CTE queries but couldn't get the syntax accurate due to saleinfo basically being a catch all column I was unsure of how to only extrapolate the data I needed
Create Table sales
(
saleid int
,saleinfo varchar(200)
,salestatus varchar(200)
)
Insert Into sales VALUES
(1, 'Phone Mark customer referral', 'Done'), (2, 'Phone Mark customer referral ', 'Done'), (3, 'Phone Mark - applied for credit', 'Holding Pattern')
,(4, 'Internet Mark', 'Done'), (5, 'Internet Mark', 'Holding Pattern'), (6, 'Internet Mark', 'Holding Pattern')
,(7, 'Phone Stan', 'Holding Pattern'), (8, 'Phone Stan', 'Done'), (9, 'Phone Stan', 'Holding Pattern')
,(10, 'Internet Stan - lives to far to drive', 'Done'), (11, 'Internet Stan', 'Done'), (12, 'Phone Vic', 'Done')
,(13, 'Phone Vic', 'Holding Pattern'), (14, '85623', 'Done'), (15, 'albaca', 'Done'), (16, 'racava', 'Done')
Jose,
Based upon the sample data that you provided here, what exactly should the specified results be? The "statistics here" is too vague... and I seem to have trouble reading other peoples minds without my trust crystal ball, which has gone missing.
Is the "salesman" always going to be the second word in this string?
If I'm understanding what you're looking for correctly, does this work for you? This does make assumptions about what you're asking for, but it should be close.
DECLARE @sales TABLE
(
saleid int
,saleinfo varchar(200)
,salestatus varchar(200)
)
Insert Into @sales VALUES
(1, 'Phone Mark customer referral', 'Done'), (2, 'Phone Mark customer referral ', 'Done'), (3, 'Phone Mark - applied for credit', 'Holding Pattern')
,(4, 'Internet Mark', 'Done'), (5, 'Internet Mark', 'Holding Pattern'), (6, 'Internet Mark', 'Holding Pattern')
,(7, 'Phone Stan', 'Holding Pattern'), (8, 'Phone Stan', 'Done'), (9, 'Phone Stan', 'Holding Pattern')
,(10, 'Internet Stan - lives to far to drive', 'Done'), (11, 'Internet Stan', 'Done'), (12, 'Phone Vic', 'Done')
,(13, 'Phone Vic', 'Holding Pattern'), (14, '85623', 'Done'), (15, 'albaca', 'Done'), (16, 'racava', 'Done');
WITH cte AS
(
SELECT t1.*,
CASE WHEN ca1.Space1 > 0
THEN SUBSTRING(t1.saleinfo, ca1.Space1+1, ISNULL(NULLIF(ca2.Space2, 0)-1, DATALENGTH(t1.saleinfo)) - ca1.Space1)
ELSE 'Unknown'
END AS SalesPerson
FROM @sales t1
CROSS APPLY (SELECT CHARINDEX(' ', t1.saleinfo)) ca1(Space1) -- get the first space
CROSS APPLY (SELECT CHARINDEX(' ', t1.saleinfo, ca1.Space1+1)) ca2(Space2) -- get the second space
)
SELECT --SalesPerson,
CASE WHEN GROUPING(saleinfo) = 1 THEN SalesPerson + ' Sales Total' ELSE saleinfo END AS saleinfo,
COUNT(*) AS SaleCounter
FROM cte
GROUP BY GROUPING SETS (SalesPerson, saleinfo), (SalesPerson)
ORDER BY SalesPerson, GROUPING(saleinfo), saleinfo;
This returns the following result set:
saleinfo SaleCounter
------------------------------------- -----------
Internet Mark 3
Phone Mark - applied for credit 1
Phone Mark customer referral 2
Mark Sales Total 6
Internet Stan 1
Internet Stan - lives to far to drive 1
Phone Stan 3
Stan Sales Total 5
85623 1
albaca 1
racava 1
Unknown Sales Total 3
Phone Vic 2
Vic Sales Total 2
See the book link in my signature for how GROUPING SETS works.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 29, 2015 at 8:57 pm
duplicate post
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 29, 2015 at 10:19 pm
The name can be, but will not always be the 2nd word in the string. It will be 1 of 8 names, and if none of those names are found in the string it should be unknown. Does that help make it a little clearer?
August 30, 2015 at 3:23 pm
JoseMenendez (8/29/2015)
The name can be, but will not always be the 2nd word in the string. It will be 1 of 8 names, and if none of those names are found in the string it should be unknown. Does that help make it a little clearer?
Yes, that makes it more clear but whatever is creating the data knows what's happening and who did it. It would be a thousand times better if the action and the name were created in separate columns by whatever is logging the data... especially since there's likely going to be more than one Mark in the world that could join your company in the future.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 30, 2015 at 6:31 pm
Jeff Moden (8/30/2015)
JoseMenendez (8/29/2015)
The name can be, but will not always be the 2nd word in the string. It will be 1 of 8 names, and if none of those names are found in the string it should be unknown. Does that help make it a little clearer?Yes, that makes it more clear but whatever is creating the data knows what's happening and who did it. It would be a thousand times better if the action and the name were created in separate columns by whatever is logging the data... especially since there's likely going to be more than one Mark in the world that could join your company in the future.
Agreed, just looking for a "pretty" way to display the data until information is able to be back-filled, as the front-end application has already been updated to prevent this from occurring. Now the data just needs to be updated to mirror.
August 31, 2015 at 10:36 am
JoseMenendez (8/29/2015)
The name can be, but will not always be the 2nd word in the string. It will be 1 of 8 names, and if none of those names are found in the string it should be unknown. Does that help make it a little clearer?
Based upon this, here is a modified query. It puts all of the sales persons names into a separate table, and then looks for that name in the saleinfo column. This query still has the same output that I posted earlier.
SET NOCOUNT ON;
DECLARE @sales TABLE
(
saleid int
,saleinfo varchar(200)
,salestatus varchar(200)
)
Insert Into @sales VALUES
(1, 'Phone Mark customer referral', 'Done'), (2, 'Phone Mark customer referral ', 'Done'), (3, 'Phone Mark - applied for credit', 'Holding Pattern')
,(4, 'Internet Mark', 'Done'), (5, 'Internet Mark', 'Holding Pattern'), (6, 'Internet Mark', 'Holding Pattern')
,(7, 'Phone Stan', 'Holding Pattern'), (8, 'Phone Stan', 'Done'), (9, 'Phone Stan', 'Holding Pattern')
,(10, 'Internet Stan - lives to far to drive', 'Done'), (11, 'Internet Stan', 'Done'), (12, 'Phone Vic', 'Done')
,(13, 'Phone Vic', 'Holding Pattern'), (14, '85623', 'Done'), (15, 'albaca', 'Done'), (16, 'racava', 'Done');
DECLARE @SalesNames TABLE (SalesPerson VARCHAR(50));
INSERT INTO @SalesNames (SalesPerson)
VALUES ('Mark'), ('Stan'), ('Vic');
WITH cte AS
(
SELECT t1.*,
ISNULL(t2.SalesPerson, 'Unknown') AS SalesPerson
FROM @sales t1
LEFT JOIN @SalesNames t2 ON t1.saleinfo LIKE '%' + t2.SalesPerson + '%'
)
SELECT --SalesPerson,
CASE WHEN GROUPING(saleinfo) = 1 THEN SalesPerson + ' Sales Total' ELSE saleinfo END AS saleinfo,
COUNT(*) AS SaleCounter
FROM cte
GROUP BY GROUPING SETS (SalesPerson, saleinfo), (SalesPerson)
ORDER BY SalesPerson, GROUPING(saleinfo), saleinfo;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply