May 26, 2011 at 1:35 pm
Hi,
I have three tables
T1 with columns: JoiningYear,ID, Type ID, DeptID
T2 with columns: TypeID, Type code
T3 with Columns: DeptID, Deptname
and i am using 3 CTE's to calculate count of ID's using these table with just difference in where clause.
;with cte1 as
(
select
JoiningYear
,Count(ID) as total_Count
from t1 inner join t2 on t1.typeid = t2.typeid
innerjoin t3 on t1.deptID = t3.deptID
where t2.typecode in('a','b',c')
and t3.deptname in ('a','b','c')
)
,cte2 as
(
select
JoiningYear
,Count(ID) as total_Count_2
from t1 inner join t2 on t1.typeid = t2.typeid
innerjoin t3 on t1.deptID = t3.deptID
where t2.typecode in('a','b',c')
and t3.deptname in ('x','y','z')
)
,cte3 as
(select
JoiningYear
,Count(ID) as total_Count_3
from t1 inner join t2 on t1.typeid = t2.typeid
innerjoin t3 on t1.deptID = t3.deptID
where t2.typecode in('a','b',c')
and t3.deptname in ('m','n','c')
)
select
JoiningYear
,total_Count
,total_Count /total_Count_2 as column3
,total_Count /total_Count_3 as column4
from cte1 left outer join cte2 on cte1.JoiningYear = cte2.JoiningYear
left outer join on cte1.JoiningYear = cte3.JoiningYear
group by joiningyear
So here in all cte's in have same slecet columns but different where conditions.
nad then from these cte's final calculations are done.
Is there is any way that i can make these where clause dynamic within in query itself, but not by passing these variable as done while executing the stored procedure.
Thanks in advance,
Punia
May 26, 2011 at 7:54 pm
I assume that you don't really mean "Dynamic SQL". I assume you mean how do you do this in a single pass instead of 3.
The answer is simple... Build a CROSS TAB.
WITH
ctePreAgg AS
(
SELECT JoiningYear,
Total_Count = SUM(CASE WHEN t3.DeptName IN ('a','b','c') THEN 1 ELSE 0 END),
Total_Count2 = SUM(CASE WHEN t3.DeptName IN ('x','y','z') THEN 1 ELSE 0 END),
Total_Count3 = SUM(CASE WHEN t3.DeptName IN ('m','n','c') THEN 1 ELSE 0 END)
FROM T1 t1
INNER JOIN T2 t2 ON t1.TypeID = t2.TypeID
INNER JOIN T3 t3 ON t1.DeptID = t3.DeptID
WHERE t2.TypeCode IN ('a','b','c')
GROUP BY JoiningYear
)
SELECT JoiningYear,
Total_Count,
Column3 = Total_Count / Total_Count2,
Column4 = Total_Count / Total_Count3
FROM ctePreAgg
;
As a sidebar (I know it's only sample code but have to point these things out to be sure), when you have any joins in a SELECT, you should always use table aliases on all the tables and all the columns so the next person trying to troubleshoot the code doesn't have to lookup which tables the columns are in. It can also shorten recompile time a bit because SQL Server won't have to figure it out, either.
The same holds true for the use of the two part naming convention. If you don't spell it out, SQL Server tries to find the table in the Master DB first. When it doesn't find it there, it looks in the local database as whatever user you're (or the app) logged in as. When it doesn't find that, then it finally looks for the table name using dbo as the schema. Again... it's only a bit of time but enough bits can add up to a real savings especially over time.
I didn't add the aliases to the column names missing them because I just don't know what they should be.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply