Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

asked on

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?
Avatar of bfuchs
bfuchs
Flag of United States of America image

ASKER

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?
https://www.experts-exchange.com/questions/28671643/SQL-Query-question.html?anchorAnswerId=40771494#a40771494
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

Avatar of bfuchs

ASKER

@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
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

Avatar of bfuchs

ASKER

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.
Avatar of bfuchs

ASKER

@Vitor,
Did you looked at the following suggestion I posted above?
https://www.experts-exchange.com/questions/28671643/SQL-Query-question.html?anchorAnswerId=40771494#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..
Avatar of bfuchs

ASKER

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.
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; https://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
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
Avatar of bfuchs

ASKER

Hi eghtebas,

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

Thanks,
Ben
Avatar of bfuchs

ASKER

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
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

Avatar of bfuchs

ASKER

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
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
Avatar of bfuchs

ASKER

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

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
Avatar of bfuchs

ASKER

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
I have tried in SQL Server 2014. What version are you using?
Avatar of bfuchs

ASKER

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.
SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfuchs

ASKER

@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

:( 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'
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
Avatar of bfuchs

ASKER

@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!
Avatar of bfuchs

ASKER