How to use sql join with multiple temp tables

Hi,

You can achieve your requirement(result set) using
CommonTableExpression - http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx
Pivot Function - http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
Inner Join - http://technet.microsoft.com/en-us/library/ms190014(v=sql.105).aspx

Please find the sample implementation

WITH cte AS (
    SELECT
		--Select the course name
        O.Course_Name, 
        --Select the course Count for a Year Till date
        O.Course_count AS Course_count_Year_Till_date ,
         --Select the course Count for a Month Till date
        P.Course_count AS Course_count_Month_Till_date ,
         --Select the course Count for a Till Today
        Q.Course_count AS Course_Name_Course_count_Today_date
    FROM #testTable1 AS O
		-- Join the Second table
        inner join #testTable2 AS P on P.Course_Name = O.Course_Name
        -- Join the Third table
        inner join #testTable3 AS Q on Q.Course_Name = O.Course_Name
    GROUP BY 
		O.Course_count,
		O.Course_Name,
		Q.Course_count, 
		P.Course_count
)
SELECT
	--Select the course name
    Course_Name,
    --Select the course Count for a Year Till date
    Course_count_Year_Till_date AS 'Course_count[Year Till date]',
    --Select the course Count for a Month Till date
    Course_count_Month_Till_date AS 'Course_count[Month Till date]',
    --Select the course Count for a Till Today
    Course_Name_Course_count_Today_date AS 'Course_count[Today date]'
FROM cte
GROUP BY 
	Course_Name,
	Course_count_Year_Till_date,
	Course_count_Month_Till_date,
	Course_Name_Course_count_Today_date

You can find a working sample demo of above code in below link

http://sqlfiddle.com/#!3/af847/5

Thanks,
A2H
My Blog

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

Very thanks for your updating, But I unable to use the mentioned CTE code in below,
Because I'm not insert any record as manually, It is fetching data automatically from the table "T_Student_Course_Detail"

Please let me know how can I use CTE in the 3 tables @testTable, @testTable1, @testTable2

Declare @testTable Table
(
S_Course_Name VARCHAR(200),
I_Course_ID INT
)
Insert into @testTable
select CM.S_Course_Name AS [Course Name]
,COUNT(TSCD.I_Course_ID) as [Month Till Date(MDT)]
from T_Student_Course_Detail TSCD JOIN T_Course_Master CM
ON (TSCD.I_Course_ID = CM.I_Course_ID) where TSCD.I_Status=1
and TSCD.Dt_Valid_From >= DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) AND TSCD.Dt_Valid_From <= GETDATE()
group by CM.S_Course_Name
,TSCD.I_Course_ID


Declare @testTable1 Table
(
S_Course_Name VARCHAR(200),
I_Course_ID INT
)
Insert into @testTable1
select CM.S_Course_Name AS [Course Name]
,COUNT(TSCD.I_Course_ID) AS [Year Till Date(YDT)]
--,TSCD.I_Course_ID
from T_Student_Course_Detail TSCD JOIN T_Course_Master CM
ON (TSCD.I_Course_ID = CM.I_Course_ID) where TSCD.I_Status=1
and TSCD.Dt_Valid_From >= CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101) AND TSCD.Dt_Valid_From < =GETDATE()
group by CM.S_Course_Name


Declare @testTable2 Table
(
S_Course_Name VARCHAR(200),
I_Course_ID INT
)
Insert into @testTable2
select CM.S_Course_Name AS [Course Name]
,COUNT(TSCD.I_Course_ID) as [Month Till Date(MDT)]
from T_Student_Course_Detail TSCD JOIN T_Course_Master CM
ON (TSCD.I_Course_ID = CM.I_Course_ID) where TSCD.I_Status=1
and CONVERT(varchar(25),DATEADD(dd, 0, Datediff(dd,0,TSCD.Dt_Valid_From))) = CONVERT(varchar(25),DATEADD(dd, 0, Datediff(dd,0,GETDATE())))
--and TSCD.I_Course_ID= 2499
group by CM.S_Course_Name
,TSCD.I_Course_ID

WITH cte AS (
SELECT
--Select the course name
O.S_Course_Name,
--Select the course Count for a Year Till date
O.I_Course_ID AS Course_count_Year_Till_date ,
--Select the course Count for a Month Till date
P.I_Course_ID AS Course_count_Month_Till_date ,
--Select the course Count for a Till Today
Q.I_Course_ID AS Course_Name_Course_count_Today_date
FROM @testTable AS O
-- Join the Second table
inner join @testTable1 AS P on P.S_Course_Name = O.S_Course_Name
-- Join the Third table
inner join @testTable2 AS Q on Q.S_Course_Name = O.S_Course_Name
GROUP BY
O.I_Course_ID,
O.S_Course_Name,
Q.I_Course_ID,
P.I_Course_ID
)
SELECT
--Select the course name
S_Course_Name,
--Select the course Count for a Year Till date
Course_count_Year_Till_date AS 'Course_count[Year Till date]',
--Select the course Count for a Month Till date
Course_count_Month_Till_date AS 'Course_count[Month Till date]',
--Select the course Count for a Till Today
Course_Name_Course_count_Today_date AS 'Course_count[Today date]'
FROM cte
GROUP BY
S_Course_Name,
Course_count_Year_Till_date,
Course_count_Month_Till_date,
Course_Name_Course_count_Today_date

Karthik

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

--Sample data 

DECLARE @testTable1 table (Course_Name varchar(200), [Year Till Date(YDT)]  INT)
insert  @testTable1 
select 'FinalAccount', 150 union all
select 'CPT', 80  union all
select 'CPT-CA', 60  union all
select 'IPCC', 70  union all
select 'IPC_Account', 100 

declare @testTable2 table (Course_Name varchar(200),[Month Till Date(MDT)] int)
insert  @testTable2 
select 'FinalAccount', 150 union all
select 'CPT', 80  union all
select 'CPT-CA', 61  union all
select 'IPCC', 71  union all
select 'IPC_Account', 101 

declare @testTable3 table (Course_Name varchar(200),[Today date] int)
insert  @testTable3 
select 'FinalAccount', 1  union all
select 'CPT', 2  union all
select 'CPT-CA', 3  union all
select 'IPCC', 4  union all
select 'IPC_Account', 0 


--Expected OUTPUT 
--Course_Name Course_count[Year Till date] Course_count[Month Till date] Course_count[Today date] 
--FinalAccount 150 150 1 
--CPT 80 80 2 
--CPT-CA 60 61 3 
--IPCC 70 71 4 
--IPC_Account 100 101 0 


--Query 
SELECT t1.Course_Name, t1.[Year Till Date(YDT)], t2.[Month Till Date(MDT)], t3.[Today date] 
FROM @testTable1 t1
JOIN @testTable2 t2 ON t1.Course_Name = t2.Course_Name
JOIN @testTable3 t3 ON t2.Course_Name = t3.Course_Name

 

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

"select * from @testTable1" table, It has fetching 95 records
"select * from @testTable2" table, It has fetching 57 records
"select * from @testTable3" table, It has fetching 0 records

--Expected OUTPUT
--Course_Name Course_count[Year Till date] Course_count[Month Till date] Course_count[Today date]
--FinalAccount 150 150 1
--CPT 80 80 2
--CPT-CA 60 61 3
--IPCC 70 71 4
--IPC_Account 100 101 0
--.
--.
--.
--95th Name 200 10 0

Ex : The mentioned example return 0 record.
DECLARE @testTable1 table (Course_Name varchar(200), [Year Till Date(YDT)] INT)
insert @testTable1
select CM.S_Course_Name AS [Course Name]
,COUNT(TSCD.I_Course_ID) as [Year Till Date(YDT)]
from T_Student_Course_Detail TSCD JOIN T_Course_Master CM
ON (TSCD.I_Course_ID = CM.I_Course_ID) where TSCD.I_Status=1
and TSCD.Dt_Valid_From >= DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) AND TSCD.Dt_Valid_From <= GETDATE()
group by CM.S_Course_Name
,TSCD.I_Course_ID

declare @testTable2 table (Course_Name varchar(200),[Month Till Date(MDT)] int)
insert @testTable2
select CM.S_Course_Name AS [Course Name]
,COUNT(TSCD.I_Course_ID) AS [Month Till Date(MDT)]
from T_Student_Course_Detail TSCD JOIN T_Course_Master CM
ON (TSCD.I_Course_ID = CM.I_Course_ID) where TSCD.I_Status=1
and TSCD.Dt_Valid_From >= CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101) AND TSCD.Dt_Valid_From < =GETDATE()
group by CM.S_Course_Name

declare @testTable3 table (Course_Name varchar(200),[Today date] int)
Insert into @testTable3
select CM.S_Course_Name AS [Course Name]
,COUNT(TSCD.I_Course_ID) as [Today Date]
from T_Student_Course_Detail TSCD JOIN T_Course_Master CM
ON (TSCD.I_Course_ID = CM.I_Course_ID) where TSCD.I_Status=1
and CONVERT(varchar(25),DATEADD(dd, 0, Datediff(dd,0,TSCD.Dt_Valid_From))) = CONVERT(varchar(25),DATEADD(dd, 0, Datediff(dd,0,GETDATE())))
group by CM.S_Course_Name
,TSCD.I_Course_ID

--Query
SELECT t1.Course_Name, t1.[Year Till Date(YDT)], t2.[Month Till Date(MDT)], t3.[Today date]
FROM @testTable1 t1
JOIN @testTable2 t2 ON t1.Course_Name = t2.Course_Name
JOIN @testTable3 t3 ON t2.Course_Name = t3.Course_Name

Karthik

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Declare @testTable1 Table
(
S_Course_Name VARCHAR(200),
I_Course_ID INT
)
Insert into @testTable1
select CM.S_Course_Name AS [Course Name]
,COUNT(TSCD.I_Course_ID) as [Month Till Date(MDT)]
from T_Student_Course_Detail TSCD JOIN T_Course_Master CM
ON (TSCD.I_Course_ID = CM.I_Course_ID) where TSCD.I_Status=1
and TSCD.Dt_Valid_From >= DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) AND TSCD.Dt_Valid_From <= GETDATE()
group by CM.S_Course_Name
,TSCD.I_Course_ID
order by CM.S_Course_Name

select * from @testTable1

Output :
Bank PO- All Subject 203
CA CPT 7
CA CPT ESP 6
CS CAP MKT & SEC LAW 62
CS CO ACC & AUD PRAC 8
CS COMPANY LAW 24
CS COST & MGT ACC 23
CS ECO & COMM LAW 20
CS Foundation All Sub 58
CS IND LAB & GEN LAW 28
CS MODULE I 47
CS MODULE I & II 19
CS MODULE II 41
CS PRO ALL SUB 5
CS PRO CR 29
CS PRO CSP 9
CS PRO CSP&CR 3
CS PRO Drafting 7
CS PRO FM 43
CS PRO GR1 4
CS PRO GR2 1
CS PRO GR3 9
CS PRO TAX 45
CS SECURITY LAW 1
CS TAX LAW & PRAC 27
CSP GS-All & CSAT 57
CSP Main GEOGRAPHY 1
CSP Main GS 4
CSP Main Pub Admin 9
CSP Pre Both Papers 60
CSP Pre Paper1 35
CSP Pre Paper1&2-SM 69
CSP Pre Paper2 3
Current Affair 12
FINAL ACCOUNT 628
FINAL AUDIT 96
FINAL C-GR1 16
FINAL C-GR2 12
FINAL COSTING 100
FINAL DIRECT TAX 63
FINAL INDIRECT TAX 283
FINAL ISCA 180
FINAL LAW 65
FINAL SFM 84
Gen. Competition 105
IPBGR 91
IPCC ACCOUNT 3002
IPCC ADV ACCOUNT 1291
IPCC AUDIT 141
IPCC C-GR1 966
IPCC C-GR2 227
IPCC CA 158
IPCC CAFM 471
IPCC FM 64
IPCC INCOME TAX 159
IPCC ITSM 317
IPCC LAW 380
SIP- IPCC CAFM 25
SIP-CA CPT 47
SIP-FINAL ACCOUNT 29
SIP-FINAL AUDIT 23
SIP-FINAL BOTH GROUP 9
SIP-FINAL C-GR1 6
SIP-FINAL C-GR2 1
SIP-FINAL COSTING 11
SIP-FINAL DT 13
SIP-FINAL DT & IDT 2
SIP-FINAL IDT 15
SIP-FINAL ISCA 11
SIP-FINAL LAW 7
SIP-FINAL OR 17
SIP-FINAL SFM 38
SIP-IPCC ACCOUNT 13
SIP-IPCC ADV ACCOUNT 23
SIP-IPCC AUDIT 11
SIP-IPCC BOTH GROUP 32
SIP-IPCC C-GR1 21
SIP-IPCC C-GR2 10
SIP-IPCC FM 1
SIP-IPCC INCOME & SERVICE TAX 14
SIP-IPCC ITSM 5
SIP-IPCC LAW 9
SIP-IPCC SM 1
SSC All Sub 334
SSC QA 1
SSP L1 4
ST&EP_CBSE_Class 10_Science & Maths 4
ST&EP_CBSE_Class 12_Chemistry 6
ST&EP_CBSE_Class 12_Physics_Chemistry&Maths 1
ST&EP_CBSE_Class 12_Physics_Chemistry_Maths&Biology 1
ST&EP_CBSE_Class 9_Science & Maths 1
ST&EP_ICSE_Class 10_Science & Maths 1
ST&EP_State_Class 10_Science_Maths_S.Science&English 1
ST&EP_State_Class 12_Physics_Chemistry_Maths&Biology 1
ST&EP_State_Class 12_Physics_Chemistry_Maths&Electronics 1

---------------------------------------

Declare @testTable2 Table
(
S_Course_Name VARCHAR(200),
I_Course_ID INT
)
Insert into @testTable2
select CM.S_Course_Name AS [Course Name]
,COUNT(TSCD.I_Course_ID) AS [Year Till Date(YDT)]
from T_Student_Course_Detail TSCD JOIN T_Course_Master CM
ON (TSCD.I_Course_ID = CM.I_Course_ID) where TSCD.I_Status=1
and TSCD.Dt_Valid_From >= CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101) AND TSCD.Dt_Valid_From < = GETDATE()
group by CM.S_Course_Name

select * from @testTable2
Output :
Bank PO- All Subject 3
CA CPT 4
CS CAP MKT & SEC LAW 1
CS COMPANY LAW 1
CS ECO & COMM LAW 1
CS Foundation All Sub 2
CS PRO CR 2
CS PRO CSP&CR 1
CS PRO FM 2
CS PRO TAX 2
CSP GS-All & CSAT 14
CSP Main GS 4
CSP Main Pub Admin 2
CSP Pre Both Papers 14
CSP Pre Paper1 3
CSP Pre Paper1&2-SM 21
Current Affair 4
FINAL ACCOUNT 47
FINAL C-GR1 1
FINAL COSTING 3
FINAL DIRECT TAX 1
FINAL INDIRECT TAX 11
FINAL ISCA 6
FINAL LAW 1
Gen. Competition 5
IPBGR 5
IPCC ACCOUNT 114
IPCC ADV ACCOUNT 65
IPCC AUDIT 21
IPCC C-GR1 101
IPCC C-GR2 34
IPCC CA 13
IPCC CAFM 72
IPCC FM 8
IPCC INCOME TAX 34
IPCC ITSM 28
IPCC LAW 48
SIP- IPCC CAFM 2
SIP-FINAL ACCOUNT 2
SIP-FINAL AUDIT 2
SIP-FINAL BOTH GROUP 1
SIP-FINAL IDT 1
SIP-FINAL OR 1
SIP-FINAL SFM 1
SIP-IPCC ACCOUNT 1
SIP-IPCC ADV ACCOUNT 1
SIP-IPCC AUDIT 2
SIP-IPCC BOTH GROUP 1
SIP-IPCC C-GR1 2
SIP-IPCC C-GR2 3
SIP-IPCC FM 1
SIP-IPCC INCOME & SERVICE TAX 3
SIP-IPCC ITSM 1
SIP-IPCC LAW 2
SSC All Sub 6
ST&EP_CBSE_Class 10_Science & Maths 1
ST&EP_CBSE_Class 12_Chemistry 2

----------------------------------------------
Declare @testTable3 Table
(
S_Course_Name VARCHAR(200),
I_Course_ID INT
)
Insert into @testTable3
select CM.S_Course_Name AS [Course Name]
,COUNT(TSCD.I_Course_ID) as [Month Till Date(MDT)]
from T_Student_Course_Detail TSCD JOIN T_Course_Master CM
ON (TSCD.I_Course_ID = CM.I_Course_ID) where TSCD.I_Status=1
and CONVERT(varchar(25),DATEADD(dd, 0, Datediff(dd,0,TSCD.Dt_Valid_From))) = CONVERT(varchar(25),DATEADD(dd, 0, Datediff(dd,0,GETDATE())))
group by CM.S_Course_Name
,TSCD.I_Course_ID

select * from @testTable3

Output :
Bank PO- All Subject 3

--Final Join query :
select distinct(t.S_Course_Name), t.I_Course_ID As [Year Till Date(YDT)],
t1.I_Course_ID As [Month Till Date(MTD)],
t2.I_Course_ID As [Today Date] from @testTable1 t
LEFT JOIN @testTable2 t1
ON( t.I_Course_ID = t1.I_Course_ID)
LEFT JOIN @testTable3 t2
ON( t2.I_Course_ID = t.I_Course_ID)

Output getting wrong:
Bank PO- All Subject 203 NULL NULL
CA CPT 7 NULL NULL
CA CPT ESP 6 6 NULL
CS CAP MKT & SEC LAW 62 NULL NULL
CS CO ACC & AUD PRAC 8 8 NULL
CS COMPANY LAW 24 NULL NULL
CS COST & MGT ACC 23 NULL NULL
CS ECO & COMM LAW 20 NULL NULL
CS Foundation All Sub 58 NULL NULL
CS IND LAB & GEN LAW 28 28 NULL
CS MODULE I 47 47 NULL
CS MODULE I & II 19 NULL NULL
CS MODULE II 41 NULL NULL
CS PRO ALL SUB 5 5 NULL
CS PRO CR 29 NULL NULL
CS PRO CSP 9 NULL NULL
CS PRO CSP&CR 3 3 NULL
CS PRO Drafting 7 NULL NULL
CS PRO FM 43 NULL NULL
CS PRO GR1 4 4 NULL
CS PRO GR2 1 1 NULL
CS PRO GR3 9 NULL NULL
CS PRO TAX 45 NULL NULL
CS SECURITY LAW 1 1 NULL
CS TAX LAW & PRAC 27 NULL NULL
CSP GS-All & CSAT 57 NULL NULL
CSP Main GEOGRAPHY 1 1 NULL
CSP Main GS 4 4 NULL
CSP Main Pub Admin 9 NULL NULL
CSP Pre Both Papers 60 NULL NULL
CSP Pre Paper1 35 NULL NULL
CSP Pre Paper1&2-SM 69 NULL NULL
CSP Pre Paper2 3 3 NULL
Current Affair 12 NULL NULL
FINAL ACCOUNT 628 NULL NULL
FINAL AUDIT 96 NULL NULL
FINAL C-GR1 16 NULL NULL
FINAL C-GR2 12 NULL NULL
FINAL COSTING 100 NULL NULL
FINAL DIRECT TAX 63 NULL NULL
FINAL INDIRECT TAX 283 NULL NULL
FINAL ISCA 180 NULL NULL
FINAL LAW 65 65 NULL
FINAL SFM 84 NULL NULL
Gen. Competition 105 NULL NULL
IPBGR 91 NULL NULL
IPCC ACCOUNT 3002 NULL NULL
IPCC ADV ACCOUNT 1291 NULL NULL
IPCC AUDIT 141 NULL NULL
IPCC C-GR1 966 NULL NULL
IPCC C-GR2 227 NULL NULL
IPCC CA 158 NULL NULL
IPCC CAFM 471 NULL NULL
IPCC FM 64 NULL NULL
IPCC INCOME TAX 159 NULL NULL
IPCC ITSM 317 NULL NULL
IPCC LAW 380 NULL NULL
SIP- IPCC CAFM 25 NULL NULL
SIP-CA CPT 47 47 NULL
SIP-FINAL ACCOUNT 29 NULL NULL
SIP-FINAL AUDIT 23 NULL NULL
SIP-FINAL BOTH GROUP 9 NULL NULL
SIP-FINAL C-GR1 6 6 NULL
SIP-FINAL C-GR2 1 1 NULL
SIP-FINAL COSTING 11 11 NULL
SIP-FINAL DT 13 13 NULL
SIP-FINAL DT & IDT 2 2 NULL
SIP-FINAL IDT 15 NULL NULL
SIP-FINAL ISCA 11 11 NULL
SIP-FINAL LAW 7

Karthik

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Hi,
Have you faced any issue when you used my query given above? I have inserted query in sample demo to demonstrate you how it works. The core part of CTE query does not not have any insert query only select query is there.

Thanks,
A2H
My Blog

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Try this once... Let me know result

select CM.S_Course_Name AS [Course Name]
	,COUNT(CASE WHEN TSCD.Dt_Valid_From >= DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) AND TSCD.Dt_Valid_From <= GETDATE() THEN TSCD.I_Course_ID END) as [Month Till Date(MDT)]
	,COUNT(CASE WHEN TSCD.Dt_Valid_From >= CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101) AND TSCD.Dt_Valid_From < = GETDATE() THEN TSCD.I_Course_ID END) AS [Year Till Date(YDT)]
	,COUNT(CASE WHEN CONVERT(varchar(25),DATEADD(dd, 0, Datediff(dd,0,TSCD.Dt_Valid_From))) = CONVERT(varchar(25),DATEADD(dd, 0, Datediff(dd,0,GETDATE()))) THEN TSCD.I_Course_ID END ) as [Month Till Date(MDT)]
from T_Student_Course_Detail TSCD JOIN T_Course_Master CM
ON (TSCD.I_Course_ID = CM.I_Course_ID) where TSCD.I_Status=1 
group by CM.S_Course_Name
,TSCD.I_Course_ID
order by CM.S_Course_Name

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator