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
 Aggregate and Groupby

Author  Topic 

Dale45039
Starting Member

41 Posts

Posted - 2012-07-05 : 11:19:14
I have had limited success generating a report for multiple offices. This has been a manual process where I enter the abbreviation of the office 'OF1', collect the data for '...Email Is Not Null', then '...Email Is Null', drop into a spreadsheet, and manually change the query for the next office 'OF2', 'OF3', etc.

Here is a sample of the final report:
=====================================

Appt_Sched_Location_Abbr Appt_Sched_Location_Descr Email Collected No Email Total Cust Email Collected No Email
OF1 OFFICE1 21,272 39,100 60,372 35% 65%
OF2 OFFICE2 7,878 18,482 26,360 30% 70%
OF3 OFFICE3 4,724 14,665 19,389 24% 76%
-------------------------------------
Columns Explained
-------------------------------------
"Appt_Sched_Location_Abbr" (Column1) is: Office Abbreviation

"Appt_Sched_Location_Descr" (Column2) is: Office Description

"Email Collected" (Column3) is: '...Email Is Not Null' Total

"No Email" (Column4) is: '...Email Is Null' Total

"Total Cust" (Column5) is: '...Email Is Not Null', plus '...Email Is Null' Calculation

"Email Collected" (Column6) is: '...Email Is Not Null' (Column3), Divided by "Total Cust" (Column5) Calculation

"No Email" (Column7) is: '...Email Is Null'(Column4), Divided by "Total Cust" (Column5) Calculation
=====================================

I woud love to be able to automate as much as possible - any help would be greatly appreciated!

Here is the query I have been running manually:

SELECT
COUNT(1) AS 'Total'
FROM
DBS.PM.vwGenCustApptInfo vwGenCustApptInfo
DBS.PM.vwGenCustInfo vwGenCustInfo
WHERE
vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_ID
AND ((vwGenCustApptInfo.Appt_Sched_Location_Abbr = 'OF1')AND(vwGenCustInfo.Customer_Email Is Not Null))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 11:33:15
[code]
SELECT vwGenCustApptInfo.Appt_Sched_Location_Abbr,Appt_Sched_Location_Descr,
COUNT(CASE WHEN vwGenCustInfo.Customer_Email Is Not Null THEN 1 END) AS EmailCollected,
COUNT(CASE WHEN vwGenCustInfo.Customer_Email Is Null THEN 1 END) AS NoEmail,
COUNT(1) AS TotalCust,
COUNT(CASE WHEN vwGenCustInfo.Customer_Email Is Not Null THEN 1 END)*100.0/COUNT(1) AS EmailCollectedPerc,
COUNT(CASE WHEN vwGenCustInfo.Customer_Email Is Null THEN 1 END)*100.0/COUNT(1) AS NoEmailPec
FROM
DBS.PM.vwGenCustApptInfo vwGenCustApptInfo
DBS.PM.vwGenCustInfo vwGenCustInfo
WHERE
vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_ID
GROUP BY vwGenCustApptInfo.Appt_Sched_Location_Abbr,Appt_Sched_Location_Descr
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Dale45039
Starting Member

41 Posts

Posted - 2012-07-05 : 11:57:13
Thank you visakh16.

Getting this error in MS Query: "Incorrect column expression: 'COUNT(CASE WHEN vwGenCustlnfo.Customer_Email Is Not Null THEN 1 END]'." ?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 12:00:59
you're wrong braces at end it should be ) rather than ]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Dale45039
Starting Member

41 Posts

Posted - 2012-07-05 : 12:21:12
I replaced braces ] at the end with ) - still getting error...
Go to Top of Page

Dale45039
Starting Member

41 Posts

Posted - 2012-07-05 : 12:28:12
Found the problem. Changed the FROM statement:
FROM
vwGenCustApptInfo vwGenCustApptInfo, vwGenCustInfo vwGenCustInfo
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 12:32:01
quote:
Originally posted by Dale45039

Found the problem. Changed the FROM statement:
FROM
vwGenCustApptInfo vwGenCustApptInfo, vwGenCustInfo vwGenCustInfo



didnt notice that

why not use ANSI join syntax


SELECT vwGenCustApptInfo.Appt_Sched_Location_Abbr,Appt_Sched_Location_Descr,
COUNT(CASE WHEN vwGenCustInfo.Customer_Email Is Not Null THEN 1 END) AS EmailCollected,
COUNT(CASE WHEN vwGenCustInfo.Customer_Email Is Null THEN 1 END) AS NoEmail,
COUNT(1) AS TotalCust,
COUNT(CASE WHEN vwGenCustInfo.Customer_Email Is Not Null THEN 1 END)*100.0/COUNT(1) AS EmailCollectedPerc,
COUNT(CASE WHEN vwGenCustInfo.Customer_Email Is Null THEN 1 END)*100.0/COUNT(1) AS NoEmailPec
FROM
DBS.PM.vwGenCustApptInfo vwGenCustApptInfo
JOIN DBS.PM.vwGenCustInfo vwGenCustInfo
ON
vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_ID
GROUP BY vwGenCustApptInfo.Appt_Sched_Location_Abbr,Appt_Sched_Location_Descr


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Dale45039
Starting Member

41 Posts

Posted - 2012-07-05 : 12:32:50
How hard would it be to format the results in Column5 to "Use 1000 Separator (,)" and Columns 6 and 7 to "Percentage (%)" with zero decimal places?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 12:34:45
quote:
Originally posted by Dale45039

How hard would it be to format the results in Column5 to "Use 1000 Separator (,)" and Columns 6 and 7 to "Percentage (%)" with zero decimal places?


these are presentation requirements which should be done at front end application using formatting functions. SQL is not ideal place for doing these

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Dale45039
Starting Member

41 Posts

Posted - 2012-07-05 : 12:46:43
visakh16, I owe you Thank you!!
Go to Top of Page

Dale45039
Starting Member

41 Posts

Posted - 2012-07-05 : 12:53:52
One more question, how would I exclude one or more specific offices from this query? (EG: 'OF1' and 'OF5')
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 13:03:06
quote:
Originally posted by Dale45039

visakh16, I owe you Thank you!!


np
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 13:04:54
quote:
Originally posted by Dale45039

One more question, how would I exclude one or more specific offices from this query? (EG: 'OF1' and 'OF5')



SELECT vwGenCustApptInfo.Appt_Sched_Location_Abbr,Appt_Sched_Location_Descr,
COUNT(CASE WHEN vwGenCustInfo.Customer_Email Is Not Null THEN 1 END) AS EmailCollected,
COUNT(CASE WHEN vwGenCustInfo.Customer_Email Is Null THEN 1 END) AS NoEmail,
COUNT(1) AS TotalCust,
COUNT(CASE WHEN vwGenCustInfo.Customer_Email Is Not Null THEN 1 END)*100.0/COUNT(1) AS EmailCollectedPerc,
COUNT(CASE WHEN vwGenCustInfo.Customer_Email Is Null THEN 1 END)*100.0/COUNT(1) AS NoEmailPec
FROM
DBS.PM.vwGenCustApptInfo vwGenCustApptInfo
JOIN DBS.PM.vwGenCustInfo vwGenCustInfo
ON
vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_ID
WHERE vwGenCustApptInfo.Appt_Sched_Location_Abbr NOT IN ('OF1','OF5')
GROUP BY vwGenCustApptInfo.Appt_Sched_Location_Abbr,Appt_Sched_Location_Descr




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Dale45039
Starting Member

41 Posts

Posted - 2012-07-12 : 09:55:16
Turns out this is not doing exactly what I'd like. It's counting emails for each customer appointment.

I would like it to count 'EmailCollected' from 'Distinct Customers' and 'NoEmail' from 'Distinct Customers'

Here is what I tried:

