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.

View 1 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Table Structure For Creating Timetable For A School

Jan 24, 2015

I am working on creating a timetable app for school. I have been given a requirement as below

1. The weekdays like Monday,Tuesday,...should be displayed on top
2. Timing slot would be 8:00, 9:00,... which would be displayed vertically

the table would display as below

MondayTuesday WednesdayThursdayFriday Saturday
07:00Physics Chemistry Maths BiologySocialScience Craft
08:00MathsValueEducation Chemistry EnglishBiology Yoga
09:00GamesComputerScience Science Chemistry Hindi Maths

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.

Timings MondayTuesday WednesdayThursday Friday Saturday
07:00Physics Chemistry Physics Biology SocialScience Craft
08:00MathsValueEducation Chemistry English Biology Yoga
09:00GamesComputerScience Science Chemistry Hindi Physics

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.

View 9 Replies View Related

Timetable

Jun 8, 2007

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


vishwamukh

View 12 Replies View Related

Returning Selected Entries From Timetable

Jul 10, 2014

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:

SELECT Time.ResourceID, DATEADD(dd, -(DATEPART(dw, Time.TimeEntryDate)-1), Time.TimeEntryDate) [WeekStart], Time.TimeEntryDate,
Time.ActivityCode, Time.ProjectCode, Time.TaskName, Time.StandardHours,
Time.StatusCode,

[Code] ....

View 7 Replies View Related

Convert SQL Table Data To XML Format

Oct 31, 2006

Hello,

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?

Thanks all in advance,
Saurav

View 2 Replies View Related

I Want Ot Convert Xml Data Format Into The General Data Format

Jun 9, 2006

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

View 1 Replies View Related

Convert To Proper Date

Apr 16, 2007

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


Madhivanan

Failing to plan is Planning to fail

View 8 Replies View Related

Proper Format Of BufferTempStoragePath

Dec 11, 2006

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.

View 3 Replies View Related

Getting Date Into Proper Format

May 22, 2007

Hi,



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?



Thanks much

View 6 Replies View Related

How To Convert SQL Server Table To CSV File Format?

May 30, 2008

 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.

View 1 Replies View Related

SQL Server 2008 :: Convert Table Into XML Format

Oct 5, 2015

I want to convert table into following xml format. Sample two records is given below.

<Row>
<Cell ss:StyleID="s22">
<Data ss:Type="String">XXXXXXXXXXXXX</Data>
</Cell>
<Cell ss:StyleID="s22">
<Data ss:Type="String">64856</Data>

[Code] ....

View 9 Replies View Related

Convert Text Data To Date Format

Nov 13, 2013

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.

-Or-

error converting data type varchar to date

View 2 Replies View Related

Convert Date Format - Data Type Varchar

Aug 6, 2013

I have a column which is

Data type : varchar(10)
Date format : 8/5/2013

I want to convert that column to the following

Data type varchar: (8)
Date format : 20130805

View 5 Replies View Related

Data Access :: Date Formatting (Convert Minutes Into HH:MM Format)

Jun 20, 2015

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:

But Some values are repeating ....

View 2 Replies View Related

How To Display The Search Result Of A BookStore With Pictures Of Books, In A Proper Format.

Jan 22, 2007

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
 

View 2 Replies View Related

Adapter To Convert CSV Format File To SAP IDOC Format

Nov 16, 2006

Hi All,

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.

Regards,

Kuldeep Chauhan

View 2 Replies View Related

Help Needed Little Urgent---how To Convert The String Date To Standard Date Format In SQL Table

Sep 28, 2007

Using DTS package in 2000 version, I am dumping TXT file contents into SQL Table,

I have one column having date in format YYYYMMDD(20070929) and corresponding column in SQL is datetime, but it fails on data type mismatch.

I have no choice of making date column in SQL to string or Varchar etc,

is there any way to make that date column in SQL to convert the value upon transformation from format (YYYYMMDD) to M/DD/YYYY (9/29/2007).

many many thanks,

View 2 Replies View Related

How To Convert Long Date Format To Short Date Format In Store Procedure.

Feb 1, 2008

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.

View 1 Replies View Related

Transact SQL :: Cast Or Convert Date In Format YYYY-MM-DD Into New Format Of MM/DD/YYYY?

Nov 27, 2015

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

View 11 Replies View Related

Convert Table Data Into Array Or Maybe Something Else?!?

Apr 27, 2008

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. 

View 3 Replies View Related

Import And Convert Table Data

Feb 15, 2006

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.

View 1 Replies View Related

Convert To Uppercase The Data In Table

Apr 9, 2008

Hi

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


Thanks in advance

View 4 Replies View Related

Exporting Sql Table Data To Csv Format

Apr 4, 2006

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

View 5 Replies View Related

How To Insert Data Into Sql Table In Desired Format

Jan 25, 2008

 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 

View 1 Replies View Related

SQL 2012 :: Parse XML Data To Table Format

May 5, 2014

declare @xml table (xmldata xml)
insert @xml select
N'<parseObject name="Motel">
<fields>
<field name="vehicleno" fieldType="int" fieldSize="">

[Code] ....

I want to extract data in in table format

ParseObjectName FieldType FieldSizeGrammar
Motel VehicleNo Int NULL div.biz-page-subheader li > span.i-phone-biz_details-wrap
mapbox-icon span.biz-partno[/size]

View 2 Replies View Related

How To Format Cell / Data Apprearecnce Under A Table

Mar 27, 2007

Hello All,



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.



Thanks,

Rashi

View 1 Replies View Related

Transact SQL :: Change Data Format In A Table

Jun 4, 2015

I have 900000 rows of data in a table and a sample of it is as shown below.Now I need to convert data into the format shown.

View 6 Replies View Related

Transact SQL :: Format To Store Data In Table

May 10, 2015

I would like to know what is the preferred format for form submissions to a SQL table. 

View 5 Replies View Related

Convert XML Data With Select Query And Insert Into Table?

Jun 19, 2014

I want to convert xml data with select query and insert into table.

Xml sample like this :

<ROOT>
<ROW>
<NAME>JHON</NAME>
<ADDRESS>
<ADDRESS1>
<CITY>LKO</CITY>
<STATE>UP</STATE>

[code]....

And data should be like this with select query :

NAME ADDRESS1CITY ADDRESS1STATE ADDRESS2CITY ADDRESS2STATE
JHON LKO UP DLI DELHI
YASH AAA HYR NULL NULL

I want simple query in form of above format.

View 8 Replies View Related

Transact SQL :: Select Data From Table With Custom Format

Sep 15, 2015

I want to select custom format from table?

View 7 Replies View Related

How To Import Data Using Bcp Without A Format File Or Table Layout

Mar 20, 2008

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.

Thanks

View 6 Replies View Related

SQL Server 2008 :: Convert Data From 2 Tables To 1 Table With Transposing

Feb 9, 2015

There are 2 tables

1. Table Name : TRNNUM Field Name : TRNID
2. Table Name : TRNPRD Field Name : TRNID, PRDID

Sample of tables
Table TRNNUM
TRNID
-----
1234
2565
3458

Table TRNPRD
TRNID PRDID
----- -----
1234 AA
1234 BB
1234 CC
2565 CC
2565 EE
2565 FF
2565 HH
3458 AA
3458 BB

Desired output is
TempTable

TRNID PRDID PRDSEQ
----- ----- ------
1234 AA START
1234 BB START:AA
1234 CC START:AA:BB
2565 CC START
2565 EE START:CC
2565 FF START:CC:EE
2565 HH START:CC:EE:FF
3458 AA START
3458 BB START:AA

View 2 Replies View Related

SQL Server 2012 :: Table With Column Data - Convert Varchar To Int

Jul 8, 2015

I have a table with column "Data" as VARCHAR, with entries like below.

1
11
2
A1
A10
A11
246
AB1
AB10
100
256
B1
B2
124
20
B21
B31
32
68

I want to select the data by converting varchar to int for numeric values and for alphanumeric it should display as it is.

SELECT CAST(dataAS INT) FROM record_tab

getting below error

Conversion failed when converting the varchar value 'A1'

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved