|
The sample query you list has both Department and Vendor in it (you could get departments listed more than once if they use different vendors). You are trying to get a percentage of total for each department but your sample result set lists no percentages and values are listed by vendor. Are you sure you know what you really need?
Given your 2nd line and assuming you are using SQL Server, first calculate a good total sales per department.
SELECT
d.deptdesc,
SUM(ti.totalsales) AS DeptTotal
FROM
tot_item ti
INNER JOIN
dept d
ON (ti.deptcode=d.deptcode)
Now write a query that calculates total sales.
SELECT
SUM(ti.totalsales) AS FullTotal
FROM
tot_item ti
Now put them together
SELECT
dep.deptdesc,
(dep.DeptTotal /
(SELECT
SUM(ti.totalsales)
FROM
tot_item ti)) * 100 AS PercentOfSales
FROM
(SELECT
d.deptdesc,
SUM(ti.totalsales) AS DeptTotal
FROM
tot_item ti
INNER JOIN
dept d
ON (ti.deptcode=d.deptcode)) AS dep
ORDER BY
deptdesc
Note: I am using a more modern form of the JOIN syntax. There are issues with expressing the JOIN in the WHERE clause. You can make this a lot cleaner using variables in a stored proc.
|
|
|
|
|
Sorry - forgot the GROUP BY clause in the aggregate:
SELECT
d.deptdesc,
SUM(ti.totalsales) AS DeptTotal
FROM
tot_item ti
INNER JOIN
dept d
ON (ti.deptcode=d.deptcode)
GROUP BY
deptdesc
This will also need to be modified in the final query.
|
|
|
|
|
You can try something like this:
DECLARE @TempTotal int
SELECT @TempTotal=sum(ti.totalsales) from ti
select d.deptdesc,v.[name],ti.deptcode ,(sum(ti.totalsales)/@TempTotal) as Total
from
tot_item ti,
dept d,
item i,
vendor v
where
ti.itemcode=i.itemcode and
v.vendorid=i.vendorid and
ti.deptcode=d.deptcode
group by v.[name], ti.deptcode, d.deptdesc
ORDER BY v.[name], ti.deptcode ASC
|
|
|
|
|
Hey all,
I'll open with an example:
Lets say I have a table of students...
I can represent each line in the table as a class, which I'll write.
This Student class would contain all of the fields in the table as properties, and maybe a few more functions.
So now lets say I have another table of classes, and I want to write a complex query, which takes information from both tables.
How can I represent this kind of stuff as classes?
Thanks in advance,
Shy.
|
|
|
|
|
It could become something like your Class object having a collection of Student class objects.
|
|
|
|
|
Hi,
Is there any inbuilt function in SQL 2005 which checks whether referential integrity exist in any other table for an particular master record?
Regards,
Vipul Mehta
Sr. Software Engineer
NIIT Technologies Ltd
|
|
|
|
|
sp_helpconstraint stored procedure returns the constraints on the table.
Ex:
 sp_helpconstraint ele_Examination
|
|
|
|
|
how to insert 3 webforms controls data to a sql database in asp.net Just posted ?
hi
i have 3 webforms.In first webform we have 2 textbox ,one is user id and other is password.then there is a continue button.page go to second webform.
in second we have two textbox.one is username and age.and again we have continue button.page go towebform3.again we have 2 textbox,one is use hobbies and user favorite food,here we have submit button.
When we clik submit button,it should get inserted in one table in database.
After clicking submit button he felt that he has to chage some thing(for eg change usrid or password) ,he goes to firstpage from third page and changes then he goes to third page and click submit and now it should be upadated.
thank you.
bye
md riyasath ali
-- modified at 1:20 Monday 25th September, 2006
|
|
|
|
|
riyasath wrote: Expecting anser very quickly
Code Project members do not respond nicely to statements like this.
|
|
|
|
|
// *** Setup DataAdapter with expicit INSERT, UPDATE,
// DELETE "Template" Commands.
private void InitializeCommands()
{
// Reuse the SelectCommand's Connection.
SqlConnection con =
(SqlConnection) _dataAdapter.SelectCommand.Connection;
// Create an explicit, reusable INSERT command
_dataAdapter.InsertCommand = con.CreateCommand();
_dataAdapter.InsertCommand.CommandText =
"INSERT INTO customers " +
"(CustomerId, CompanyName, ContactName, ContactTitle, " +
"Address, City, PostalCode, Phone) " +
"VALUES(@CustomerId, @CompanyName, @ContactName, " +
"@ContactTitle, @Address, @City, @PostalCode, @Phone)";
// Bind parameters to appropriate columns for INSERT command
this.AddParms(_dataAdapter.InsertCommand,
"CustomerId", "CompanyName", "ContactName", "ContactTitle",
"Address", "City", "PostalCode", "Phone");
// Create an explicit UPDATE command
_dataAdapter.UpdateCommand = con.CreateCommand();
_dataAdapter.UpdateCommand.CommandText =
"UPDATE customers " +
"SET CompanyName = @CompanyName " +
"WHERE CustomerID = @CustomerId";
// Bind parameters to appropriate columns for UPDATE command
this.AddParms(_dataAdapter.UpdateCommand, "CompanyName", "CustomerID");
|
|
|
|
|
hi
u did not get my doubt.
I have 3 webforms with two textbox each.in every form there is continue button except for last form where there is submit button.In the third webform when we click submit then all the webforms i.e firt,second and third data should go to database.continue takes to next webform.
After clicking submit ,if he feels he has given some wrong information in firt form,then he click back button in the browser and changes,then it should get updated
thanks in advance
riyasath
|
|
|
|
|
This is more of an ASP.NET question, and you should probably be storing the intermediate results from the first two forms in Session state.
|
|
|
|
|
hi
Thank's for ur reply.
iam new to .net.
can u plz explain the answer briefly.
my another doubt is by using dataadapter can we insert the data in database.?
My another question is by using dataset,where there is a emp table in it, and i want a denpno =10 whole record .how to get that complete record.
Thanks in advance
Byee
riyasath
|
|
|
|
|
Hi all,
I need to know what are my choices to implement the following:
I have a table named "PostedJobs" with a "Status" bit(Open/Close) field. When an Employer posts a job this bit field will be set to Open - on an Employer-chosen specific date this bit field will be set to Close. Then this job(row) must be deleted from "PostedJobs" and inserted into another table named "ClosedJobs" where I keep a log of old posts.
Now, is there any chance to automate the whole process?
Thanks in advance
|
|
|
|
|
If you are using SQL server you can use Sql Server Agent to periodically (say once a day at 00:00:1) run a job (stored procedure). The stored proc could check a table of Jobid/closeDate records and close out any jobs due for closure... if not SQL, then maybe use windows scheduler to run an exe that does the same thing(with or without a stored procedure).
|
|
|
|
|
Thank you very much Rob![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
Hey Rob,
I've scheduled a job in SQL Server Agent and created this step:
declare @ExpirationDate smalldatetime
select @ExpirationDate = min( ExpirationDate ) from PostedJobs where Status <> 'False'
while (select datediff (day, @ExpirationDate, getdate())) <= 0
begin
update PostedJobs set Status = 'False'
end
The problem is that when I tested it it executes successfully but it doesn't update rows that I set their date couple of days later.
Execuse me, this is the first time I use most of these tools.
thanks
-- modified at 21:06 Sunday 24th September, 2006
Altered the code to:
declare @ExpirationDate smalldatetime
select @ExpirationDate = min( ExpirationDate ) from PostedJobs where Status <> 'False'
while (select datediff (day, getdate(), @ExpirationDate)) <= 0
begin
update PostedJobs set Status = 'False' where PostedJobs.ExpirationDate = @ExpirationDate
select @ExpirationDate = min( ExpirationDate ) from PostedJobs where Status <> 'False'
end
worked fine
|
|
|
|
|
Is there a way of building an ADO Recordset from some data?
I need to return a Recordset structure from a function but the data is not available trough a database so the ideea was to load the recordset and return it. I want to do this because for some user settings my application cannot read data from a database and building my own recordset beats going trough all the code and reading settings. I can simply change the method that does the querying.
|
|
|
|
|
Hai friend,
You have to return a recordset from a function.
ur function
_Recordsetptr mRs;
mRs.CreateInstance(_uuidof(Recordset));
return mRs;
Ok try it
|
|
|
|
|
Hello,
I am creating a application that can connect any sql server on the network.
The customer would like a dialog box to be able to find a instance of a server, and then enter the password and username to get a list of all the database they want to connect to.
Does anyone have any examples projects in vb.net that does something like this.
I will be most grateful for any code.
Thanks in advance,
Steve
|
|
|
|
|
Hi Steve,
Refer the below link.
http://www.codeproject.com/cs/database/locate_sql_servers.asp
Regards
Narendra
|
|
|
|
|
Hi,
I was wondering when designing a database with multiple tables with PKs and FKs in place, why would I think to map those FKs and PKs between tables hence making relationships while I can retrieve whatever I need with joins without creating any relationships?
|
|
|
|
|
Hi
ADO.net Support you Option by GUI this help you to make relationships
and can make it by coding
|
|
|
|
|
Creating the relationships ensures database integrity.
If there is a relationship between table A and table B, where table A's PK is a FK in table B, then SQL server will not allow you to delete records in table A that has child records in table B.
I would not be surprised if there were not also performance benefits when joining table A and B aswell but I am not sure if this is the case.
|
|
|
|
|
You can get records using joins from any two tables. But if u are not using PKs and FKs how can u assure that a particular row or record in a child table is associated to a particular row in the master or parent table. By using PKs and FKs database forces a rule on data table so invalid data cannot be inserted.
Joins are used to fetch record from more than 1 tables but donot force the rules on datbase tables.
Do good and have good.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