Select
vwGenCustApptInfo.Appt_Sched_Location_Abbr,
vwGenCustApptInfo.Appt_Sched_Location_Descr,
Count(Case When vwGenCustInfo.Customer_Email Is Not Null Then 1
End) As EmailCollected,
Count(Case When vwGenCustInfo.Customer_Email Is Null Then 1 End) As NoEmail,
Count(Distinct vwGenCustApptInfo.Customer_ID) As 'Distinct Customers',
Count(1) As 'Total Appt',
Count(Case When vwGenCustInfo.Customer_Email Is Not Null Then 1
End) * 1.0 / Count(1) As EmailCollectedPerc,
Count(Case When vwGenCustInfo.Customer_Email Is Null Then 1
End) * 1.0 / Count(1) As NoEmailPec
From
PM.vwGenCustApptInfo vwGenCustApptInfo Inner Join
PM.vwGenCustInfo vwGenCustInfo On vwGenCustInfo.Customer_ID =
vwGenCustApptInfo.Customer_ID
Where
vwGenCustApptInfo.Appt_Sched_Location_Abbr Not In ('OF2', 'OF3', 'OF7',
'OF21') And
vwGenCustApptInfo.Appt_Acknowledged_Date Between '2012-06-09 23:59:59' And
'2012-07-10 23:59:59'
Group By
vwGenCustApptInfo.Appt_Sched_Location_Abbr,
vwGenCustApptInfo.Appt_Sched_Location_Descr
Order By
EmailCollectedPerc Desc
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-12 : 10:15:58
can you elaborate with some sample data. i cant get the issue from query alone

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Dale45039
Starting Member

41 Posts

Posted - 2012-07-12 : 11:11:25
Here is a sample of the final report:
=====================================

Appt_Sched_Location_Abbr Appt_Sched_Location_Descr EmailCollected NoEmail Distinct Customers Total Appt EmailCollectedPerc NoEmailPec
OF1 Office Number One 1058 641 1029 1699 0.622719247 0.377280753
OF4 Office Number Four 257 414 546 671 0.383010432 0.616989568
OF5 Office Number Five 156 267 353 423 0.368794326 0.631205674
-------------------------------------
Columns Explained
-------------------------------------
"Appt_Sched_Location_Abbr" (Column1) is: Office Abbreviation

"Appt_Sched_Location_Descr" (Column2) is: Office Description

"EmailCollected" (Column3) is: '...Email Is Not Null' Total
(I would like it to be 'Distinct Customers' WHERE '...Email Is Not Null' Total)

"NoEmail" (Column4) is: '...Email Is Null' Total
(I would like it to be 'Distinct Customers' WHERE '...Email Is Null' Total)

"Distinct Customers" (Column5) is: Total Distinct Customers
(My goal is for Column5 and Column6 totals to be the same, this column is here for me to verify)

"Total Appt" (Column6) is: Column3, plus Column4 Calculation
(Let's re-name this 'Total')

"EmailCollectedPerc" (Column7) is: Column3 Divided by Column6 Calculation

"NoEmailPec" (Column8) is: Column4 Divided by Column6 Calculation
=====================================

Does this help - or is it as clear as mud?
Go to Top of Page

Dale45039
Starting Member

41 Posts

Posted - 2012-07-12 : 14:07:19
Let me try a new approach.

I have written three (3) separate queries that provide the basic info I need. My goal is to combine all three (3) queries into one (1) new query with a few calculations:

1. GotMail column divided by TotalCust column.
2. NoMail column divided by TotalCust column.

Desired results would look like this:
Location GotMail NoMail TotalCust GotMailPerc NoMailPerc
Office01 89 57 146 0.61 0.39
Office02 35 7 42 0.83 0.17

QUERY1
=================================================================================================================================
SELECT Appt_Sched_Location_Descr,
COUNT(DISTINCT vwGenCustApptInfo.Customer_ID) AS GotMail
From
vwGenCustApptInfo vwGenCustApptInfo Inner Join
vwGenCustInfo vwGenCustInfo On vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_ID
Where
vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_ID
AND ((vwGenCustInfo.Customer_Email Is Not Null) AND (vwGenCustApptInfo.Appt_Acknowledged_Date Between '2012-07-06 23:59:59' And
'2012-07-10 23:59:59'))
Group By Appt_Sched_Location_Descr
Order By GotMail DESC
-------------------------------------------------------------------------------------------------------------------------------
Results above give me a count from each "Appt_Sched_Location" of DISTINCT Customers that have email and were seen between 07/06 and 07/10.

QUERY2
=================================================================================================================================
SELECT Appt_Sched_Location_Descr,
COUNT(DISTINCT vwGenCustApptInfo.Customer_ID) AS NoMail
From
vwGenCustApptInfo vwGenCustApptInfo Inner Join
vwGenCustInfo vwGenCustInfo On vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_ID
Where
vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_ID
AND ((vwGenCustInfo.Customer_Email Is Null) AND (vwGenCustApptInfo.Appt_Acknowledged_Date Between '2012-07-06 23:59:59' And
'2012-07-10 23:59:59'))
Group By Appt_Sched_Location_Descr
Order By NoMail DESC
-------------------------------------------------------------------------------------------------------------------------------
Results above give me a count from each "Appt_Sched_Location" of DISTINCT Customers that DO NOT have email and were seen between 07/06 and 07/10.

QUERY3
=================================================================================================================================
SELECT Appt_Sched_Location_Descr,
COUNT(DISTINCT vwGenCustApptInfo.Customer_ID) AS TotalCust
From
vwGenCustApptInfo vwGenCustApptInfo Inner Join
vwGenCustInfo vwGenCustInfo On vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_ID
Where
(vwGenCustApptInfo.Appt_Acknowledged_Date Between '2012-07-06 23:59:59' And
'2012-07-10 23:59:59')
Group By Appt_Sched_Location_Descr
Order By TotalCust DESC
-------------------------------------------------------------------------------------------------------------------------------
Results above give me a count from each "Appt_Sched_Location" of DISTINCT Customers that were seen between 07/06 and 07/10.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-12 : 17:22:57
ok use like below

SELECT q1.Appt_Sched_Location_Descr,GotMail,NoMail,TotalCust
FROM (Query1) q1
JOIN (Query2) q2
ON q2.Appt_Sched_Location_Descr = q1.Appt_Sched_Location_Descr
JOIN (Query3) q3
ON q3.Appt_Sched_Location_Descr = q1.Appt_Sched_Location_Descr



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Dale45039
Starting Member

41 Posts

Posted - 2012-07-13 : 09:17:44
quote:
Originally posted by visakh16

ok use like below

SELECT q1.Appt_Sched_Location_Descr,GotMail,NoMail,TotalCust
FROM (Query1) q1
JOIN (Query2) q2
ON q2.Appt_Sched_Location_Descr = q1.Appt_Sched_Location_Descr
JOIN (Query3) q3
ON q3.Appt_Sched_Location_Descr = q1.Appt_Sched_Location_Descr



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





Thanks. I just tried and got this error: "Could not add the table '(SELECT'."
Go to Top of Page

Dale45039
Starting Member

41 Posts

Posted - 2012-07-13 : 09:29:35
I've had limited success with the query below, but I can't figure out how to GROUP BY "Appt_Sched_Location_Descr". What do you think?

SELECT
-- 'NoMail' Where Statement
NoMail=(SELECT COUNT(DISTINCT vwGenCustApptInfo.Customer_ID) From
vwGenCustApptInfo vwGenCustApptInfo Inner Join
vwGenCustInfo vwGenCustInfo On vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_ID
Where
vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_ID
AND ((vwGenCustInfo.Customer_Email Is Null) AND (vwGenCustApptInfo.Appt_Acknowledged_Date Between '2012-07-06 23:59:59' And
'2012-07-10 23:59:59'))),
-- 'GotMail' Where Statement
GotMail=(SELECT COUNT(DISTINCT vwGenCustApptInfo.Customer_ID) From
vwGenCustApptInfo vwGenCustApptInfo Inner Join
vwGenCustInfo vwGenCustInfo On vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_ID
Where
vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_ID
AND ((vwGenCustInfo.Customer_Email Is Not Null) AND (vwGenCustApptInfo.Appt_Acknowledged_Date Between '2012-07-06 23:59:59' And
'2012-07-10 23:59:59'))),
-- 'TotalPT' Where Statement
TotalPT=(SELECT COUNT(DISTINCT vwGenCustApptInfo.Customer_ID) From
vwGenCustApptInfo vwGenCustApptInfo Inner Join
vwGenCustInfo vwGenCustInfo On vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_ID
Where
vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_ID
AND ((vwGenCustApptInfo.Appt_Acknowledged_Date Between '2012-07-06 23:59:59' And
'2012-07-10 23:59:59')))
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-13 : 09:54:10
this is not what i suggested. try like suggested approach

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -