help with query - too challenging for me!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

help with query - too challenging for me!

Author

Topic

PGG_CA
Starting Member

24 Posts

Posted - 2013-10-11 : 18:28:49

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

52326 Posts

Posted - 2013-10-12 : 04:59:06

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

PGG_CA
Starting Member

24 Posts

Posted - 2013-10-15 : 11:46:47

Thanks visakh16.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-16 : 02:17:51

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-16 : 02:18:19

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources