| Author |
 Topic  |
|
Dale45039
Starting Member
41 Posts |
 Posted - 07/13/2012 : 10:02:00
|
Hi visakh16,
See my post 07/13/2012 : 09:17:44. I tried your suggestion, but got this error: "Could not add the table '(SELECT'."  Sorry. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48025 Posts |
Posted - 07/13/2012 : 10:05:21
|
post the full code without which i cant understand what you're doing wrong
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Dale45039
Starting Member
41 Posts |
Posted - 07/13/2012 : 10:42:49
|
SELECT q1.Appt_Sched_Location_Descr,GotMail,NoMail,TotalCust
FROM (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
) q1
JOIN (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
) q2
ON q2.Appt_Sched_Location_Descr = q1.Appt_Sched_Location_Descr
JOIN (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
) q3
ON q3.Appt_Sched_Location_Descr = q1.Appt_Sched_Location_Descr |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48025 Posts |
Posted - 07/13/2012 : 10:51:40
|
you had some order by's which is not allowed inside subqueries unless you've top clause
SELECT q1.Appt_Sched_Location_Descr,GotMail,NoMail,TotalCust
FROM (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
) q1
JOIN (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
) q2
ON q2.Appt_Sched_Location_Descr = q1.Appt_Sched_Location_Descr
JOIN (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
) 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 : 11:11:04
|
| Found the problem. "(SELECT Appt_Sched_Location_Descr" needs to be "(SELECT vwGenCustApptInfo.Appt_Sched_Location_Descr" |
|
|
|
Dale45039
Starting Member
41 Posts |
Posted - 07/13/2012 : 11:13:43
|
visakh16, thank you for your patience and generous help!  |
|
|
|
Dale45039
Starting Member
41 Posts |
Posted - 07/13/2012 : 11:23:14
|
Now that we got that part working, how can I add two more calculated columns to the end?
1. Divide 'GotMail' by 'TotalCust'
2. Divide 'NoMail' by 'TotalCust' |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48025 Posts |
Posted - 07/13/2012 : 23:17:47
|
SELECT q1.Appt_Sched_Location_Descr,GotMail,NoMail,TotalCust,
GotMail*1.0/totalCust AS CalculatedCol1,
NoMail*1.0/totalCust AS CalculatedCol2
FROM (SELECT vwGenCustApptInfo.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
) q1
JOIN (SELECT vwGenCustApptInfo.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
) q2
ON q2.Appt_Sched_Location_Descr = q1.Appt_Sched_Location_Descr
JOIN (SELECT vwGenCustApptInfo.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
) 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/16/2012 : 08:22:10
|
There you go again - rescuing a newbie  Thanks you visakh16! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48025 Posts |
Posted - 07/16/2012 : 09:44:46
|
welcome...as always
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Dale45039
Starting Member
41 Posts |
Posted - 11/09/2012 : 14:09:25
|
When I run the query below in SQL Server Management Studio 10.0.1600.22, I get this error: Msg 208, Level 16, State 1, Line 14
Invalid object name 'vwGenPatApptInfo'.
When I use Excel and Microsoft Query, I do not get an error.
Why does Studio error and MS Query does not?
--------------------------------------------------------------
Select
q1.Appt_Sched_Location_Descr,
q1.GotMail,
q2.NoMail,
q3.TotalPat,
-- q1.GotMail + q2.NoMail As Dale,
q1.GotMail * 1.0 / q3.TotalPat As GotMailPerc,
q2.NoMail * 1.0 / q3.TotalPat As NoMailPerc
From
(Select
vwGenPatApptInfo.Appt_Sched_Location_Descr,
Count(Distinct vwGenPatApptInfo.Patient_ID) As GotMail
From
vwGenPatApptInfo vwGenPatApptInfo Inner Join
vwGenPatInfo vwGenPatInfo On vwGenPatInfo.Patient_ID =
vwGenPatApptInfo.Patient_ID
Where
vwGenPatInfo.Patient_ID = vwGenPatApptInfo.Patient_ID And
(vwGenPatInfo.Patient_Email Is Not Null And
vwGenPatApptInfo.Appt_Sched_Location_Abbr Not In ('OFPAIN', 'DMDIAB',
'OFHMP', 'OFSPINEN') And
vwGenPatApptInfo.Appt_Acknowledged_Date Between '2012-09-01 00:00:01' And
'2012-09-30 23:59:59')
Group By
vwGenPatApptInfo.Appt_Sched_Location_Descr) q1 Join
(Select
vwGenPatApptInfo.Appt_Sched_Location_Descr,
Count(Distinct vwGenPatApptInfo.Patient_ID) As NoMail
From
vwGenPatApptInfo vwGenPatApptInfo Inner Join
vwGenPatInfo vwGenPatInfo On vwGenPatInfo.Patient_ID =
vwGenPatApptInfo.Patient_ID
Where
vwGenPatInfo.Patient_ID = vwGenPatApptInfo.Patient_ID And
(vwGenPatInfo.Patient_Email Is Null And
vwGenPatApptInfo.Appt_Sched_Location_Abbr Not In ('OFPAIN', 'DMDIAB',
'OFHMP', 'OFSPINEN') And
vwGenPatApptInfo.Appt_Acknowledged_Date Between '2012-09-01 00:00:01' And
'2012-09-30 23:59:59')
Group By
vwGenPatApptInfo.Appt_Sched_Location_Descr) q2
On q2.Appt_Sched_Location_Descr = q1.Appt_Sched_Location_Descr Join
(Select
vwGenPatApptInfo.Appt_Sched_Location_Descr,
Count(Distinct vwGenPatApptInfo.Patient_ID) As TotalPat
From
vwGenPatApptInfo vwGenPatApptInfo Inner Join
vwGenPatInfo vwGenPatInfo On vwGenPatInfo.Patient_ID =
vwGenPatApptInfo.Patient_ID
Where
vwGenPatApptInfo.Appt_Acknowledged_Date Between '2012-09-01 00:00:01' And
'2012-09-30 23:59:59' And
vwGenPatApptInfo.Appt_Sched_Location_Abbr Not In ('OFPAIN', 'DMDIAB',
'OFHMP', 'OFSPINEN')
Group By
vwGenPatApptInfo.Appt_Sched_Location_Descr) q3
On q3.Appt_Sched_Location_Descr = q1.Appt_Sched_Location_Descr
Order By
q1.GotMail * 1.0 / q3.TotalPat Desc |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48025 Posts |
Posted - 11/14/2012 : 07:08:22
|
the view might be one renamed in excel?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Dale45039
Starting Member
41 Posts |
Posted - 11/14/2012 : 08:03:12
|
Would that explain the "Invalid object name 'vwGenPatApptInfo'." error message? I'm not quite sure what you mean.
quote:
Originally posted by visakh16
the view might be one renamed in excel?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48025 Posts |
Posted - 11/15/2012 : 05:44:14
|
quote:
Originally posted by Dale45039
Would that explain the "Invalid object name 'vwGenPatApptInfo'." error message? I'm not quite sure what you mean.
quote:
Originally posted by visakh16
the view might be one renamed in excel?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
check your datasource definition in excel
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Topic |
|
Aggregate and Groupby
