Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2009
    Posts
    47

    Unanswered: Create Table Based on Another Table's Row

    Hi

    I am currently working on an attendance table for a school. It is used to keep track of every student in the school along with the date. Every day of the year is a field IE Aug 10, Aug 11 ... June 1, etc, so it pretty much looks like an Excel sheet in a database. Now every year my work will recreate the table and manually put each date in that we need, basically Monday through Friday and leaves out days off and weekends.

    Now I thought that I could just create a table (tbl_all_attendance) that has every day of the year and I could create a second table (tbl_holidays) that has all the days off listed. Currently the days off are listed in one column called Exclusions in tbl_holidays. So I would have a row in the Exclusions column called Dec 25 and a field in tbl_all_attendance called Dec 25. I need SQL to create a third table (tbl_attendance_2010) and exclude any fields in tbl_all_attendance that are in a row in the Exclusions column.

    I would then use this link Find the WeekEnds in this Quarter or Year using SQL Server 2005/2008 | Sql Server Blog to find all the weekends of the year and do the same thing as the days off table, just remove all the weekends.

    This way we would only have to edit the holiday table and find the weekends using that link. It would save us about 24 hours of work to just do it this way every year.

    I also thought about just creating a table that creates a record for the student every day but couldn't think of how to display this as a spreadsheet and then I would be adding 1600+ records per day.

    Any help would be great. Or if there is a better way that anyone has I am open to ideas.

    Thanks

  2. #2
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    This solution will work on SQL Server 2005 and 2008.

    Aside from the above request being fairly poor database design, here's a fairly inelegant solution using a combination of cursors, common table expressions and dynamic sql, but it's quick, it creates the table with all the columns you need and it's easy.

    Create your holiday table first and load it with all the holidays of the year. (sample data here of course)
    Code:
    Create Table tbl_holidays (Holiday_Date date)
    Insert Into tbl_holidays Values ('2010-01-01')
    Insert Into tbl_holidays Values ('2010-01-02')
    Insert Into tbl_holidays Values ('2010-01-03')
    Insert Into tbl_holidays Values ('2010-01-04')
    Insert Into tbl_holidays Values ('2010-01-05')
    Insert Into tbl_holidays Values ('2010-01-06')
    Insert Into tbl_holidays Values ('2010-01-07')
    Insert Into tbl_holidays Values ('2010-02-10')
    Insert Into tbl_holidays Values ('2010-02-11')
    Insert Into tbl_holidays Values ('2010-02-12')
    Insert Into tbl_holidays Values ('2010-02-13')
    Insert Into tbl_holidays Values ('2010-02-14')
    Insert Into tbl_holidays Values ('2010-02-15')
    Insert Into tbl_holidays Values ('2010-02-16')
    Next create your years attendance table using dynamic SQL to create the table/columns, a common table expression to get all the dates of the year and a cursor to loop through all the valid dates and add the column to your table.

    Code:
    -- Set start and end date to start and end of year.
    -- Of course these could be defined as parameters for a stored procedure
    Declare @StartDate Date
    Declare @EndDate Date
    Set @StartDate = '2010-01-01'
    Set @EndDate = '2010-12-31';
    Declare @DateValue Date
    
    Declare @SQLString nvarchar(1000)
    
    -- Determine what the table name will be (tbl_attendance_yyyy) and create it with a single field of student name.
    Declare @v_TableName nvarchar(30)
    Set @v_TableName = 'tbl_attendance_'+CAST(datepart(year,@StartDate) as nvarchar)
    Set @SQLString = 'CREATE TABLE '+@v_TableName+' (Student_Name nvarchar(80))'
    exec sp_executeSQL @SQLString
    
    -- Use a CTE to loop through all dates of the year and put them into
    -- a temporary table if it is not a saturday/sunday and the date is
    -- not in the tbl_holidays table.
    Declare @DatesTable Table (Date_Value date);
    With DATECte As
    (
    	Select @StartDate AS Date_Value
    	Union All
    	Select	DateAdd(Day,1,Date_Value)
    	From    DATECte   
    	Where   DateAdd(Day,1,Date_Value) <= @EndDate
    )
    Insert Into @DatesTable (Date_Value)
    Select	H.Date_Value
    From	DATECte h
    Where	H.Date_Value NOT IN (Select Holiday_Date From tbl_holidays)
    	And	DATEPART(dw,H.Date_Value) Not In (6,7)
    Option (MAXRECURSION 365)
    
    -- Create a cursor to loop through the resulting dates, using dynamic
    -- sql to create an alter table statement for adding the appropriately
    -- named column.
    Declare c_Dates Cursor For
    	Select Date_Value From @DatesTable
    
    Open c_Dates
    Fetch Next From c_Dates into @DateValue
    While @@FETCH_STATUS = 0
    Begin
    	
    	Set @SQLString = 'ALTER TABLE '+@v_TableName+' ADD ['+substring(convert(nvarchar,@DateValue,0),1,3)+'-'+CAST(datepart(day,@DateValue) as nvarchar)+'] nvarchar(10)'
    	exec sp_executeSQL @SQLString
    	Fetch Next From c_Dates into @DateValue
    End
    
    close c_Dates
    Deallocate c_Dates
    
    -- Show the information_schema listing all the columns in the table we've just created.
    select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @v_TableName

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by apurgert View Post
    I also thought about just creating a table that creates a record for the student every day but couldn't think of how to display this as a spreadsheet and then I would be adding 1600+ records per day.
    Do it this way, and don't care for one second about adding 1600 rows a day.

    Your design of one column per day of the year will result in a world of pain when you actually come to use it.

  4. #4
    Join Date
    Feb 2004
    Posts
    492
    What about turning the whole thing around: assume a student is present unless it's listed absent or that day is during weekend/holiday?

  5. #5
    Join Date
    Jul 2009
    Posts
    47
    Quote Originally Posted by pootle flump View Post
    Do it this way, and don't care for one second about adding 1600 rows a day.

    Your design of one column per day of the year will result in a world of pain when you actually come to use it.
    Would I be able to still view it as a "spreadsheet" though? I would need it to look like this

    Student 01/01/10 01/02/10 ....
    Student A [Blank] Unexcused
    Student B Excused [Blank]
    Student C [Blank] [Blank]

    [Blank] meaning s/he is present, and come through with nothing (IE it doesn't say "NULL").

    I am just not sure how to get the dates to be column headers. We assume the students are present each day until they are absent.

    I agree that this is a weird table to keep in a database but it was an Access database and I got moved from our hardware department to the SQL admin even though I've never coded in SQL.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The principles of database design have no relationship to how your UI is to look to your users. You separate (or de-couple) your data and presentation layers.

  7. #7
    Join Date
    Jul 2009
    Posts
    47
    Thanks for the advice. I have looked into ways of creating crosstabs and pivot tables which look like they will do the job for what I need. Now is there a simple way of creating a record for each student every day or will SQL just "know" to do this?

    I thought about only creating a record for a student if they are absent but I didn't know how this would affect other students. For example if a Student A was absent he would have a record. However, if Student B was present there would not be a record. Would SQL get confused when I am putting the data into a spreadsheet, something like:

    Student 01/01/10
    Student A Excused
    Student B #Error or #Deleted or something like that?

    On the topic of only creating "necessary" records what if every student was present? It is unlikely but would there just not be a date? Like 01/01/10 01/03/10 and miss 01/02/10?

    The only other problem I have is that if every day is created I again have the problem of weekends and days off in my table that would need to be handled.

    Thanks for your help.

  8. #8
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    I thought about only creating a record for a student if they are absent but I didn't know how this would affect other students. For example if a Student A was absent he would have a record. However, if Student B was present there would not be a record.
    If you are going to fix your database design and throw out the ridiculous idea of having a column for every da of the year then I think you would be far better off having your absenteeism table showing only those absentees. Having a record for every student for every day would add too much maintenance. If you have 1600 student you need to add 1600 records every day, if you only record absences then you need to add say 50 or 100 records a day - however many absences you have.

    I.e. You absenses table could look something like:
    Student Name
    Date
    Absent Reason

    Presumably you have a master student table, so if you start with this table as the base of your query, joining to a table holding all the days of the year (or cross applying a CTE that gets the dates for you), then you can left join to the absenteeism table to pick up whether or not there was an absense on that day.

    Eg: Using a CTE
    Code:
    With DATECte As
    (
    	Select Cast('2010-01-01' as DATE) AS Date_Value
    	Union All
    	Select	DateAdd(Day,1,Date_Value)
    	From    DATECte   
    	Where   DateAdd(Day,1,Date_Value) <= '2010-12-31'
    )
    Select		S.Student_Name,
    		DATECte.Date_Value,
    		TA.Absense_Reason
    From		tbl_Students S
    Cross Apply	DATECte 
    Left Join	tbl_absentees TA
    		On	TA.Student_Name = S.Student_Name
    		And	TA.Date = DATECte.Date_Value
    Where		DATEPART(dw,DATECTE.Date_Value) Not In (6,7)
    Order By	S.Student_Name,
    		DATECte.Date_Value
    Option		(MAXRECURSION 365)
    You would get results like
    Student A 2010-01-01 Null
    Student A 2010-01-02 Null
    Student A 2010-01-03 Unexcused
    Student A 2010-01-04 Null
    Student A 2010-01-05 Excused
    .
    .
    Student B 2010-01-01 Null
    Student B 2010-01-02 Null
    Student B 2010-01-03 Null
    Student B 2010-01-04 Null
    .
    .
    etc for every day of the year.

    You can then Pivot this data in your presentation layer (Report or UI).

    Note: The above only excludes saturday and sunday, you could also join to your holidays table to exclude non-school days.

  9. #9
    Join Date
    Jul 2009
    Posts
    47
    Wow that works just as you said it would. However the last question I have is "where" is the data? When I run the query I get results exactly as you state.

    EMIS_ID Last First M Date Absence_Reason
    1 Aardvarklast Aardvarkfirst NULL 2010-08-01 NULL
    1 Aardvarklast Aardvarkfirst NULL 2010-08-02 NULL
    1 Aardvarklast Aardvarkfirst NULL 2010-08-03 NULL
    1 Aardvarklast Aardvarkfirst NULL 2010-08-04 NULL

    But I am not sure how to actually access the data. I don't have a table with all of these things in it? This query just gives me these results but doesn't apply it anywhere.

    Here is the code as I have it.

    Code:
    -- Set start and end date to start and end of year.
    -- Of course these could be defined as parameters for a stored procedure
    Declare @StartDate Date
    Declare @EndDate Date
    Set @StartDate = '2010-08-01'
    Set @EndDate = '2011-06-10';
    Declare @DateValue Date
    
    Declare @SQLString nvarchar(1000)
    
    -- Use a CTE to loop through all dates of the year and put them into
    -- a temporary table if it is not a saturday/sunday and the date is
    -- not in the tbl_holidays table.
    Declare @DatesTable Table (Date_Value date);
    With DATECte As
    (
    	Select Cast(@StartDate as DATE) AS Date_Value
    	Union All
    	Select	DateAdd(Day,1,Date_Value)
    	From    DATECte   
    	Where   DateAdd(Day,1,Date_Value) <= @EndDate
    )
    Select	S.EMIS_ID,
    		S.Last,
            S.First,
            S.M,
    		DATECte.Date_Value,
    		TA.Absence_Reason
    From		tbl_student_record S
    Cross Apply	DATECte 
    Left Join	tbl_Attendance TA
    		On	TA.EMIS_ID = S.EMIS_ID
    		And	TA.Date = DATECte.Date_Value
    Where		DATEPART(dw,DATECTE.Date_Value) Not In (6,7) AND DATECte.Date_Value NOT IN (Select Excluded From tbl_holiday)
    
    Order By	S.Last,
    			S.First,
    		DATECte.Date_Value
    Option		(MAXRECURSION 365)
    Does this need a create table statement somewhere? And if so how would I do it. I tried creating a table similar to how EngadaSQL showed in his first response but I couldn't get it to work correctly.

    Thanks again you have all been so helpful to me. I really appreciate all this.
    Last edited by apurgert; 06-10-10 at 23:15.

  10. #10
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Where the data is would depend on what you want to do with it.

    You are just running a query which will return the data to your query window, but the data is not materialised anywhere - it's gone as soon as it's created.

    What you need to decide is what is the next step - do you need that data accessible to a report, do you need it accessible to an applications UI, do you want it stored in a table so several things can query on it?

    I would probably go for making it a table-valued function/stored procedure - takes a start date and end date (Putting them into the @StartDate and @EndDate variables as declared at the top of your code (and even optionally a student ID, so you can enquire on a single student if you need to) and return the results as you have seen them.

    The next step is up to you what to do with that - do you call that TVF/SProc directly from your application? Do you set your reporting system to call the stored procedure? Do you turn it into a view and make it visible to something like excel or access?

    There is oodles of options and they all depend on what you want to see at the end.

    I would avoid inserting this query's data directly into a table, since the data is going to be continuously changing and you don't want to be repopulating that table every day. You just need to update your tbl_attendance table with each days non-attendees and then you can re-run this query in whatever format you decide to put it (TVF/SProc/View etc) and you have your data available to whatever wants to use it.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would advise against only recording absenteeism. However, this depends on your regulatory and organisational requirements. I have worked in education and attendance systems were just those - records of attendance. Inferring attendance from the absence of a row in a table would not have been permitted.

    EngadaSQL's advice is good database practice but may not be good business practice for your organisation. I can't tell you if one way is right or wrong for you (you must do that) however I would carefully think it through. Also, 1600 rows a day is a trivial number of rows to SQL Server (especially rows as narrow as these will be) so don't be scared of that.

  12. #12
    Join Date
    Jul 2009
    Posts
    47
    Thanks for all the advice on this stuff. I took last Friday off for a weekend vacation so I haven't looked at it since then.

    What I think would be best is making it into a table because we do use query's on the attendance table quite frequently.

    Two questions come to mind. First how would I create such a table to do this? Would it be like

    CREATE TABLE

    then run the query? and it will populate itself?

    The second question is if we add a student will they be automatically added or would I have to recreate the entire table again thus losing the attendance for everyone else?

    Also, I will look into the policy of only keeping absenteeism. We have only done it that way in the past, blank for present, then excused or unexcused for absent.

    Thanks again for helping me through this.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You create the table once, using CREATE TABLE DDL syntax.
    You manipulate the data in the table using INSERT, UPDATE and DELETE DML syntax (or MERGE if you are using SQL Server 2008).

  14. #14
    Join Date
    Jul 2009
    Posts
    47
    Creating the table was easy after you had mentioned the INSERT statement. The final code that I used - in case anyone ever needs it - is as follows.

    Code:
    -- Original Code by EngadaSQL at http://www.dbforums.com/microsoft-sql-server/1657340-create-table-based-another-tables-row.html#post6464129
    -- Edited by apurgert
    -- Set start and end date to start and end of year.
    -- Of course these could be defined as parameters for a stored procedure
    Declare @StartDate Date
    Declare @EndDate Date
    Set @StartDate = '2010-08-01'
    Set @EndDate = '2011-06-10';
    Declare @DateValue Date
    
    Declare @SQLString nvarchar(1000)
    
    -- Use a CTE to loop through all dates of the year and put them into
    -- a temporary table if it is not a saturday/sunday and the date is
    -- not in the tbl_holidays table.
    Declare @DatesTable Table (Date_Value date);
    With DATECte As
    (
    	Select Cast(@StartDate as DATE) AS Date_Value
    	Union All
    	Select	DateAdd(Day,1,Date_Value)
    	From    DATECte   
    	Where   DateAdd(Day,1,Date_Value) <= @EndDate
    )
    INSERT INTO tbl_Attendance (EMIS_ID, Last, First, M, Date, Absence_Reason)
    Select	S.EMIS_ID,
    		S.Last,
            S.First,
            S.M,
    		DATECte.Date_Value,
    		TA.Absence_Reason
    From		tbl_student_record S
    Cross Apply	DATECte 
    Left Join	tbl_Attendance TA
    		On	TA.EMIS_ID = S.EMIS_ID
    		And	TA.Date = DATECte.Date_Value
    Where		DATEPART(dw,DATECTE.Date_Value) Not In (6,7) AND DATECte.Date_Value NOT IN (Select Excluded From tbl_holiday)
    
    Order By	S.Last,
    			S.First,
    		DATECte.Date_Value
    Option		(MAXRECURSION 365)
    Results in the table look like this.

    Code:
    ID	Absence_Reason	EMIS_ID	Date	Last	First	M
    335966	NULL	1	2010-08-01 00:00:00	Aardvarklast	Aardvarkfirst	NULL
    335967	NULL	1	2010-08-02 00:00:00	Aardvarklast	Aardvarkfirst	NULL
    335968	NULL	1	2010-08-03 00:00:00	Aardvarklast	Aardvarkfirst	NULL
    335969	NULL	1	2010-08-04 00:00:00	Aardvarklast	Aardvarkfirst	NULL
    Last thing needed is to write the rest of the INSERT command to add new students and the UPDATE command to give the absence reason.

  15. #15
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Using this method, then why even store it in a table? Set up a view that would return the data as you want it, the query above and then just run it when you want a report on student absences or further restrict it by querying the view by a single class/student etc... No need to store the data that already exists, when all you are doing is reporting it.
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •