SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Aggregate and Groupby
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

Dale45039
Starting Member

41 Posts

Posted - 07/13/2012 :  10:02:00  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/13/2012 :  10:05:21  Show Profile  Reply with Quote
post the full code without which i cant understand what you're doing wrong

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

Go to Top of Page

Dale45039
Starting Member

41 Posts

Posted - 07/13/2012 :  10:42:49  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/13/2012 :  10:51:40  Show Profile  Reply with Quote
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/

Go to Top of Page

Dale45039
Starting Member

41 Posts

Posted - 07/13/2012 :  11:11:04  Show Profile  Reply with Quote
Found the problem. "(SELECT Appt_Sched_Location_Descr" needs to be "(SELECT vwGenCustApptInfo.Appt_Sched_Location_Descr"
Go to Top of Page

Dale45039
Starting Member

41 Posts

Posted - 07/13/2012 :  11:13:43  Show Profile  Reply with Quote
visakh16, thank you for your patience and generous help!
Go to Top of Page

Dale45039
Starting Member

41 Posts

Posted - 07/13/2012 :  11:23:14  Show Profile  Reply with Quote
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'
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/13/2012 :  23:17:47  Show Profile  Reply with Quote

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/

Go to Top of Page

Dale45039
Starting Member

41 Posts

Posted - 07/16/2012 :  08:22:10  Show Profile  Reply with Quote
There you go again - rescuing a newbie Thanks you visakh16!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/16/2012 :  09:44:46  Show Profile  Reply with Quote
welcome...as always

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

Go to Top of Page

Dale45039
Starting Member

41 Posts

Posted - 11/09/2012 :  14:09:25  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/14/2012 :  07:08:22  Show Profile  Reply with Quote
the view might be one renamed in excel?

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

Go to Top of Page

Dale45039
Starting Member

41 Posts

Posted - 11/14/2012 :  08:03:12  Show Profile  Reply with Quote
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/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/15/2012 :  05:44:14  Show Profile  Reply with Quote
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/

Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000