April 16, 2014 at 12:43 pm
What I am trying to do: Obtain attendance percentages for schools for the last five days. The outcome would look like this:
DISTRICTGROUPING, SCHOOLNAME, 5 DAYS AGO PCTG, 4 DAYS AGO PCTG, 3 DAYS AGO PCTG, 2 DAYS AGO PCTG, 1 DAY AGO PCTG
I am using nested subqueries for each day as follows: (total enrollment-total absent/total enrollment)
,(
((SELECTCOUNT(*)--GET TOTAL ENROLLMENT COUNT FOR SPECIFIED DATE
FROMdbo.att_stu_day_memb ASD
WHEREMEMBERSHIP_DATE = CONVERT(DATE,(SELECT DATEADD("d",-5,GETDATE())))
ANDMEMBERSHIP_VALUE = 1
ANDASD.BUILDING = RUB.BUILDING)
-
(SELECTCOUNT(*)--GETS TOTAL ABSENCES FOR SPECIFIED DATE
FROMdbo.ATT_STU_DAY_TOTALS ASD
WHEREATTENDANCE_CODE IN('E','U')
ANDATTENDANCE_DATE = CONVERT(DATE,(SELECT DATEADD("d",-5,GETDATE())))
ANDASD.BUILDING = RUB.BUILDING))
/
CAST((SELECTCOUNT(*)--GET TOTAL ENROLLMENT COUNT FOR SPECIFIED DATE
FROMdbo.att_stu_day_memb ASD
WHEREMEMBERSHIP_DATE = CONVERT(DATE,(SELECT DATEADD("d",-5,GETDATE())))
ANDMEMBERSHIP_VALUE = 1
ANDASD.BUILDING = RUB.BUILDING) AS FLOAT)
)
AS'PERCENTAGE 5'
The query works with the following exceptions:
My issues are:
1. Avoid the "division by zero" error. This can occur if a school is closed for a day or if a smaller school has no absences for a day.
2. Avoid weekend dates. I need the query to display only weekdays
3. Currently I am using "PERCENTAGE 5: as a column header whereas I need the actual date as the header.
Any suggestions would be greatly appreciated.
April 16, 2014 at 1:06 pm
In your WHERE clause, you could try:
WHERE DATEPART(dw, getdate()) in (2, 3, 4, 5, 6)
That should give you only weekdays.
April 16, 2014 at 1:56 pm
miles_lesperance (4/16/2014)
In your WHERE clause, you could try:WHERE DATEPART(dw, getdate()) in (2, 3, 4, 5, 6)
That should give you only weekdays.
quick note...make sure you understand what is the first day of your week
http://technet.microsoft.com/en-us/library/ms181598(v=sql.100).aspx
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 16, 2014 at 1:57 pm
miles_lesperance (4/16/2014)
In your WHERE clause, you could try:WHERE DATEPART(dw, getdate()) in (2, 3, 4, 5, 6)
That should give you only weekdays.
That doesn't really capture what the OP is looking for. This just means the query will return results if the current day of week is Monday - Friday (assuming the first day of the week is Sunday). That does NOT do any filtering of the data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 16, 2014 at 1:58 pm
To the OP - Hi and welcome to the forums. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 17, 2014 at 2:02 pm
I think this query will give you what you need in separate rows. If so, let us know, and we can pivot it to a single row.
DECLARE @control_date datetime
SET @control_date = GETDATE()
--------------------------------------------------------------------------------
SET @control_date = DATEADD(DAY, DATEDIFF(DAY, 0, @control_date), 0) -- strip time
DECLARE @first_date_to_pull datetime
SET @first_date_to_pull = DATEADD(DAY, -5, @control_date)
SET @first_date_to_pull = DATEADD(DAY, CASE
WHEN DATEDIFF(DAY, 0, @first_date_to_pull) = 0 THEN 0 --Mon
WHEN DATEDIFF(DAY, 0, @first_date_to_pull) IN (1, 5) THEN -1 --Tue,Sun
ELSE -2 END, @first_date_to_pull)
--SELECT @first_date_to_pull
SELECT
DATEADD(DAY, DATEDIFF(DAY, 0, MEMBERSHIP_DATE), 0) AS MEMBERSHIP_DATE,
COUNT(*) AS TOTAL_ENROLLMENT,
SUM(CASE WHEN ATTENDANCE_CODE IN('E','U') THEN 1 ELSE 0 END) AS TOTAL_ABSENCES
FROM dbo.att_stu_day_memb ASD
WHERE
MEMBERSHIP_DATE >= @first_date_to_pull
AND MEMBERSHIP_DATE < @control_date
AND DATEDIFF(DAY, 0, MEMBERSHIP_DATE) NOT IN (5, 6) --Sat,Sun
AND MEMBERSHIP_VALUE = 1
AND ASD.BUILDING = RUB.BUILDING
GROUP BY
DATEADD(DAY, DATEDIFF(DAY, 0, MEMBERSHIP_DATE), 0)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 22, 2014 at 9:39 am
Thank you for all of your input.
I recently learned that the days that I must gather data for is for the last 5 school days. All days that had no classes must be eliminated. My team is currently collaborating on creating a function that will handle this requirement.
Again, thank you for your input.
Dan Tuma
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply