September 16, 2019 at 3:08 am
Here is sample code from my running total, now I need to reverse it and add a where exists clause to subtract rather than add from the initial value (in this case enrolled students from day 1). I need to add some sort of where clause or update for endDates to show drop offs from the initial value. Is it even possible with sum over or do I need to write a cursor or use something else? Do I have options?
table #enrollments
(
uniqueID INT
, startDate DATE
, endDate DATE
)
--My running total to get total enrolled.
SELECT startDate, sum(count(DISTINCT uniqueID)) OVER (PARTITION BY startDate ORDER BY startDate) AS enrolled
FROM dbo.enrollments
GROUP BY startDate;
--result set, date and active students....
2001-01-10 150
2001-01-11 150
.........
2001-03-09 149
..........
2001-04-10 148
September 16, 2019 at 3:59 am
If you're trying to get a count of enrollments for each day, then you would need a Calendar table of some kind. Are you trying to count the number of empty "seats" in a course section or something like that? (Course has a max of 30 enrollees, and counts down until there are no free seats.)
use tempdb;
GO
CREATE TABLE Enrollment (
StudentID CHAR(10)
,EnrollDate DATE
,LeaveDate DATE );
GO
INSERT INTO Enrollment(StudentID, EnrollDate, leaveDate)
VALUES ('0000000132','1/1/2019','1/31/2019'),
('0000000345','1/2/2019',null),
('0000000742','1/2/2019','5/18/2019');
CREATE TABLE #Dates(TheDate DATE);
GO
DECLARE @StartDate DATE,
@EndDate DATE,
@TheDate DATE;
SELECT @TheDate = MIN(EnrollDate)
, @EndDate = MAX(LeaveDate)
FROM Enrollment;
SET NOCOUNT ON;
WHILE @TheDate <= @EndDate
BEGIN
INSERT INTO #Dates(TheDate) VALUES (@TheDate);
SET @TheDate = DATEADD(day,1,@TheDate);
END
SELECT
d.TheDate
, COUNT(e.StudentID) AS EnrollCount
, 30-COUNT(e.StudentID) AS SeatsLeft -- 30 is an arbitrary maximum for the countdown.
FROM #Dates d LEFT JOIN Enrollment e
ON (d.TheDate >= e.EnrollDate AND
d.TheDate <= e.LeaveDate)
GROUP BY d.TheDate
ORDER BY d.TheDate;
September 16, 2019 at 10:55 am
This is pretty damn close. The numbers don't add up though because you are using an arbitrary total amount of seats, when it has to be the initial count of unique id's (students). When I arbitrarily put in the numbers to make the first set right, it destroys the rest. Also adding a day to the initial startDate throws it all off. I also put a CREATE INSERT attachment so you can take a look at the actual data, since it is just dummy data for my own learning process.
SELECT d.TheDate AS [Dates ]
, 55 + COUNT(e.unique_id) AS Term_Enrollment --how many enrolled on the first day
, COUNT(e.unique_id) AS Currently_Enrolled /*subtracted from term to show how many are still
enrolled from day one, should be 133 like term*/
FROM #Dates d LEFT JOIN #active_enrollments e
ON (d.TheDate >= e.start_date AND
d.TheDate <= e.end_date)
GROUP BY d.TheDate
ORDER BY d.TheDate;
/*Technically I don't need to keep track of the first day term enrollment, but I do need to see it drop when a child drops out ( or reaches end_date).*/
September 16, 2019 at 4:00 pm
From your data, I think what you're really dealing with is a overlapping intervals problem. You have overlapping intervals (student start and end dates) and you need a running count based on the intervals.
Try this solution, which only gives dates where the running total changes. If you need all dates, you can use a calendar table in conjunction with this solution. If you do, note that the order gets a bit tricky.
SELECT
de.enroll_status_dt
,SUM(de.enroll_status) AS daily_total
,SUM(SUM(de.enroll_status)) OVER(ORDER BY de.enroll_status_dt ROWS UNBOUNDED PRECEDING) AS running_total
FROM #enrollments AS e
CROSS APPLY ( VALUES(1, e.start_date), (-1, COALESCE(e.end_date, '9999-12-31'))) de(enroll_status, enroll_status_dt)
GROUP BY de.enroll_status_dt
ORDER BY de.enroll_status_dt;
Also, when working with open or half-open intervals, it is best to use arbitrary end points for the unknown values rather than using a NULL value. I tend to use 1900-01-01 as an arbitrary start value and 9999-12-30 (or 9999-12-31 or 9999-01-01) as an arbitrary end value unless the data could realistically fall outside of that range.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply