SQL Query question

Hi Experts,
I have the following sql embedded  in a store procedure
SELECT ''Prospect'' AS Category, COUNT(*) AS [Total #]
						FROM dbo.View_EmpStatisticsEmployeesTbl
						WHERE ' + @strFilter +
					  '	And EmployeeStatus = ''Prospect''

Open in new window

Now I need to add a column next to the count, the third column should display the percentage of column #2 out of the total (total meaning only including the condition of the strFilter, but not the second condition).

What is the most efficient way to accomplish it?
LVL 5
bfuchsAsked:
Who is Participating?
 
Mike EghtebasDatabase and Application DeveloperCommented:
You really worked hard on this. Following will work for categories other than 'Prospect':
DECLARE @strSelect nvarchar(4000);
DECLARE @strFilter nvarchar(400);
DECLARE @Catagoty nvarchar(25) = 'Prospect';

set @strFilter = 'State = ''nj''';

SET @strSelect = '
With CTE_1
As
(
Select Count(*) As Qty1
From dbo.View_EmpStatisticsEmployeesTbl
WHERE ' + @strFilter + '),
CTE_2
AS
(
Select   Count(*) As Qty2
From dbo.View_EmpStatisticsEmployeesTbl
WHERE EmployeeStatus = ' + @Catagoty + ' And '+ @strFilter + '
)
Select ' + @Catagoty + '  AS Category
   , Qty1
   , cast(Qty1 as decimal(10,3)) As Numerator
   , cast((Select Qty2 From CTE_2) as decimal(10,3)) As Denominator
   , cast((Select Qty2 From CTE_2) * 100.0 / qty1 as decimal(10,2)) 
From CTE_1'

EXEC sp_ExecuteSQL @strSelect

Open in new window

0
 
bfuchsAuthor Commented:
Hi,
I tested the below in SSMS
DECLARE @strFilter nvarchar(4000)
set @strFilter = 'State = ''nj''';
                                    
                                    
;WITH Tot
AS
(
Select COUNT(*) AS Qty 
From dbo.View_EmpStatisticsEmployeesTbl
WHERE ' + @strFilter + ') 
Select ''Prospect'' AS Category, COUNT(*) AS [Total #], COUNT(*)/Tot.QTY AS Perctntag
WHERE EmployeeStatus = ''Prospect''  

Open in new window

and got the following error

Msg 4145, Level 15, State 1, Line 11
An expression of non-boolean type specified in a context where a condition is expected, near ')'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near ''.

Perhaps you can help me modify the following suggestion to this case?
http://www.experts-exchange.com/questions/28671643/SQL-Query-question.html#a40771494
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this:
DECLARE @strSelect nvarchar(4000)
DECLARE @strFilter nvarchar(400)
set @strFilter = 'State = ''nj''';
                                    

SET @strSelect = '                                  
WITH Tot
AS
(
Select COUNT(*) AS Qty 
From dbo.View_EmpStatisticsEmployeesTbl
WHERE ' + @strFilter + ') 
Select EmployeeStatus AS Category, COUNT(*) AS [Total #], COUNT(*)/Tot.QTY AS Perctntag
WHERE EmployeeStatus = ''Prospect''
GROUP BY EmployeeStatus'

sp_ExecuteSQL @strSelect 

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
bfuchsAuthor Commented:
@Vitor,
Getting the following for yours.

Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'sp_ExecuteSQL'.

@Jim,
"SQL Query help needed in adding a column" would be enough for title?

Thanks,
Ben
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I forgot the EXEC command:
DECLARE @strSelect nvarchar(4000)
DECLARE @strFilter nvarchar(400)
set @strFilter = 'State = ''nj''';
                                    

SET @strSelect = '                                  
WITH Tot
AS
(
Select COUNT(*) AS Qty 
From dbo.View_EmpStatisticsEmployeesTbl
WHERE ' + @strFilter + ') 
Select EmployeeStatus AS Category, COUNT(*) AS [Total #], COUNT(*)/Tot.QTY AS Perctntag
WHERE EmployeeStatus = ''Prospect''
GROUP BY EmployeeStatus'

EXEC sp_ExecuteSQL @strSelect 

Open in new window

0
 
bfuchsAuthor Commented:
Hi Vitor,

Getting the below

Msg 207, Level 16, State 1, Line 9
Invalid column name 'EmployeeStatus'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'EmployeeStatus'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'EmployeeStatus'.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "Tot.QTY" could not be bound.
0
 
bfuchsAuthor Commented:
@Vitor,
Did you looked at the following suggestion I posted above?
http://www.experts-exchange.com/questions/28671643/SQL-Query-question.html#a40771494
This was the exact same question I posted just with a different sql, I believe with minor changes this should work here as well.

@Jim,
Perhaps you can help me out here..
0
 
bfuchsAuthor Commented:
Just updating,
Also tried the following
DECLARE @strSelect nvarchar(4000)
DECLARE @strFilter nvarchar(400)
set @strFilter = 'State = ''nj''';
                                    

SET @strSelect = '                                  
WITH Tot
AS
(
Select COUNT(*) AS Qty 
From dbo.View_EmpStatisticsEmployeesTbl
WHERE ' + @strFilter + ') 
Select EmployeeStatus AS Category, COUNT(*) AS [Total #], COUNT(*)/Tot.QTY AS Perctntag
From dbo.View_EmpStatisticsEmployeesTbl
WHERE EmployeeStatus = ''Prospect''
GROUP BY EmployeeStatus'

EXEC sp_ExecuteSQL @strSelect 

Open in new window


And getting error below
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "Tot.QTY" could not be bound.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
I have two CTEs below with an outer query using them. I had to make a tempt table to test it. Using this solution you can make it work.

create table #t(Col_1 varchar(15), Col_2 varchar(15));
Insert #t (Col_1,Col_2) values
('Jeff', 'Hans')
,('Dave', 'Nancy')
,('Grorge', null)
,('Shila', 'Smith')
,('Peter', null)
,('Kyle', 'Ralph')

select * From #t;

;With CTE_1
As
(
Select Count(*) As Qty1
From #t
Where Col_2 is not null),
CTE_2
AS
(
Select  Count(*) As Qty2
From #t
Where  Col_1 = Col_1
)
Select 'Prospect' AS Category
   , Qty1
   , cast(Qty1 as decimal(10,3))/cast(Qty2 as decimal(10,3)) As Percentage
From CTE_1, CTE_2

Open in new window


Alternatively, you can change lines 25 - 28 above to:
Select 'Prospect' AS Category
   , Qty1
   , cast(Qty1 as decimal(10,3))/cast((Select Qty2 From CTE_2) as decimal(10,3)) As Percentage
From CTE_1

Open in new window


For multiple CTEs, see Box 4 T; http://www.experts-exchange.com/articles/18366/Subqueries-at-a-glance.html
(To refer from one CTE to another:...)

or, see: http://blog.sqlauthority.com/2009/08/08/sql-server-multiple-cte-in-one-select-statement-query/

Mike
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Try:
DECLARE @strSelect nvarchar(4000)
DECLARE @strFilter nvarchar(400)
set @strFilter = 'State = ''nj''';

SET @strSelect = '
With CTE_1
As
(
Select Count(*) As Qty1
From dbo.View_EmpStatisticsEmployeesTbl
WHERE ' + @strFilter + '),
CTE_2
AS
(
Select  Count(*) As Qty2
From dbo.View_EmpStatisticsEmployeesTbl
WHERE EmployeeStatus = ''Prospect'')
Select EmployeeStatus AS Category
   , Qty1
   , cast(Qty1 as decimal(10,3))/cast(Qty2 as decimal(10,3)) As Percentage
From CTE_1, CTE_2
Order By Category'

EXEC sp_ExecuteSQL @strSelect

Open in new window


Mike
0
 
bfuchsAuthor Commented:
Hi eghtebas,

Just arrived at work, will test them & let you know,

Thanks,
Ben
0
 
bfuchsAuthor Commented:
Hi,

As is its giving me the following error

Msg 207, Level 16, State 1, Line 14
Invalid column name 'EmployeeStatus'.

Trying to figure out how to solve it, as that column definitely exists..

Thanks,
Ben
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Invalid column name 'EmployeeStatus'. is not in CTE_1 or CTE_2. The code below includes it in CTE_2

try:
DECLARE @strSelect nvarchar(4000)
DECLARE @strFilter nvarchar(400)
set @strFilter = 'State = ''nj''';

SET @strSelect = '
With CTE_1
As
(
Select Count(*) As Qty1
From dbo.View_EmpStatisticsEmployeesTbl
WHERE ' + @strFilter + '),
CTE_2
AS
(
Select  EmployeeStatus AS Category, Count(*) As Qty2
From dbo.View_EmpStatisticsEmployeesTbl
WHERE EmployeeStatus = ''Prospect''
Group By EmployeeStatus)
Select Category
   , Qty1
   , cast(Qty1 as decimal(10,3))/cast(Qty2 as decimal(10,3)) As Percentage
From CTE_1, CTE_2
Order By Category'

Open in new window

0
 
bfuchsAuthor Commented:
Okay We are getting closer..

There were no errors anymore.

Now we only need the numbers should be accurate..

The second column Total, # is correct but the third number is not.

Its showing 1.04372077762283 while the real number is much higher (approx 6.4)

In addition, in second column its possible to have the total prospects, instead of the overall total.

Also why do I need the order by Category in the end?

Thanks,
Ben
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
You don't need order by Category. I was using it in my temp table example.

Apparently, the percentage could be max 1.00.

Also, this percentage division of two numbers. So, if you change the outer query part to:

...
Select Category
   , Qty1
   , cast(Qty1 as decimal(10,3)) As Numerator
   , cast(Qty2 as decimal(10,3)) As Denominator
From CTE_1, CTE_2

You will see one of the numbers is out of shape. The solution to correct that number could be:

1. The filter applied to CTE_2. Work with it to see if you can correct the filter.

2. Other most likely reason could be use of Cross Join. If so then try:
Select Category
   , Qty1
   , cast(Qty1 as decimal(10,3)) As Numerator
   , cast((Select Qty2 From CTE_2) as decimal(10,3)) As Denominator
From CTE_1

Open in new window


After getting reasonable number use:
 
Select  Category
   , Qty1
   , cast(Qty1 as decimal(10,3))/cast((Select Qty2 From CTE_2)/decimal(10,3)) As Percentage
From CTE_1

Open in new window

Mike
0
 
bfuchsAuthor Commented:
The filter applied to CTE_2 which is strFilter is correct.

Columns 2 & 3 are the same (in the first sql above), and that is the correct # of total.

The forth column is giving me a number that is little less then the total but doesn't make any sense.

What I need is the total of the below, and the % that number is out of the total.

select count(*) From dbo.View_EmpStatisticsEmployeesTbl
where state = 'nj'
and employeestatus = 'prospect'

Open in new window

0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Qty1 in CTE_1
QTY2 and Category in CTE_2
Display Category, Qty1, Qty2 in the Outer Query

Work with the filters in CTE_1 and CTE_2 until you get correct (expected) numerator and denominator. When you get the correct values, then divide them to get percentage.

Other than supplying the structure, I can not test it because I do not access to your database.

Mike
0
 
bfuchsAuthor Commented:
okay, I finally got the number to be correct,

Now I only need the division

When trying your code for division above I get the following

Msg 195, Level 15, State 10, Line 19
'decimal' is not a recognized built-in function name.

any Other idea?

Also if possible to round that division by 2 digits (like 6.40)

Thanks,
Ben
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
I have tried in SQL Server 2014. What version are you using?
0
 
bfuchsAuthor Commented:
2005/2008

Actually I got it done by the following

DECLARE @strSelect nvarchar(4000)
DECLARE @strFilter nvarchar(400)
set @strFilter = 'State = ''nj''';

SET @strSelect = '
With CTE_1
As
(
Select Count(*) As Qty1
From dbo.View_EmpStatisticsEmployeesTbl
WHERE ' + @strFilter + '),
CTE_2
AS
(
Select   Count(*) As Qty2
From dbo.View_EmpStatisticsEmployeesTbl
WHERE EmployeeStatus = ''Prospect'' And '+ @strFilter + '
)
Select ''Prospect'' AS Category
   , Qty1
   , cast(Qty1 as decimal(10,3)) As Numerator
   , cast((Select Qty2 From CTE_2) as decimal(10,3)) As Denominator
   , cast((Select Qty2 From CTE_2) * 100.0 / qty1 as decimal(10,2)) 
From CTE_1'

EXEC sp_ExecuteSQL @strSelect

Open in new window


However, I still need to test it in 2005.
0
 
PortletPaulfreelancerCommented:
Ben, you would help yourself to faster solutions if you provided
a: sample data & 
b: expected result

From this sample data:
CREATE TABLE View_EmpStatisticsEmployeesTbl
    ([ID] int, [EmployeeStatus] varchar(8), [State] varchar(2))
;
    
INSERT INTO View_EmpStatisticsEmployeesTbl
    ([ID], [EmployeeStatus], [State])
VALUES
    (1, 'BLAH', 'X'),
    (2, 'BLAH', 'X'),
    (3, 'BLAH', 'X'),
    (4, 'BLAH', 'X'),
    (5, 'BLAH', 'X'),
    (6, 'BLAH', 'NJ'),
    (7, 'BLAH', 'NJ'),
    (8, 'BLAH', 'NJ'),
    (9, 'BLAH', 'NJ'),
    (10, 'BLAH', 'NJ'),
    (11, 'Prospect', 'NJ')
;

Open in new window


I got this result:
|          | Num Prospects | Propect Percentage | Propect Percentage Rounded |
|----------|---------------|--------------------|----------------------------|
| Prospect |             1 |          16.666666 |                      16.67 |

Open in new window


Using this query:
SELECT
       'Prospect'
     , SUM(CASE WHEN employeestatus = 'prospect' THEN 1 END) AS [Num Prospects]
     , SUM(CASE WHEN employeestatus = 'prospect' THEN 100.0 END) / (COUNT(*) * 1.0) AS [Propect Percentage]
     
     -- TO ROUNG THE RESULT
     , ROUND(SUM(CASE WHEN employeestatus = 'prospect' THEN 100.0 END) / (COUNT(*) * 1.0),2) AS [Propect Percentage Rounded]
     
FROM (
    SELECT EmployeeStatus FROM dbo.View_EmpStatisticsEmployeesTbl
    WHERE state = 'nj'
    ) AS E

Open in new window


You can inspect this as a working demo here:  http://sqlfiddle.com/#!6/2ace0/2
0
 
bfuchsAuthor Commented:
@eghtebas,

There is one problem I'm having with your solution,

I need it to be part of a union query in a store procedure, and when I execute it I get the following error message

Incorrect syntax near the keyword 'With'.

this is the code I just added there

union
                    With CTE_1
						As
						(
						Select Count(*) As Qty1
						From dbo.View_EmpStatisticsEmployeesTbl
						WHERE ' + @strFilter + '),
						CTE_2
						AS
						(
						Select   Count(*) As Qty2
						From dbo.View_EmpStatisticsEmployeesTbl
						WHERE EmployeeStatus = ''Prospect'' And '+ @strFilter + '
						)
						Select ''Prospect'' AS Category
						   , (Select Qty2 From CTE_2) AS [Total #]
						   , cast((Select Qty2 From CTE_2) * 100.0 / qty1 as decimal(10,2)) AS Percentage
						From CTE_1

                        '

Open in new window

0
 
PortletPaulfreelancerCommented:
:( this is the problem with only displaying part of the overall stored procedure.

I recommend you re-try without using a CTE at all, which is possible.

In the example I provided above I have used a subquery type called "derived table" that would be based on the @strFilter conditions.

You only need that one subquery.

Then use a "case expression" to figure out which of the records has the category of 'prospect'
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Ben,

Your project may have some other issues not discussed in your original post. You have basically gotten what you have asked so far. My commendation is to close this question and post a new question keeping in mind what Paul stated above (sample data etc.)

Mike
0
 
bfuchsAuthor Commented:
@eghtebas,
Actually, you're 100% right.
I was just wondering if that could work as part of a union query or it needs a different method.
However regardless, I am very thankful for your help!!

@Paul,
Your solution seems to work as well, Just didn't have a chance to test yet in the store procedure.
Thank You!
0
 
bfuchsAuthor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.