calculating on conflicting instruction

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 2000 Forums

Transact-SQL (2000)

calculating on conflicting instruction

Author

Topic

chipembele
Posting Yak Master

106 Posts

Posted - 2007-01-12 : 09:26:57

Hi
I have some code that produces a report to calculate actual enrolments, continuing students, transferred, withdrawn, cancelled, completed and percentage retained which is (continuing + completed)/actual enrolments. The code works great but as usual the goalposts are shifted after signoff.

The figures are based on an academic year which is fair enough but the management now want to calculate the figures based on a date also. They want it to return the numbers for the whole year and since 1st November in this academic year in the same report. This is because any withdrawals before 1st November don’t count against retention.

Is it possible to force the query to return two numbers for each course based on the different dates so there would be extra columns based on the extra query that I’d need to run. One of the columns would show the year as a whole while the other column would just show the data since 01/11/2006. If I can get it to return all the columns in the first instance I can then remove what I don’t need.

This is the code I have so far.

SELECT     TOP 100 PERCENT SD.AcademicYearID, O.Code, O.Name, O.SID, prosolution.dbo.CollegeLevel.Code AS [College Level Code], 
                      prosolution.dbo.CollegeLevel.Name AS [College Level Name], O.TargetStudents, COUNT(E.StudentDetailID) AS [Total Students], 
                      TotalEnrols = SUM(CASE WHEN E.CompletionstatusID = '1' THEN 1 ELSE 0 END) + SUM(CASE WHEN E.CompletionstatusID = '2' THEN 1 ELSE 0 END) 
                      + SUM(CASE WHEN E.CompletionstatusID = '3' THEN 1 ELSE 0 END) + SUM(CASE WHEN E.CompletionstatusID = '4' THEN 1 ELSE 0 END) 
                      + SUM(CASE WHEN E.CompletionstatusID = '7' THEN 1 ELSE 0 END), ContEnrols = COUNT(E.StudentDetailID) 
                      - SUM(CASE WHEN E.CompletionstatusID = '2' THEN 1 ELSE 0 END) - SUM(CASE WHEN E.CompletionstatusID = '3' THEN 1 ELSE 0 END) 
                      - SUM(CASE WHEN E.CompletionstatusID = '4' THEN 1 ELSE 0 END) - SUM(CASE WHEN E.CompletionstatusID = '7' THEN 1 ELSE 0 END), 
                      Completed = SUM(CASE WHEN E.CompletionstatusID = '2' THEN 1 ELSE 0 END), 
                      Withdrawn = SUM(CASE WHEN E.CompletionstatusID = '3' THEN 1 ELSE 0 END), 
                      Transferred = SUM(CASE WHEN E.CompletionstatusID = '4' THEN 1 ELSE 0 END), 
                      Cancelled = SUM(CASE WHEN E.CompletionstatusID = '7' THEN 1 ELSE 0 END), 
                      Retained = SUM(CASE WHEN E.CompletionstatusID = '1' THEN 1 ELSE 0 END) + SUM(CASE WHEN E.CompletionstatusID = '2' THEN 1 ELSE 0 END), 
                      [Percentage Retained] = CAST((CAST(SUM(CASE WHEN E.CompletionstatusID = '1' THEN 1 ELSE 0 END) AS DECIMAL(19, 2)) 
                      + CAST(SUM(CASE WHEN E.CompletionstatusID = '2' THEN 1 ELSE 0 END) AS DECIMAL(19, 2))) 
                      / NULLIF (CAST(SUM(CASE WHEN E.CompletionstatusID = '1' THEN 1 ELSE 0 END) AS DECIMAL(19, 2)) 
                      + CAST(SUM(CASE WHEN E.CompletionstatusID = '2' THEN 1 ELSE 0 END) AS DECIMAL(19, 2)) 
                      + CAST(SUM(CASE WHEN E.CompletionstatusID = '3' THEN 1 ELSE 0 END) AS DECIMAL(19, 2)) 
                      + CAST(SUM(CASE WHEN E.CompletionstatusID = '4' THEN 1 ELSE 0 END) AS DECIMAL(19, 2)) 
                      + CAST(SUM(CASE WHEN E.CompletionstatusID = '7' THEN 1 ELSE 0 END) AS DECIMAL(19, 2)), 0) * 100 AS DECIMAL(19, 2)), 
                      [CollegeLevelPercentage] =
                          (SELECT     (CAST(SUM(CASE WHEN En.CompletionstatusID = '1' THEN 1 ELSE 0 END) AS DECIMAL(19, 2)) 
                                                   + CAST(SUM(CASE WHEN En.CompletionstatusID = '2' THEN 1 ELSE 0 END) AS DECIMAL(19, 2))) 
                                                   / CAST((SUM(CASE WHEN En.CompletionstatusID = '1' THEN 1 ELSE 0 END) 
                                                   + SUM(CASE WHEN En.CompletionstatusID = '2' THEN 1 ELSE 0 END) 
                                                   + SUM(CASE WHEN En.CompletionstatusID = '3' THEN 1 ELSE 0 END) 
                                                   + SUM(CASE WHEN En.CompletionstatusID = '4' THEN 1 ELSE 0 END) 
                                                   + SUM(CASE WHEN En.CompletionstatusID = '7' THEN 1 ELSE 0 END)) AS DECIMAL(19, 2)) * 100
                            FROM          prosolution.dbo.Enrolment En INNER JOIN
                                                   prosolution.dbo.Offering Offering ON En.OfferingID = Offering.OfferingID INNER JOIN
                                                   prosolution.dbo.StudentDetail StudD ON En.StudentDetailID = StudD.StudentDetailID INNER JOIN
                                                   prosolution.dbo.CollegeLevel CL ON Offering.SID = CL.SID
                            WHERE      Offering.AcademicYearId = '06/07' AND CL.SID = O.SID
                            GROUP BY CL.SID)
FROM         prosolution.dbo.Enrolment E INNER JOIN
                      prosolution.dbo.Offering O ON E.OfferingID = O.OfferingID INNER JOIN
                      prosolution.dbo.StudentDetail SD ON E.StudentDetailID = SD.StudentDetailID INNER JOIN
                      prosolution.dbo.CollegeLevel ON O.SID = prosolution.dbo.CollegeLevel.SID
WHERE     SD.AcademicYearId = '06/07'
GROUP BY O.Code, O.Name, O.TargetStudents, SD.AcademicYearID, O.SID, prosolution.dbo.CollegeLevel.Code, prosolution.dbo.CollegeLevel.Name
ORDER BY O.Code, SD.AcademicYearID

Thanks
Dan

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-01-12 : 10:19:54

I think you should look at having the 'date calculations' driven by something like....
report date = coredate required +- date offset...where the offset shifts the goalposts, like your client did, to another timeperiod.

so your core data could be balances from 1/1/TY to 31/12/TY...but with an offset of -3months, you could report on balances from 1/10/LY to 30/9/TY...

This way the requirements would be satisfied by running the report 3 times....
If all the data was needed on the one report, then you would need to expand some of your query above to join again to the same data tables but inder a different time period...it's not unusual...you just need different 'aliases' for the tables...

chipembele
Posting Yak Master

106 Posts

Posted - 2007-01-13 : 16:52:05

Thanks for your reply. Thats probably a bit beyond me so I've had a think and am going to do two separate queries, one for all year and one since November. The final query will combine the two. Not efficient but i reckon it will do the job.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources