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:SELECTCOUNT(1) AS 'Total'FROMDBS.PM.vwGenCustApptInfo vwGenCustApptInfoDBS.PM.vwGenCustInfo vwGenCustInfoWHEREvwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_IDAND ((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 NoEmailPecFROMDBS.PM.vwGenCustApptInfo vwGenCustApptInfoDBS.PM.vwGenCustInfo vwGenCustInfoWHEREvwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_IDGROUP BY vwGenCustApptInfo.Appt_Sched_Location_Abbr,Appt_Sched_Location_Descr[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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]'." ? |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
Dale45039
Starting Member
41 Posts |
Posted - 2012-07-05 : 12:21:12
|
I replaced braces ] at the end with ) - still getting error... |
|
|
Dale45039
Starting Member
41 Posts |
Posted - 2012-07-05 : 12:28:12
|
Found the problem. Changed the FROM statement:FROMvwGenCustApptInfo vwGenCustApptInfo, vwGenCustInfo vwGenCustInfo |
|
|
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:FROMvwGenCustApptInfo vwGenCustApptInfo, vwGenCustInfo vwGenCustInfo
didnt notice thatwhy not use ANSI join syntaxSELECT 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 NoEmailPecFROMDBS.PM.vwGenCustApptInfo vwGenCustApptInfoJOIN DBS.PM.vwGenCustInfo vwGenCustInfoONvwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_IDGROUP BY vwGenCustApptInfo.Appt_Sched_Location_Abbr,Appt_Sched_Location_Descr ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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? |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
Dale45039
Starting Member
41 Posts |
Posted - 2012-07-05 : 12:46:43
|
visakh16, I owe you Thank you!! |
|
|
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') |
|
|
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!!
npyou're welcome ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 NoEmailPecFROMDBS.PM.vwGenCustApptInfo vwGenCustApptInfoJOIN DBS.PM.vwGenCustInfo vwGenCustInfoONvwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_IDWHERE vwGenCustApptInfo.Appt_Sched_Location_Abbr NOT IN ('OF1','OF5')GROUP BY vwGenCustApptInfo.Appt_Sched_Location_Abbr,Appt_Sched_Location_Descr ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 NoEmailPecFrom PM.vwGenCustApptInfo vwGenCustApptInfo Inner Join PM.vwGenCustInfo vwGenCustInfo On vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_IDWhere 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_DescrOrder By EmailCollectedPerc Desc |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 NoEmailPecOF1 Office Number One 1058 641 1029 1699 0.622719247 0.377280753OF4 Office Number Four 257 414 546 671 0.383010432 0.616989568OF5 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 - 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 NoMailPercOffice01 89 57 146 0.61 0.39Office02 35 7 42 0.83 0.17QUERY1=================================================================================================================================SELECT Appt_Sched_Location_Descr, COUNT(DISTINCT vwGenCustApptInfo.Customer_ID) AS GotMailFrom vwGenCustApptInfo vwGenCustApptInfo Inner Join vwGenCustInfo vwGenCustInfo On vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_IDWherevwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_IDAND ((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_DescrOrder 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 NoMailFrom vwGenCustApptInfo vwGenCustApptInfo Inner Join vwGenCustInfo vwGenCustInfo On vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_IDWherevwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_IDAND ((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_DescrOrder 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 TotalCustFrom vwGenCustApptInfo vwGenCustApptInfo Inner Join vwGenCustInfo vwGenCustInfo On vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_IDWhere(vwGenCustApptInfo.Appt_Acknowledged_Date Between '2012-07-06 23:59:59' And '2012-07-10 23:59:59') Group By Appt_Sched_Location_DescrOrder 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
52326 Posts |
Posted - 2012-07-12 : 17:22:57
|
ok use like belowSELECT q1.Appt_Sched_Location_Descr,GotMail,NoMail,TotalCustFROM (Query1) q1JOIN (Query2) q2ON q2.Appt_Sched_Location_Descr = q1.Appt_Sched_Location_DescrJOIN (Query3) q3ON q3.Appt_Sched_Location_Descr = q1.Appt_Sched_Location_Descr ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Dale45039
Starting Member
41 Posts |
Posted - 2012-07-13 : 09:17:44
|
quote: Originally posted by visakh16 ok use like belowSELECT q1.Appt_Sched_Location_Descr,GotMail,NoMail,TotalCustFROM (Query1) q1JOIN (Query2) q2ON q2.Appt_Sched_Location_Descr = q1.Appt_Sched_Location_DescrJOIN (Query3) q3ON q3.Appt_Sched_Location_Descr = q1.Appt_Sched_Location_Descr ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Thanks. I just tried and got this error: "Could not add the table '(SELECT'." |
|
|
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 StatementNoMail=(SELECT COUNT(DISTINCT vwGenCustApptInfo.Customer_ID) From vwGenCustApptInfo vwGenCustApptInfo Inner Join vwGenCustInfo vwGenCustInfo On vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_IDWherevwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_IDAND ((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 StatementGotMail=(SELECT COUNT(DISTINCT vwGenCustApptInfo.Customer_ID) From vwGenCustApptInfo vwGenCustApptInfo Inner Join vwGenCustInfo vwGenCustInfo On vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_IDWherevwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_IDAND ((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 StatementTotalPT=(SELECT COUNT(DISTINCT vwGenCustApptInfo.Customer_ID) From vwGenCustApptInfo vwGenCustApptInfo Inner Join vwGenCustInfo vwGenCustInfo On vwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_IDWherevwGenCustInfo.Customer_ID = vwGenCustApptInfo.Customer_IDAND ((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
52326 Posts |
Posted - 2012-07-13 : 09:54:10
|
this is not what i suggested. try like suggested approach------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Previous Page&nsp;
Next Page
|