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.
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.
In a Stored Proc I am creating the following temp table and index:
CREATE TABLE [dbo].[#ShipTo]( [Ship_to_Num] [int] NOT NULL, [Country_key] [nvarchar](3) NULL, CONSTRAINT [PK_ShipTo] PRIMARY KEY CLUSTERED ( [ship_to_Num] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
The stored Proc runs fine from "exec", but when you batch into a Job it gives the error that "PK_ShipTo" already exists! I even put in a drop table on #ShipTo, but the same effect.
I have an xml file that I need to read out into columns and I'm having a problem with the section that has the persons address in it, a snippit of the file is shown below:-
DECLARE @xml XML SET @xml = ' <PersonStructure> <PersonName> <GivenName>Mike</GivenName> <FamilyName>Myers</FamilyName>
[code].....
Now the problem I am getting is, it is pulling out all the fields except it repeats the first part of the 'PersonAddress' field 3 times instead of giving me the whole address?
Is there a way of gaining access to all the parts within the 'PersonAddress' node even though all 3 parts within it are named the same (Address)?
I need to get the replacement records between the 2 tables. I have table A and table B with same structure. I have 5 fields. Table A has 50,000 records and table B has 20,000 records. I have fields id , name, address,meter_flag,end_Date.
Some of the records in Table B are just replacement records of table A. I mean for example I have records like this in Table A
id name address meter_flag end_date
23 john 1201 salt lake dr no 2011-12-28
24 tom 1222 gibson ln yes 2011-12-16
25 alex 1334 qayak dr no 2011-12-17
In Table B
23 john 1344 mc kinney st yes 2011-12-18
24 tom 1222 gibson ln yes 2011-12-16
56 gary 1335 pruitt rd no 2011-12-18
25 alex 1334 qayak dr no 2011-12-17
So here in Table B i have an update for john with id 23 in table A in address field and meter_flag has changed to yes. There is new record with id 25 in table b but that is not in table A. so I need to find all these difference records by querying these 2 table
Hi all,I am writing a database using SQL as the back end and Access as thefront end. My project is to create a rota for when people have towork. The problem I have is setting up the tables so I can query themcorrectly.I currently have a staff table which holds the staff_id and staff_namefields. I have a worktime table that has the staff_id, date, starttime, end time as fields. What I would like to do able to do is have aquery that returns the times a person is working in date order alongthe rowi.estaff_id, date, day1_start, day1_end, day2_start, day2_end etc..1, 8/1/04, 9:00, 18:00, 9:00, 18:00, etc..I can't seem to work out a query that will do this easily. I canthink of ways of manipulating the data heavily but that defeats theobject. I wonder if my table structure is incorrect. Is it goodpractice to have a table where each field is a date? Seeing this willneed to be referred to for year on year comparison it seems a bad wayof doing it. Basically is this the correct structure for a diaryapplication?Any help from you great people out there would be superb! Thanks inanticipation.CheersMike
SELECT 'A' as Item, '1' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL SELECT 'A' as Item, '1.1' as Version, 1 as Counter, '01-02-2011' as CreatedDate UNION ALL SELECT 'A' as Item, '1.2' as Version, 2 as Counter, '01-03-2011' as CreatedDate UNION ALL SELECT 'B' as Item, '1.2' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL
[Code] .....
I want to write a script where if a user enters the version number, then the output should show all the upward and downward nodes..e.g. if a user selects '1.2' version then following should be the output
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:
How do I install / setup SQL server 2005 so that I can use it for my school projects. I am learning C# and web service programming that supports connections to an SQL database. I need to be able to use it as a "local host" that can connect to Visual Studio 2005.
What I want to do is add a column that has 5 values and create a row for each value
I want it too look like this -
024Swanson, Ronrswanson@tv.com a 024Swanson, Ronrswanson@tv.com b 024Swanson, Ronrswanson@tv.com c 024Swanson, Ronrswanson@tv.com d 024Swanson, Ronrswanson@tv.com e
[Code] ....
Currently all my query looks like is this -
SELECT EmpID, LastFirst, Email FROM dbo.EmpList WHERE (Active = 1)
The other day developer asked me to create a new table that had a FK constraint to an existing largish table on the largish table's primary key.
When i ran the script, it took it about 4 seconds to complete, and in the meantime i had 3 deadlocks show up from other processes attempting to use the table the FK was being created from. The new table was empty at this point.
I've never seen this behavior before so I'm curious if creating a new FK constraint will cause a table lock on the source table for the FK.
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database which is configured and set on the hosting web server. Right now for managing my sql server 2005 database, i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database, it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors. Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still the problem persist.
we have a table in our ERP database and we copy data from this table into another "stage" table on a nightly basis. is there a way to dynamically alter the schema of the stage table when the source table's structure is changed? in other words, if a new column is added to the source table, i would like to add the column to the stage table during the nightly refresh.
Hey, I need to retrieve info from a database and display it using a repeater control. No problems there! But, I need to add data before displaying it, and I don't mean add data to the database but rather to the repeater control. For eaxample: I have a simple database containing two fields: [date] and [event]. The repeater will display these events in a monthly view. That is: the repeater will have 31 rows and the events will be displayed next to the day it happens. Now if there's nothing happening a certain day then I need to add that day manually because it will not be bound, right! See my problem? In other words, how do i loop through a records when using the SqlDataSource? Thanks,Björn Andersson
I am trying to calculate a whole school total tuition. I've got helped with individual tuition total which is
create proc sp_individualtuition as select c.contractNum, (c.tuition-((sum(d.discountPer))* c.tuition)) as totaltuition from contract c, contractDiscount cd, discount d where c.contractNum = cd.contractNum and cd.discountNum =d.discountNum group by c.contractNum, c.tuition
however how can i calculate the whole school total tuition (adding all the individual total tuition)?
my contract, contractDiscount and discount DDL are down below
create table contract( contractNum int identity(1,1) primary key, contractDate smalldatetime not null, tuition money not null, studentId char(4) not null foreign key references student (studentId), contactId int not null foreign key references contact (contactId) );
create table contractDiscount( contractNum int not null, discountNum char(3) not null );
Hi there,I have learned to create very, very basic queries at work, but I wouldlike to expand my knowledge. I live in Northern California, doesanyone know of any good schools and/or literature that could help mein this quest?Thanks a bunch,Juana
On a new project i need to create possible ranges between a specific interval.
suppose i have this main product:
main product : LY E67F
Also, in first level i have a table classify by Group depending on Intensity.
table group Group intensity AA 1120 AB 1400 BA 1800 BB 2240 CA 2800
I need to create these diferent options:
1 option : AAAB or AABA or AABB or AACA 2 option : ABBA or ABBB or ABCA 3 option : BABB or BACA or BBCA or beginning from the end 1.option CABB or CABA... or beginning from the middle 1.option BBBA or BBAB and so on.
I fact, i need to find all available options possibles to build article code, like a matrix.
I'm a college student working on a database project using MS SQL Server 2005 Express Edition.
The program (SQL SMSEE) is installed on both the computers in class and on two of my computers at home. The first installation resulted in the "remote connections" error. No matter what I do, I can't get the program to fully load. So I tried installing on another computer at home. The second installation went well. Didn't do anything different from the first installation, but any hoo....................
My 2-week old problem is this - I save my database that I do in class on my thumbdrive. Last class, I saved all the files that had my database's name on it on my thumbdrive--.mdf, .log, .bak, etc. On the second home computer, I cannot open the databases that I work on at school. Even my professor is stumped on this one.
Here is what I'm doing......
After I connect to the server, I right-click "Databases" then left-click "Restore Database".
In the "To database" box, I enter the name of the database, as I saved it at school.
In the "To a point in time" box, I leave the default "Most recent possible" entry.
I select "From device" as the location of backup and click the "..." button. The file is on my thumbdrive, so I click "Add" and select the location on my usb (with the .bak extension) and click OK.
I pick the most recent file checkbox and click "OK".
The green progress circle goes to 50% and then gets stuck. The database never opens and the Object Explorer shows the database name followed by "(Restoring...)". So if I try to do anything else with it, I get an error message stating the database in the middle of a restore and that I have to wait until its done. Well, of course it never finishes.
Please help me. Right now I am stuck doing duplicate work at home and at school and am making little to no progress. The final project is due on 26 March and right now I only have my tables, a few attributes and a couple of relationships. And I have a LOT more work to do.
In a sql server 2012 database, I have a field called date of birth that is stored in a tha that is stored in a datetime format. I need to determine the age of various students in the school system. Thus can you show me sql to that I can use to determine the a child is currently?
creating the missing records in a date/time range.
However, I need to return different groups for each span of records.
here's some data....
aaa1 aaa7 bbb2 bbb5 bbb6
The numbers are the hour of the day.
I need to return
aaa 0 0 aaa 1 1 aaa 2 0 aaa 3 0 ... bbb 0 0 bbb 1 0 bbb 2 1 ... and so on.
I've got a numbers table and I can left join with it but I just get nulls for the missing hours instead of having it as above.....I can't think of a way of repeating the groups for each of the 'missing' hours - other than creating a length insert statement to fill in the gaps....unless that is the only way of doing it.
In the T-SQL below, I retrieved data from two queries and I've tried to join them to create a report in SSRS 2008 R2. The SQL runs, but I can't create a report from it. (I also couldn't get this query to run in an Excel file that connects to my SQL Server data base. I've used other T-SQL queries in this Excel file and they run fine.) I think that's because I am creating temporary tables. How do I modify my SQL so that I can get the same result without creating temporary tables?
/*This T-SQL gets the services for the EPN download from WITS*/
-- Select services entered in the last 20 days along with the MPI number and program code.
SELECT DISTINCT dbo.group_session_client.note, dbo.group_session_client.error_note, dbo.group_session_client.group_session_id, dbo.group_session_client.group_session_client_id, dbo.group_session.signed_note, dbo.group_session.unsigned_note into #temp_group_sessions FROM dbo.group_session_client, dbo.group_session WHERE dbo.group_session_client.group_session_id = dbo.group_session.group_session_id
-- Form an outer join selecting all services with any group notes attached to them.
select * from #temp_services LEFT OUTER JOIN #temp_group_sessions on #temp_services.group_session_client_id = #temp_group_sessions.group_session_client_id ;
-- Drop temporary tables
DROP TABLE #temp_group_sessions; DROP TABLE #temp_services;
Hi allI have two tables in SqlServer with Exactly Same Structure,I want to Copy all Records fromone of them to another one.I came across to "Insert....select..." statement But i have two problem 1) I don't know any thing about Columns name!!! i just know they have same structure and as far as i know , "Insert...select..." need the Column list to operate correctly, am i right? 2) these two table have One Prinary Key column with IDENTITY feature. Any Help Greatly appriciated.Regards.
I am trying to find a way to add into a table a flattened (comma seperated list) of email addresses based on the multiple columns of nformation in another table (joined by customer_full_name and postcode.
This is to highlight duplicate email addresses for people under the same customer_full_name and Postcode.
I have done this using a loop which loops through concatenating the email addresses but it takes 1minute to do 1000. The table is 19,000 so this isn't really acceptable. I have tried temp tables, table variables and none of this seems to make any difference. I think that it is becuase i am joining on text columns?
Structure Number1 Category: CategoryId(P.K.), UniqueName, CreatedDate, ModifiedDateCategoryNative: CategoryId(F.K.), LanguageId(F.K.), NativeName, Description, Importance, IsVisible, CreatedDate, ModifiedDate Structure Number2 Category: CategoryId(P.K.), UniqueName, CreatedDate, ModifiedDateCategoryNative: CategoryNativeId(P.K.), CategoryId(F.K.), LanguageId(F.K.), NativeName, Description, Importance, IsVisible, CreatedDate, ModifiedDate Can anyone tell me that between above 2 structure what is better and why? I just add CategoryNativeId(P.K.) in Number 2 structure.
How can I setup a table structure for the diagram shown in the attached bitmap?
1) I need to create a product using labour and materials. 2) I need to create hardware using labour and materials. 3) I need to be able to include some hardware in some products. 4) Some materials in the product are made of a culmination of materials. E.G., Concrete is made of sand, stone, and cement.
Any suggestions?
So far I have :
USE NORTHWIND
Create Table tbProducts ( ProductID int NOT NULL, Product varchar(50) )
go
ALTER TABLE tbProducts ADD CONSTRAINT tbProducts_pk PRIMARY KEY (ProductID) GO
CREATE Table tbMaterials ( MaterialID int NOT NULL, Material varchar (50) )
GO
ALTER TABLE tbMaterials ADD CONSTRAINT tbMaterials_pk PRIMARY KEY (MaterialID) GO
CREATE Table tbLabour ( LabourCode char (2) NOT NULL, Labour varchar(50) )
GO ALTER TABLE tbLabour ADD CONSTRAINT tbLabour_pk PRIMARY KEY (LabourCode) GO
CREATE Table tbProductMaterials ( fkProductID int NOT NULL, fkMaterialID int NOT NULL, Quantity Float NOT NULL )
GO ALTER TABLE tbProductMaterials ADD CONSTRAINT tbProductMaterials_pk PRIMARY KEY (fkProductID, fkMaterialID) GO
I'm new in creating tables. and I was asked to create table(s) to capture information using SQL server 2005 (express edition)
please read below and share your thoughts and opinions o what's the efficient and best way to structure the tables.
I need to capture user's input daily and every hour, text can be from 20-100 characters, and my company is expecting to have 50,000+users in the future...Also the hrs's text can be stored for max 120 days, after that data would be deleted from the table(s) An example is put all together, (not sure if it is the right and efficient way)
An example is put all together, (not sure if it is the rigth and effectient way) and if database would support that.. and if it will be fast enougth to search for data
(table structure) - (example with 3 users) userId | timestap | message 1 12/12/2007 10:00 some message 1 12/12/2007 11:00 some message 1 12/12/2007 12:00 some message 1 12/12/2007 13:00 some message 1 12/12/2007 14:00 some message 2 12/12/2007 10:00 some message 2 12/12/2007 11:00 some message 3 12/12/2007 12:00 some message 3 12/12/2007 13:00 some message 3 12/12/2007 14:00 some message ...etc..
