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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 help with query - too challenging for me!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

PGG_CA
Starting Member

24 Posts

Posted - 10/11/2013 :  18:28:49  Show Profile  Reply with Quote
table1
student course credit enddate
1 1258 1 2012-10-19
1 1211 1 2011-01-25
1 2512 5 2013-05-23
1 1212 5 2012-10-09
1 2312 1 2013-05-07
2 1223 1 2011-10-01
2 2061 5 2013-04-26
2 2062 5 2013-05-29
2 1906 5 2013-03-05
2 1905 1 2013-01-24

table2
student enrollment_date withdrawl_date unit
1 2011-05-24 2012-08-30 YAC
1 2012-08-31 2012-09-12 CST
1 2012-09-13 2012-10-01 CST
1 2012-10-02 2013-06-06 CST
1 2013-06-07 2013-06-11 CST
1 2013-06-12 2013-06-12 YAC
1 2013-06-13 2013-06-30 CST
1 2013-07-01 2013-10-10 YAC
2 2011-05-29 2012-05-29 RDL
2 2012-05-30 2013-10-01 RDL
2 2013-10-02 2013-10-10 CST

These 2 table scontain my sample data. What I want to do is show the unit and the total credits per unit between a specific period, say Sep 1, 2012 and Aug 27, 2013. I need to check the withdrawl_date from table2 to determine which unit (YAC, CST or RDL) would be credited with the course credits. If the course's enddate occurred when the student is in a particular unit (within an enrollment and a withdawl date) , then that unit gets the credit.

The output should look like this:
YAC 0
CST 12
RDL 16

Thanks for any help.

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/12/2013 :  04:59:06  Show Profile  Reply with Quote

SELECT m.unit,COALESCE(n.totCredit,0) AS Credits
FROM (SELECT DISTINCT unit FROM table2) m
LEFT JOIN 
(

SELECT t2.unit,SUM(t1.credit) AS TotCredit
FROM table1 t1
INNER JOIN table2 t2
ON t1.enddate > t2.enrollment_date AND t1.enddate < t2.withdrawl_date
AND t1.student = t2.student
GROUP BY t2.unit
)n
ON n.unit = m.unit


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

PGG_CA
Starting Member

24 Posts

Posted - 10/15/2013 :  11:46:47  Show Profile  Reply with Quote
Thanks visakh16.

How do I filter the data so that only data between Sep 1, 2012 and Aug 27, 2013 are included
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/16/2013 :  02:17:51  Show Profile  Reply with Quote

SELECT m.unit,COALESCE(n.totCredit,0) AS Credits
FROM (SELECT DISTINCT unit FROM table2) m
LEFT JOIN 
(

SELECT t2.unit,SUM(t1.credit) AS TotCredit
FROM table1 t1
INNER JOIN table2 t2
ON t1.enddate > t2.enrollment_date AND t1.enddate < t2.withdrawl_date
AND t1.student = t2.student
 WHERE t1.enddate > = '20120901' AND t1.enddate < '20130828'
GROUP BY t2.unit
)n
ON n.unit = m.unit


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/16/2013 :  02:18:19  Show Profile  Reply with Quote
Also see

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 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.06 seconds. Powered By: Snitz Forums 2000