table a
id pct
1 0.1
2 0.24
3 0.5
table b
year amount
2001 10
2002 20
2003 30
2004 23
2005 67
2006 80
I want to create a table ret
wiht this
year newamount
2006 80*0.1+67*0.24+23*0.25
2005 67*0.1+27*0.24+30*0.25
2004 23*0.1+30*0.24+20*0.25
2003 30*0.1+20*0.24+10*0.25=9.9
How can i do that.
Thanks.
Jeff
Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:
-------
Fixture
-------
fix_id
fix_date
fix_played
----
Team
----
tem_id
tem_name
-----------
TeamFixture
-----------
fix_id
tem_id
homeorawayteam
goals
It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.
Fixture contains the details about the fixture like date and fixture id and has it been played
Team contains team info like team id, name, associated graphic
TeamFixture is the table which links the fixture to it's home and away team.
TeamFixture exists to prevent a many to many type relationship.
Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!
hi
i am new to t-sql. the problem is i have to subtract a field from a column A in table X from a field in Column R from Table Y and put the subtracted value in the Column K in the Table Z
ex:
Table Name: Order
Field Name: Boxes
Table Name: Despatch
Field Name: Boxes
Table Name: Balance
Field Name: Boxes
the problem is i have to subtract the values(Boxes) in the table Despatch, from the values(Boxes) in the table Order, and the subtracted value has to be put in values(Boxes) in the table Balance.
can any one help?
one more thing: Where i can get the basics of t-sql
regards.,
sekar.r
Hello, I am trying to achieve a query results with not much luck. I am struggling the query design of how to construct to get the right results.
My table has the following data
RoomID Subject StartDate Duration(min) EndDate
201 test 28/09/2007 07:00:00 180 28/09/2007 10:00:00
202 test 28/09/2007 09:00:00 240 28/09/2007 13:00:00
The best way to describe is a scheduling system with the StartDate, Duration, EndDate.
I am trying to query for datetime periods which are allocated, eg the table records show from 7am - 10am and 9am - 1pm are allocated.
When I query on StartDate >= 28/09/2007 07:00:00 AND EndDate <= 28/09/2007 13:00:00, record 201 & 202 shows which is correct because they are within query parameters and are allocated.
When I query on StartDate >= 28/09/2007 07:30:00 AND EndDate <= 28/09/2007 13:00:00, record 202 only shows which is correct because record 201 StartDate is before 07:00:00.
But my problem is I require record 201 to show because the time period between 07:00:00 and 09:00:00 is allocated and the query parameter is 07:30:00 which is still between 07:00:00 and 09:00:00.
I have researched where on similar situations, the use of duration is used, but I am unsure on its application to achieve results.
I have also read of other situations where a lookup table should be used, but again I cannot get my head around the application of alookup table to query the resuults.
Does anyone have any theories, design thoughts or real work solutions they can help me with.
Thanks
Peter Smith
I am designing an ASP.NET app that can be used to keep track of
attendance at office hours for a class. The purpose of this is
that we need to know if a student is attending office hours by
different people (so that we can flag them as "in trouble"). I
don't know if I have chosen the best database design, and I'm lost as
to how to accomplish a query I need.
I have a table HoursAttendance that has the following design.
Column_Name Data_Type Length Allow_Nulls
TA
char
4
n
Date
smalldatetime 4
n
Start
smallint
2 n
End
smallint
2 n
Student1
bit
1 y
Student2
bit
1 y
Student3
bit
1 y
Student4
bit
1 y
Student5
bit
1 y
I chose to have the students as columns because the students don't
change, and then you add rows of office hours. If students are
the rows, then you would be adding columns as the semester continued
which I thought was odder...? I'm completely open to suggestions
on Database Design, because I really wasn't sure.
Ok, so now I need useful queries. The one that I am stuck on (and
also the first one besides select * from HoursAttendance) is that I
want the names of Students who have attended more than x office
hours. So I need something like
select <column name> from HoursAttendance where count(sum(<column name>)) > x
Granted a better table design could help with this. I'm
relatively new to design, so constructive criticism is desired please
Dear All
I have posted this a few days ago, but probably didn’t describe it properly so I’ll attempt it again.
I’m wondering if it’s the design that needs to be changed or I simply can’t put this together.
I have 3 tables.
1. people (peopId, peopFName, peopSName etc.)
2. codes (codeId, codeName)
3. codedPeople(codePeopleId, peopId, codeId)
Codes represent different skills that people might have, like:
Technology-Founder
Technology-Chief Executive Officer
Technology-Systems Designer
etc.
people, clearly holds data about people.
CodedPeople holds data on which people in the db are coded what skills.
What I need is a query that returns all distinct people records and takes a number of codeNames as input.
So if I have person1 and person2 in the db and they are coded as:
Person1 is a t-Founder
Person1 is a t-CEO
Person2 is a t-Systems Designer
Query 1, looking for all t-CEO
Result 1: person1
Query 2, looking for all t-CEO AND t-Founder
Result 2: person1
Query 3, looking for all t-CEO OR t-Systems Designer
Result 3: person1, person2
Query 4, looking for all t-Founder AND t-Systems Designer
Result 4: -
I have:
[ code]
SELECT people.peopId, peopFName, peopSName, peopPhoneHome, peopPhoneMobile, peopEmail, codes.codeId, codename
FROM people INNER JOIN codedPeople ON people.peopId = codedPeople.peopId
INNER JOIN codes ON codes.codeId = codedPeople.codeId
WHERE ( parameter )
ORDER BY peopSName, peopFName
[ /code]
And I add in parameters from my front end to the WHERE clause, like in the example:
Parameter = ( codeName LIKE 't-CEO' ) OR ( codeName LIKE 't-CFO' )
This works fine when using OR but doesn’t work when trying to use AND in a sense of a person is coded with skill1 AND skill2 AND skill3 etc.
Any idea how to implement what I need to achieve?
Even though I select "Column Names" in Design View when creating a query (or view), only "* (All Columns)" appears in the table box.
In InfoPath, when I connect a combo-box, err drop down box, to the database, I am unable to connect directly to a table... no tables are shown. If I select a different database, these problems do not exist.
I can not find any setting to allow these columns to be shown in the design view or any setting that will "expose" the tables in InfoPath.
I tried creating a new database and exporting the data, tables and data, from the troubled DB to the new DB; however, the new DB exhibited the same behaviour. The system tables, Master and Model, have the same behaviour. Please help me with your ideas and suggestions... thank you very much for your time.
This database was upsized from Access 2003 to SQL Server 2000 SP4.
rogge
Hi all,
I have a couple of reports with predefined queries. When I try to edit a data set query it goes directly to query design mode losing the query I already had. As you can see this is a real problem because I can't edit queries. How can I switch off design mode? any ideas are pretty much appreciated.
Cheers
i have a number of business programs. Each program is started anew at the beginning of each fiscal year. each program has a number of goals and customers subscribe to the goals.
i have to pull all this info out of the database.
i have a cursor that gets the first program, inside this program i have a cursor that gets the first period, and inside that i have a cursor that gets the info on each goal.
program cursor
{
period cursor
{
goal cursor
}
}
}
}
this takes ages ( hours and hours ) to run. is there any way i could have designed this using joins and simple selects to make it more efficient??
in SQL Query analyzer i get the desired result for the following querry succesfull.
But can any one help me in getting the same result in design view so that i can save this view and get the required result on my aspx page.(Web page)
A beginer !!!! not much familiar with MS SQl.....HELP>>>
USE nfxdash SELECT *, DATEDIFF(day, Date_Opened, getdate()) AS no_of_days
FROM nf_Tickets
Dear All,
I’m wondering if it’s the design that needs to be changed or I simply can’t put this together.
I have 3 tables.
1. people (peopId, peopFName, peopSName etc.)
2. codes (codeId, codeName)
3. codedPeople(codePeopleId, peopId, codeId)
Codes represent different skills of people, example the sort of job functions they’ve held in their employment. Like:
t-CEO,
t-CFO
t-Founder
etc.
people, clearly holds data about people.
CodedPeople holds data about which people are coded. So person1 can be coded as t-CEO as well t-Founder, and person2 coded as t-CFO
What I need is a query that returns all distinct people records and takes a number of codeNames as input. So if I throw in t-CEO OR t-Founder I get person1, again if I define t-CEO AND t-Founder I get person1.
However when I add t-CEO OR t-CFO I get person1 and person2 but when the query takes t-CEO AND t-CFO I get no result.
I can’t seem to come up with anything that would give me a good starting point. Is there a design fault here? All opinions are much appreciated, thanks in advance!
I have an opportunity to rebuild a database model with the express purpose of improving query performance. So given the following I have a few questions.
Table A (~500M records)
Primary Key Field (int)
Field 1 (varchar)
Field 2 (varchar)
Field 3 (varchar)
Field 4 (varchar)
Field 5 (varchar)
Table B (1B+ records)
Primary Key Field (int)
Foreign Key Field (int)
Field 1 (varchar)
Field 2 (varchar)
Field 3 (varchar)
Field 4 (varchar)
Field 5 (varchar)
* Assumed: Tables are inner joined on all queries. The database is readonly.
-- Most of my lookups are based on querying Field 1 of Table A. The data content of Field 1, Table A is 90% unique.
1) Would it be more beneficial to put the clustered index on Field 1 instead of the PK field in Table A?
2) Can an Identity column be non-clustered?
3) Alternatively, would it be beneficial to build a separate lookup table with just the PK & Field 1 of Table A, with a clustered index on the lookup table Field 1 which I join on Table A? (did that make sense?)
-- I have a secondary lookup that performs queries on Fields 1, 2, 3, 4 & 5 of Table B
1) Would it be more beneficial to create an additional indexed lookup column of the concantenated values of Fields 1-5 of Table B versus a covering index of all 5 columns?
2) Does a clustered index have to be unique?
3) Would a clustered index be more beneficial over Fields 1-5 or the special lookup column versus the PK or FK fields?
4) Would creating a special lookup table with just the requisite fields be more beneficial?
An extra question. The existing data model uses the CHAR datatype for all columns less than 9 characters wide and the columns are set to allow nulls. This requires every select statement to COALESCE() and RTRIM() all these columns. I intend to make all (affected) columns VARCHAR, NOT NULL with a default value of a 0-length string.
Will this enhance query performance?
Thanks in advance for any insight.
I have a rather complex query (to me at least) that I need to create but I am unsure of where to start. The query requires me to copy existing data into a new row (which will then create a new ID) as well as update all existing records with the newly created id. More specifically, I need to separate the data associated with LocationID 219 from it's parent, CompanyID 992.
Ideally I want to copy the data associated with LocationId 219 and then make a new CompanyId with the copied data (which will also create a new LocationID). Since this new record is no longer going to be associated with CompanyID 992 I will want to remove/delete/drop it from that record.
Finally, and perhaps most difficult of all, I need to update all tables that reference the old ID's together (992 / 219) to reflect the newly created Company ID and Location ID.
I am wondering if there is a direct query in this case:
I am developing a program to a company which simply sells services
One service may have different prices for different types of clients
The price of any service for any client can change at any time, and I should be able to trace these changes at any time
I made the following tables (simplified): (asterisk for primary key)
(Table) (Fields)
CLIENT_TYPES : ID*, ClientTypeName
SERVICES : ID*, ServiceName
PRICES : ServiceID*, ClientTypeID*, Price, Date*
ORDERS : ID*, Date, ClientTypeID
ORDER_SERVICES : OrderID*, ServiceID*
The field in bold is the area of the question
This is a sample data in the PRICES table:
ServiceID ClientTypeID Price Date1 1 100 1/1/20072 1 150 1/1/20071 2 90 1/1/20072 2 135 1/1/2007
Now if I want to update a price of service 1 for clienttype 1, I add the following row:
1 1 100 1/1/2008
So one product for one client can have any number of prices with different dates
The following query:
SELECT * FROM PRICES WHERE ClientTypeID = 1
will retrieve all prices with repeats for a specific client (#1 here)
What I want is a query to retrieve the most recent prices for specific client for all products, even if a query on query
If there is commemts on table design please tell me
thanks for any one who provides help
ASP.net 2.0 (VB), SQL Server 2005:While creating a new TableAdapter in design section, I'm using the query builder and trying to write a query within "LIKE" statement as below -Example1: SELECT * FROM table WHERE field LIKE @'%TextBoxData%'Example2: SELECT * FROM table WHERE field LIKE '%@TextBoxData%'
but these query doesn't work...error in building query...any clue to make it work? If I remove "@" sure the query will work with normal but '%TextBoxData%' will become a hardcoding value...this is not I want...I want make the TextBoxData become a flexible value depend on the data what I enter in my text box like 'abc,123' not like 'TextBoxData'...
I know normally it supposed to be like:
"SELECT * FROM table WHERE field LIKE '%" & TextBoxData.Text & "%'"
It can work when in Code Section, but not at this time...because now i'm trying to made it with "Query Builder" for TableAdapter " in Design Section...hmm did you get what I mean? Sorry for my bad english
Thanks in advance
I am querying a tableA with 1.8 million rows, it has id as its primary key and is a clustered index. I need to select all rows where I order by lastname. Its taking me 45 seconds. Is there anything i can do to optimize the query.Will creating a fulltext index on lastname If so, can you give me an example on how to create a full text index on lastname?
[Project1].[Id] AS [Id],Â
[Project1].[DirectoryId] AS [DirectoryId],Â
[Project1].[SPI] AS [SPI],Â
[Project1].[FirstName] AS [FirstName],Â
[Project1].[LastName] AS [LastName],Â
[Project1].[NPI] AS [NPI],Â
[Project1].[AddressLine1] AS [AddressLine1],Â
[Project1].[AddressLine2] AS [AddressLine2],Â
[code]...
Good morning all,
I have searched everywhere for this and I can't find any information on it. When I use the Query Builder, in the top pane, the Filter pane, there are five columnsimension, Hierarchy, Operator, Filter Expession and Parameters.
In the dropdown for Operator, one of the choices is MDX. I cannot find any documentation on how to use this operator. I want to limit my filter to SELF_AND_AFTER, and I am hoping that this is possible using this operator. I know how to hand code it, but I have a ton of other tweaks to make to the query, so I want to use the Query Designer as much as I can.
Does anyone have any links to documentation on how to use this operator?
Thanks,
Kathryn
I have a very frustrating problem. But given that this is a development forum, I'll keep that for my 'Envelope Lickers Rehab' and instead talk about an issue I'm having.
I have a very large query (line-wise) that executes in less than a minute when run from the SQL Management Studio as well as via Excel Services.Â
It is a stored procedure with one parameter. When I try to prime the designer with this query it gives me the following message:
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated."
I've considered cracking open the xml source and manually creating the fields, but I'd rather not go there.
When  I click on the DataSet Query Desginer there is an Error Source:.NetSqlClient Data Provider. why this error comes and I have executed the SQL query in SSMS succesfully and the result comes. However this is causing the report from the report manager to fail.
View 8 Replies View RelatedHow to design at database level such a way so that when I implement a SQL query that returns one hundred thousand rows only display 25 rows at the client (Web page at a time). How can I accomplish this?
Once I display first 25 rows then how do I bring next 25 rows and so on. Can it be done via paging or there are other techniques. However I asked to design this in the database level. I am using MS SQL Server 2008. Front end Visual Studio 2010. I use C# for coding.Â
So I have a query that need to find the most recent datetime record each day for a customer. So I have a query that looks like this:
SELECT
dhi.[GUID],
dhi.[timestamp],
la.[bundle_id],
dhi.[value]
FROM
[dbo].[DecisionHistoryItem] as dhi WITH(NOLOCK)
[Code] ....
I have a scenario like below
Product1
Product2 Product3
Product4 Product5
Product1 1
1 0 0
1
Product2 1
1 0 0
1
Product3 0
0 1 1
0
Product4 0
0 1 1
0
Product5 1
1 0 0
1
How to design tables in SQL Server for the above.
I would like to create a table called product. My objective is to get list of packages available for each product in data grid view column while selecting each product. Each product may have different packages type (eg:- Nos, CTN, OTR etc). Some product may have two packages and some for 3 packages etc. Quantity in each packages also may be differ ( for eg:- for some CTN may contain 12 nos or in other case 8 nos etc). Prices for each packages also will be different that also need to show. Â How to design the table..Â
Product name  : Â
Nestle milk |
Rainbow milk
packages  :
CTN,OTR, NOs |
CTN, NOs
Price:
50,20,5 |
40,6
(Remarks for your reference):CTN=10nos, OTR=4 nos Â
| CTN=8 Nos
Hi all.
I'm going to create a big DB that will hold important info
(the usual stuff - Clients, Products, Orders...)
I wonder where should I use the IDENTITY field,
for example - on Orders I will have Order_ID...
(and where does SQL server the numbers of a deleted records)
My fear is that IDENTITY fields will go wrong somehow so I can
loose connections within the tables
(maybe when restoring my DB to some other locations... with DTS... or other issues)
1. When should I use IDENTITY field ?
2. If I do NOT - how can I lock a record when I add a new one
Hi,
I have 7 web forms wizard. after the user edit the fields, I need to keep a history log of the changed data only.
I am thinking of the following structure
log_id, user_id, field_id, old_value, new_value, transaction
Where field_id is a number indicates the changed field.
for example 1> First Name, 2> Last Name etc
Where transaction is ADD, DELETE, EDIT
Also i need to keep history of the details
for example an enterprise has 10 branches.
State, employees
NY, 1000
MD, 500
My log table will look like this
log_id, user_id, field_id, old_value, new_value, transaction
1, 1, 1, , Mick ,John , EDIT
2, 1, 3, ,NY , HI , EDIT
3, 1, 3, ,MD , , DELETE
4, 1, 4, ,500 , , DELETE
5, 1, 3, , ,NV , ADD
6, 1, 4, , ,700 , ADD
Is there a better way to log changes?
Thanks
I'm going to create a hardware/software inventory program to keep track on all the computers at the office. This program should be able to search the database using queries like "all computers with a cpu faster than 500Mhz".
I want some tips on my database design.
One solution is to create columns for each piece of hardware, i.e. cpu, ram, hdd etc etc. Then just run simple SELECT queries against them. The problem is that a computer may have many HDDs/CDs etc, and also other type of equipment may be entered in the database like switches and routers.
Another solution is to specify the valuetype + value in one table having a relation to another table containing the actual machines/routers/switches. The problem here is that I can't do numeric comparisons this way since "11" is less than "2", ("all computers with a cpu faster than 500Mhz").
Any suggestions?
Table A
[ID] [SalesmanEmail] [SalesmanName]
Table B
[ID] [QuoteNo] [SalesmanID]
Table C
[ID] [Product] [QuoteNo]
Program A creates the records for Table C. In the process it reads from Table A and Table B.
Program B reads record from Table C. Program B now needs [SalesmanEmail] field.
The suggestion is to add [SalesmanEmail] to Table C. Is this good or bad design?
hello,
i am working on a project with another guy as well. well, we have a table called lets say student and student_unit where student_unit holds the unit names the student is doing. and i was planning to use the same student_unit table to hold the result of that unit for that student as it is the case of adding one field but this guy who happens to be my boss thinks that result should be separated. but is it a better idea to make a separate table just for that result which is totally dependent on unit and student? that doesn't make any sense to me.
if asked why, he says for performance, modularity and object oriented approach and blah blah and honestly i think he knows nothing. so i would like u guys to tell him if there is really a need to create a separate table for attributes which is totally dependant on the same PK? having five more attributes on the same table hurts than creating a new table?
I think I might have dug myself in a hole here, so could use some advice on database design.
Currently, I have a table that records meeting minutes. The design is:
Code:
minutesid | int | 4 | no nulls
minutescat | varchar | 255 | allow nulls
minutesnotes | text | 16 | allow nulls
What this does is set up a minutes category, such as "Members Present", and the notes for that category, such as "Mrs. Peacock, Mr. Green, Professor Plum, Colonel Mustard, Miss Scarlett". Each individual category has it's own ID.
I need to develop an archive of each of the minutes. So if there are 15 categories and associated notes for the 15 OCT 2007 meeting, how can I design the archive table to include all of those categories and notes into one record for 15 OCT 2007, then for 15 NOV 2007 and so on ...?
Hope that makes sense. Thanks in advance ...
I have a table to store members. For each member, I have flags like manager, verified, etc. How should I be storing the flags? As individual columns in the same table and using bit datatype or creating a new table called MemberStatus and creating 1 row for each flag?
Thanx in advance.
Premal.
Hi,
I need to design a database, but i i just don know if i am doing it correctly.
Basically, i have an equipment which is sending me a lot of gauges readings (high limit, low limit, current level), electrical readings, hydraulic readings, alarms levels such as Fuel level, temperature level, etc etc, about 200 different types of readings, continuously. I may have different types of equipments and although most of the data are the same, some data types and its availability may be different too., depending on the type of equipment.
I want to keep this readings in a database, and this database will be updated constantly.
Should i create all of this different gauges readings in one big single table? If i do so, i only have one long row of data then for a particular equipment.If i put in different table, i can't see how i can create relationship among my tables with such kind of data.
Can you help on the possible ways of putting this equipment real time readings into DB?
I am trying to create a database that is used to create/store estimates for a manufacturing company. There are many things to consider in this estimate but I will isolate this question to the takeoff itself:
There are several categories in which costs are estimated and they are as follows : Product(s), Site Work, Transportation (Shipping), Field Materials, etc...
Should each of the above have their own "Takeoff" table, or would using one table and a gategory table be a better way to go?
Mike B
The data that I have is the following:
Car Manufacturer Factory CarCode
---------------- ------- -------
Ford Houston F-Hou
Ford Reno R-Hou
Chevy Houston C-Hou
Chevy Las Vegas C-LV
Honda SLC H-SLC
Ford SanFran F-SF
Chevy Miami C-Mia
I have a database design implemented already, but I'm receiving some disagreement about it from a co-worker. The way I see it is you have a car manufacturer and a factory location that are both unique, so I created a lookup table for each. I then created a branch table for the many-to-many relationship that exists between Manufacturer and Factory, and within that branch table I placed a column for CarCode, because it seems to me that Manufacturer and Location both determine the CarCode.
I also have an autonumber field within the branch table, which I then use for foreign key relationships.
Is there anything wrong with my design here?
Thank you in advance for your help!