create table Student_Master (Student_ID int,Section int, Student_Name varchar(100))
insert into Student_Master
select 101,1,'Johnson' union all
select 102,1,'Murugan' union all
select 103,3,'Reena' union all
select 104,2,'Kumar'
create table Account_Head (Acct_ID int, Income_Head varchar(100))
insert into Account_Head
select 1, 'Tution Fee' union all
select 2, 'Yoga Fee' union all
select 3, 'Bus Fee'
create table Transaction_Master (Txn_Id int, Student_Id Int, Income_Acct_head Int, Txn_Date datetime, Income_Amount int)
insert into Transaction_Master
select 1, 101,1,'2011/01/05',300 union all
select 2, 101,2,'2011/01/10',100 union all
select 3, 101,3,'2011/01/07',500 union all
select 4, 102,1,'2011/01/04',350 union all
select 5, 102,2,'2011/01/12',150 union all
select 6, 103,1,'2011/01/06',300 union all
select 7, 101,1,'2011/02/10',300 union all
select 8, 101,2,'2011/02/10',100 union all
select 9, 101,3,'2011/02/11',150 union all
select 10, 102,1,'2011/02/10',300 union all
select 11, 102,2,'2011/02/10',200 union all
select 12, 101,1,'2011/03/10',100 union all
select 13, 101,2,'2011/03/10',100 union all
select 14, 101,3,'2011/03/11',50
I’m expecting results like,
Report As of March 2011
Johnson
Murugan
Reena
Acct_ID
Acct_Head
As of Last Month
Current Month
Total
As of Last Month
Current Month
Total
Section 1 - Total
As of Last Month
Current Month
Total
1
1.Tution Fee
600
100
700
650
650
1350
300
300
2
2.Yoga Fee
200
100
300
350
350
650
3
3.Bus Fee
650
50
700
If I take Report As of April 2011 I’m expecting results like
Johnson
Murugan
Reena
Acct_ID
Acct_Head
As of Last Month
Current Month
Total
As of Last Month
Current Month
Total
Section 1 - Total
As of Last Month
Current Month
Total
1
1.Tution Fee
700
700
650
650
1350
300
300
2
2.Yoga Fee
300
300
350
350
650
3
3.Bus Fee
700
700
Thanks in advance
Ayyappan.CNN,
HAND IN HAND IND., Asst. Project Director, Admin and IT,Systems, TN,India
Use below query logic to fetch data and then make group by on Student name in any of the reports which you are using, (Crytal, RDLC or SSRS etc....)
select SM.Student_ID ,SM.Section, SM.Student_Name,AH.Income_Head ,SUM(Income_Amount)
(select SUM(Income_Amount) from Transaction_Master TM_Last where TM_Last.Student_Id = TM.Student_Id and TM_Last.Acct_ID = TM.Income_Acct_head and month(Txn_Date) = MONTH(getdate)-1 and YEAR(Txn_Date)= YEAR(Txn_Date)) as 'As of Last Month' -- required to bit change to handle if current month is jan
(select SUM(Income_Amount) from Transaction_Master TM_Last where TM_Last.Student_Id = TM.Student_Id and TM_Last.Acct_ID = TM.Income_Acct_head and month(Txn_Date) = MONTH(getdate) and YEAR(Txn_Date)= YEAR(Txn_Date)) as 'As of Last Month'
from Student_Master SM
inner join Transaction_Master TM on TM.Student_Id = SM.Student_Id
inner join Account_Head AH on AH.Acct_ID = TM.Income_Acct_head
group by SM.Student_ID ,SM.Section, SM.Student_Name,AH.Income_Head
My Tech Blogs MCPD Enterprise and Web Application
MCTS Web, Window and Enterprise Application
<div>Msg 156, Level 15, State 1, Line 3</div> <div>Incorrect syntax near the keyword 'as'.</div> <div>Msg 156, Level 15, State 1, Line 4</div> <div>Incorrect syntax near the keyword 'as'.</div>
Ayyappan.CNN,
HAND IN HAND IND., Asst. Project Director, Admin and IT,Systems, TN,India
Are you using any reporting tool? If yes, then i would suggest you to use the cross tab functionality of it.
Use the below query to produce the desired resultset and then use cross tab functionality of the reporting tool to display the data in desired format
create table #Student_Master (Student_ID int,Section int, Student_Name varchar(100))
insert into #Student_Master
select 101,1,'Johnson' union all
select 102,1,'Murugan' union all
select 103,3,'Reena' union all
select 104,2,'Kumar'
create table #Account_Head (Acct_ID int, Income_Head varchar(100))
insert into #Account_Head
select 1, 'Tution Fee' union all
select 2, 'Yoga Fee' union all
select 3, 'Bus Fee'
create table #Transaction_Master (Txn_Id int, Student_Id Int, Income_Acct_head Int, Txn_Date datetime, Income_Amount int)
insert into #Transaction_Master
select 1, 101,1,'2011/01/05',300 union all
select 2, 101,2,'2011/01/10',100 union all
select 3, 101,3,'2011/01/07',500 union all
select 4, 102,1,'2011/01/04',350 union all
select 5, 102,2,'2011/01/12',150 union all
select 6, 103,1,'2011/01/06',300 union all
select 7, 101,1,'2011/02/10',300 union all
select 8, 101,2,'2011/02/10',100 union all
select 9, 101,3,'2011/02/11',150 union all
select 10, 102,1,'2011/02/10',300 union all
select 11, 102,2,'2011/02/10',200 union all
select 12, 101,1,'2011/03/10',100 union all
select 13, 101,2,'2011/03/10',100 union all
select 14, 101,3,'2011/03/11',50
declare @curr_period int
set @curr_period = year(getdate())*100+month(getdate())
set @curr_period = 201103
select A.Acct_ID, cast(A.Acct_ID as varchar)+'.'+A.Income_Head As Acc_Head, S.Student_Name
, [Current Month] = sum(case when year(Txn_Date)*100+month(Txn_Date) < @curr_period then Income_Amount else 0 end)
, [As of last Month] = sum(case when year(Txn_Date)*100+month(Txn_Date) = @curr_period then Income_Amount else 0 end)
, [Total] = sum(Income_Amount)
from #Account_Head A
inner join #Transaction_Master T on A.Acct_ID = T.Income_Acct_head
inner join #Student_Master S on S.Student_ID = T.Student_Id
group by A.Acct_ID, cast(A.Acct_ID as varchar)+'.'+A.Income_Head, S.Student_Name
drop table #Student_Master
drop table #Account_Head
drop table #Transaction_Master
ayyappan.CNN
Participant
870 Points
326 Posts
SQL SP, getting data from 3 Tables
Dec 25, 2012 10:56 AM|LINK
Hi.. This is my SQL Tables
I’m expecting results like,
Report As of March 2011
Johnson
Murugan
Reena
Acct_ID
Acct_Head
As of Last Month
Current Month
Total
As of Last Month
Current Month
Total
Section 1 - Total
As of Last Month
Current Month
Total
1
1.Tution Fee
600
100
700
650
650
1350
300
300
2
2.Yoga Fee
200
100
300
350
350
650
3
3.Bus Fee
650
50
700
If I take Report As of April 2011 I’m expecting results like
Johnson
Murugan
Reena
Acct_ID
Acct_Head
As of Last Month
Current Month
Total
As of Last Month
Current Month
Total
Section 1 - Total
As of Last Month
Current Month
Total
1
1.Tution Fee
700
700
650
650
1350
300
300
2
2.Yoga Fee
300
300
350
350
650
3
3.Bus Fee
700
700
Thanks in advance
HAND IN HAND IND., Asst. Project Director, Admin and IT,Systems, TN,India
Mark post(s) as "Answer" that helped you
amitpatel.it
Star
7908 Points
1856 Posts
Re: SQL SP, getting data from 3 Tables
Dec 25, 2012 11:13 AM|LINK
Use below query logic to fetch data and then make group by on Student name in any of the reports which you are using, (Crytal, RDLC or SSRS etc....)
MCPD Enterprise and Web Application
MCTS Web, Window and Enterprise Application
ayyappan.CNN
Participant
870 Points
326 Posts
Re: SQL SP, getting data from 3 Tables
Dec 25, 2012 11:32 AM|LINK
Hi... I tried but it shows following err.
<div>Msg 156, Level 15, State 1, Line 3</div> <div>Incorrect syntax near the keyword 'as'.</div> <div>Msg 156, Level 15, State 1, Line 4</div> <div>Incorrect syntax near the keyword 'as'.</div>HAND IN HAND IND., Asst. Project Director, Admin and IT,Systems, TN,India
Mark post(s) as "Answer" that helped you
ayyappan.CNN
Participant
870 Points
326 Posts
Re: SQL SP, getting data from 3 Tables
Dec 25, 2012 11:32 AM|LINK
sorry ! following err.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'as'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'as'.
HAND IN HAND IND., Asst. Project Director, Admin and IT,Systems, TN,India
Mark post(s) as "Answer" that helped you
sandeepmitta...
Contributor
6767 Points
1057 Posts
Re: SQL SP, getting data from 3 Tables
Dec 25, 2012 12:47 PM|LINK
Are you using any reporting tool? If yes, then i would suggest you to use the cross tab functionality of it.
Use the below query to produce the desired resultset and then use cross tab functionality of the reporting tool to display the data in desired format
Sandeep Mittal | My Blog - IT Developer Zone
ayyappan.CNN
Participant
870 Points
326 Posts
Re: SQL SP, getting data from 3 Tables
Dec 25, 2012 02:42 PM|LINK
Yes Mr.Sandeep, now am trying that... Let us know soon.
HAND IN HAND IND., Asst. Project Director, Admin and IT,Systems, TN,India
Mark post(s) as "Answer" that helped you