SQL SP, getting data from 3 Tables RSS

• • Reply
  

  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 

  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
  
  Mark post(s) as "Answer" that helped you
• • Reply
  

  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....)

  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
• • Reply
  

  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>
  Ayyappan.CNN,
  HAND IN HAND IND., Asst. Project Director, Admin and IT,Systems, TN,India
  
  Mark post(s) as "Answer" that helped you
• • Reply
  

  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'.

  Ayyappan.CNN,
  HAND IN HAND IND., Asst. Project Director, Admin and IT,Systems, TN,India
  
  Mark post(s) as "Answer" that helped you
• • Reply
  

  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

  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

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Edited by sandeepmittal11 on Dec 25, 2012 12:49 PM
  • Marked as answer by ayyappan.cnn on Dec 29, 2012 02:22 PM
• • Reply
  

  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. 

  Ayyappan.CNN,
  HAND IN HAND IND., Asst. Project Director, Admin and IT,Systems, TN,India
  
  Mark post(s) as "Answer" that helped you