How To Convert A Table Containing Timetable Data Into A Proper Timetable Format
Mar 7, 2008
Using SQL Server 2005, I have set up a database for a school to provide timetables for students, teachers etc. The data is currently displayed in this form:
DAY LESSON YR SUBJ GRP STAFF ROOM
Mon 1 7 PE T Da Gym
Mon 2 7 PE T Da Gym
Mon 3 7 Ma A Pr 5
Mon 4 7 Ma A Pr 5
Mon 5 7 Ma A Pr 5
Mon 6 7 Dr T Fn 36
Mon 7 7 Dr T Fn 36
Mon 8 7 En T Bn 21
Mon 9 7 En T Bn 21
Tue 1 7 De T Df 27
Tue 2 7 De T Df 27
etc.
I would like to display this as timetable with DAYS as row headings and LESSON numbers as column headings, with the remaining column info. in the above table combined into one value:
1 2 3 4 etc
MON (7 PE T Da Gym) (7 PE T Da Gym) (7 Ma A Pr 5) (7 Ma A Pr 5)
TUE (7 De T Df 27) (7 De T Df 27) etc.
WED
THU
FRI
Should I be doing this with PIVOT (scares the life out of me ...) or is there some other solution? Many thanks.
to display data like above would be simpler. The above timetable would be similar all through the year.
But my 3rd requirement is that, there is a option wherein the school admin can edit the timetable for any particular day or a week based on presence/absence of a particular teacher. So if a teacher who is teaching Maths is not present for 2 days, they might assign a different subject(eg., Physics) for those 2days to a different teacher.
so the table should look like below for those two days only based on the date selection.
I am not sure how exactly I can mention the dates wherein I can allocate the days when the periods are changed and how exactly same timetable should be shown for the entire year if there is no change.
suppose the timetable is changed for two days 24/01/2015 and 25/01/2015 wherein Maths is replaced with Physics subject, how to retrieve the changed data for only those two days.
I am building a sql table. It is a time table. It has fields From/To stations, departure_time/arrival_time, blocktime and total time. Is there any way I can create a computed column which subtracts departure_time from arrival_time and displays it in block_time column. Is there any way to sum the block_time. Datetime is unable to sum time if it is above 24:00. I am using SQL 2000
I am trying to run a query against the time & resourceorganization tables that returns selected entries from the time table, the date of the first day of the work week, and the current department. The query returns an "Exists" error. When I add exists or where exists I get ..an error near From. The main query and the sub query each run by themselves I just can;t seem to combine them. Here is what I have:
I need to convert a SQL table or SQL table data to XML format. I tried using the Import Export Wizard in SQL 2005 (used SQLXMLOLEDB and SQLXMLOLEDB 4.0 as the source). However, it didn't work. Any way you know how I can convert and obtain data in XML format?
hi siri have table hh .it has two columnsone is hhno is integer datatype another hhdoc is xml data type likehh tablehhno hhdoc---------------------------------------------------------------------------------------------100<suresh>sfjfjfjfjf</suresh>....................................101<ramesh>hhfhfhf</ramesh>..................................how to convert the xml data format into the general data format plshelp me with examples
I read some questions where questioners ask "Sometimes client gives data where dates are expressed as float or integer values. How do I find maximum date?".
Ex March 02, 2006 can be expressed as 02032006.0 020306 2032006 20306 020306.0000 2032006 Assuming the values are expressed in dmy format
The possible way is convert that value into proper date so that all types of date related calculations can be done Create function proper_date (@date_val varchar(25)) returns datetime as Begin Select @date_val= case when @date_val like '%.0%' then substring(@date_val,1,charindex('.',@date_val)-1) else @date_val end return cast( case when @date_val like '%[a-zA-Z-/]%' then case when ISDATE(@date_val)=1 then @date_val else NULL end when len(@date_val)=8 then right(@date_val,4)+'-'+substring(@date_val,3,2)+'-'+left(@date_val,2) when len(@date_val)=7 then right(@date_val,4)+'-'+substring(@date_val,2,2)+'-0'+left(@date_val,1) when len(@date_val)=6 then case when right(@date_val,2)<50 then '20' else '19' end +right(@date_val,2)+'-'+substring(@date_val,3,2)+'-'+left(@date_val,2) when len(@date_val)=5 then case when right(@date_val,2)<50 then '20' else '19' end +right(@date_val,2)+'-'+substring(@date_val,2,2)+'-0'+left(@date_val,1) else case when ISDATE(@date_val)=1 then @date_val else NULL end end as datetime ) End
This function will convert them into proper date select dbo.proper_date('02032006.0') as proper_date, dbo.proper_date('020306.000') as proper_date, dbo.proper_date('02032006') as proper_date, dbo.proper_date('020306') as proper_date, dbo.proper_date('20306') as proper_date, dbo.proper_date('020306') as proper_date
Apart from converting integer or float values to date, it will also convert date strings to date Select dbo.proper_date('March 2, 2006') as proper_date, dbo.proper_date('2 Mar, 2006') as proper_date, dbo.proper_date('2006 Mar 2') as proper_date, dbo.proper_date('2-Mar-2006') as proper_date, dbo.proper_date('3/02/2006') as proper_date, dbo.proper_date('02-03-2006') as proper_date, dbo.proper_date('2006/03/02') as proper_date, dbo.proper_date('March 2006') as proper_date, dbo.proper_date('2 Mar 2006') as proper_date
Hello, I have one package that seems to have continuous problems with memory. As of right now it loads a little over 1 million records. I tried leveraging the property, BufferTempStoragePath, but I don't seem to have the right path name. What sort of path do you put there? File? Folder? If it is a file, what sort of file should it be... text, dat, xml? If someone could point me in the right direction it would be greatly appreciated. Thanks.
PS: Below are the error messages I am getting: [DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 4 buffers were considered and 4 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
[DTS.Pipeline] Error: The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission.
I have a .csv file that lists date like this: "20070522", no hyphens or spaces. In the file connection manager I have the column defined as string. The database column is a datetime.
When I attempt to load the file to the table, I get this error:
[OLE DB Destination [9]] Error: There was an error with input column "effective date" (155) on input "OLE DB Destination Input" (22). The column status returned was: "The value could not be converted because of a potential loss of data.".
My question is, what do I need to do to this column so that I can load it into the database?
i have 12 different sql server tables. all are binded to one gridview. i have kept one admin user to view the details of the 12 tables in a gridview (pagesize=5). now the admin wants to generate 12 CSV files from the 12 tables (that is, he has to choose the table name from a dropdown and then has to click "Generate" button. CSV file name should be like this "<table_name>_<date>" ). also want to put links(dynamically [after generation of those csv files]) in a separate page to download those csv files. how to do this in asp.net (C#)? its urgent.
I've got a field I'm trying to convert into a date format as it currently sits as a text field. The date setup is of the format DD/MM/YYYY. Some of the fields are NULL, however for the sake of filling in gaps I've just set the nulls to '01-01-1905'.
I've tried to use CAST to change the data into a readable format for SQL Server however I tend to get one of two messages:
Conversion failed when converting date and/or time from character string.
Please find below my query and result , how to display [Total Service Time ] in HH:Min format (Currently values in minutes)
Query: SELECT DISTINCT dbo.sectn_dept.sectn_sc AS Customer, MONTH(dbo.incident.date_logged) AS Month_Number, DATENAME(month, dbo.incident.date_logged) AS Month, YEAR(dbo.incident.date_logged) AS Year, dbo.incident.incident_ref PM_ref, dbo.product.product_n "Product",
[Code] ....
Result: Need to Display [Total Service Time] in below Format:
Hi all, I am using ASP.NET 2003 with SQL Server as database. I have a database of a book store with BookPicture, Author, Title, and Description of the Book. Now when the user searchers for a book with a keyword, how can I display the results which should show: 1. The picture of the book, 2. The at it right, Title of Book, 3. The author,4. The descritionThen the image of "Add to cart" Each search result must be separated by a box like the cell of a table. Is it possible to be done? Thanking you in advance Tomy
I am stuck at one place, where I have to convert CSV format file data into SAP IDOC format file. In SSIS we don't have any such SAP adapter (though we have .NET Data Provider for mySAP suite [SSIS SAP Adapter] but this is still not fully supported by Microsoft, plus it doesn't have feature to convert data into IDOC format) that can do this. Can someone here please provide me some pointers on any third party adapters available in market to do this job or if anyone has already developed some custom approach to achieve this task?
Your quick response on this is highly appreciated.
E.g, i have a store procedure. The start date is long date (4/15/2007 3:00pm). i want to select the start date with a particular date (short date format 4/15/2006). Thanks in advance.
I have a table that has a DATE field named. AccountingDate that is in the format YYYY-MM-DD. It's not a VARCHAR field. I simply want to convert this date field into the format MM/DD/YYYY and call it New_Accounting_Date.
I've played with various combinations of CAST & CONVERT but haven't been able to get it to work.
Below is my latest effort which returns the error:
Incorrect syntax near the keyword 'as'
What code would work to return a MM/DD/YYYY value for New_Accounting_Date?
Select GLBATCH.AccountingDate, convert(GLBATCH.AccountingDate as date),101) AS New_Accounting_Date from GLBATCH
I am using vs2008 with c#.I have table in my database that has email addresses stored in a column. I would like somehow to take each email address from every row and paste them into a textbox that can be copied and pasted with a comma delimiter into outlook so I can send a mass email.I have no idea how I can accomplish this task.
Hi everyone,I have some data in a CSV file, and I have to import it into a table. For some reason, I am supposed to import this data into a temp table and then move it to the original table and I have to convert it to the right data types while I do this. Is there a better way to do this and how can I give custom error messages saying, for e.g., the data type cannot be converted, the right number of records are not present etc. Thanks for the help.
I am trying to output the data in the table to uppercase. I am using bcp to output as text file.Any possible solution to convert the data to uppercase from the table.Any tsql code for this
Hi I am trying to export an table data to csv format. The problem here is the table columns are dynamic. The DTS exports only the columns available during the DTS design time and it ignores if any new columns are added after the design. I need solution for this asap. Thanks SqlJerin
Hello,I have a problem the scenario is :I have data in an excel file and now I am reading data from that file and insert that data into sql database. this is well.but the problem is that I have few fields with date time data in excel sheet. In my database I have varchar type data type for these data columns.I want to read these data columns from the excel sheet and insert only time into the data base.how can I do this I am using the following line of code for selelcting only time from the excel file. string qry = "Select CONVERT(CHAR(5),datetime,114) from [" + objStr[0] + "];";this gives me error message.help me to read the data from excel file and insert it into the sql table in desired format. Thanks in advance, junior
I uploaded custtable under the database, the data looks fine except that the name that apprears has a lot of distance e.g
it should be :
firstname lastname however the format appears very strange:
firstname lastname
firstname lastname
fistname lastname
Same is the case with the address, I need to adjust or format the apperance that appears on the cell. Is there a way/ sql statement to format the data under the table so that the apprearence looks okay.
I will really appreciate any sort of help on this one.
I have several bcp output files I need to import into tables. I do not have format files for them. As far as I know they are in native format. I do not know the layout of the destination table they would populate.
1) how can I determine from the bcp file itself the schema of the destination table? Once I know that I should be able to import the data into the table.
