3 part question (substr) (nps) and partition by

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

3 part question (substr) (nps) and partition by

Author

Topic

sqlbrahzilla
Starting Member

7 Posts

Posted - 2015-03-19 : 15:39:06

So to start, can someone explain how the Substr function works.

For example let's say I have a field named Duns. In this field there are 6 characters, and I want to filter based upon the first 4 of those 6 digits. And then filter on 2 digits, etc.

Is it :

SUBSTR(fieldname, 4) ? I can't find this anywhere online.

Second, I'd like to calculate the net promoter score within SQL

I'm using the following syntax but I want to do a sanity check and make sure I'm doing it right. see below:

(case
when a.net_promoter__c in (9, 10) then 1
when a.net_promoter__c in (7, 8) then 0
when a.net_promoter__c in (0, 1, 2, 3, 4, 5, 6) then -1
end) as net_promoter_score

FYI, calculating the NPS is using the following criteria:

Promoters (score 9-10) are loyal enthusiasts who will keep buying and refer others, fueling growth.
Passives (score 7-8) are satisfied but unenthusiastic customers who are vulnerable to competitive offerings.
Detractors (score 0-6) are unhappy customers who can damage your brand and impede growth through negative word-of-mouth.
To calculate your company’s Net Promoter Score (NPS®), take the percentage of customers who are Promoters and subtract the percentage who are Detractors.

LASTLY, I'm hoping to break apart the number of closed deals, by sales reps, by closed date (the date the deal was closed).

My initial thoughts were to do the following, but again, I'm pretty sure I'm off. Thoughts?

SELECT
salesrepfield
,COUNT(d.number_opportunities_won__c) OVER (PARTITION BY b.closedate) AS RepC

Not worried about the join structure or where statements as they are good to go. Just not sure how to select them or use the partition function correctly.

I'm hoping the guru's here can take me to school

Edit: Last question (I know I'm asking a ton).

Does this actually filter out null fields, or is there a smarter way to do it.

WHERE fieldname IS NOT NULL

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-19 : 16:03:24

1. the function is SUBSTRING

https://msdn.microsoft.com/en-us/library/ms187748.aspx

2. the case is OK but you don't need the enclosing parenthesis. how will you calculate the overall percentage?

3. or just

select salresrepfield, COUNT(d.number_opportunities_won__c)
group by salesrepfield

4. WHERE clause is correct.

sqlbrahzilla
Starting Member

7 Posts

Posted - 2015-03-19 : 18:16:50

I'm trying to figure out how to complete the nps part of my query to use as a template for a metric I receive in almost all my queries moving forward so I'm a little confused how to complete it.

Aside from that I was also trying to build a template for calculating total sales by sales reps and have it so that the view gives me the rep for each sale, the date of each sale, and the nps for each sale. So if they are each different fields I don't think my template is even close to correct lol..

Thoughts?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-20 : 08:43:01

Best thing:

Post some sample input data and the results you want to see using that data

sqlbrahzilla
Starting Member

7 Posts

Posted - 2015-03-20 : 08:47:30

i will post output later thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources