| Author |
 Topic  |
|
Dale45039
Starting Member
41 Posts |
 Posted - 07/05/2012 : 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
India
47173 Posts |
Posted - 07/05/2012 : 11:33:15
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
Dale45039
Starting Member
41 Posts |
Posted - 07/05/2012 : 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]'." ? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 07/05/2012 : 12:00:59
|
you're wrong braces at end it should be ) rather than ]
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Dale45039
Starting Member
41 Posts |
Posted - 07/05/2012 : 12:21:12
|
| I replaced braces ] at the end with ) - still getting error... |
|
|
|
Dale45039
Starting Member
41 Posts |
Posted - 07/05/2012 : 12:28:12
|
Found the problem. Changed the FROM statement:
FROM
vwGenCustApptInfo vwGenCustApptInfo, vwGenCustInfo vwGenCustInfo
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 07/05/2012 : 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/
|
|
|
|
Dale45039
Starting Member
41 Posts |
Posted - 07/05/2012 : 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? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 07/05/2012 : 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/
|
|
|
|
Dale45039
Starting Member
41 Posts |
Posted - 07/05/2012 : 12:46:43
|
visakh16, I owe you  Thank you!! |
|
|
|
Dale45039
Starting Member
41 Posts |
Posted - 07/05/2012 : 12:53:52
|
| One more question, how would I exclude one or more specific offices from this query? (EG: 'OF1' and 'OF5') |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 07/05/2012 : 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/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 07/05/2012 : 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/
|
|
|
|
Dale45039
Starting Member
41 Posts |
Posted - 07/12/2012 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 07/12/2012 : 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/
|
|
|
|
Dale45039
Starting Member
41 Posts |
Posted - 07/12/2012 : 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? |
|
|
|
Dale45039
Starting Member
41 Posts |
Posted - 07/12/2012 : 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.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 07/12/2012 : 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/
|
|
|
|
Dale45039
Starting Member
41 Posts |
Posted - 07/13/2012 : 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'." |
|
|
|
Dale45039
Starting Member
41 Posts |
Posted - 07/13/2012 : 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'))) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 07/13/2012 : 09:54:10
|
this is not what i suggested. try like suggested approach
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Topic |
|
Aggregate and Groupby
