|
Ok I created a application that keeps track of our "tickets" at work.
Now they were wanting to add something where it auto assigned the tickets when the user submitted them.
How would I do this in SQL?
I have a "Admin" table:
ID
Name - Name of person
Available - Are they here? (This will be either TRUE or FALSE)
Area - Area that they work (This will be either Telecommunications or System Administrators)
Now I have my other table that when a user submits a ticket it adds it to this table:
"User_Submit" table:
ID
Users_Name
Users_Email
...
...
...
Assigned - (Name of user assigned to (from the Telecommunications or System Administrators branch)
Type - (What it is... will either be Telecommunications or System Administrators)
So when a user submits a ticket I need it to do the following:
Get a count from the User_Submit Table of all names in the ASSIGNED column WHERE the Type = System Administrator.
So lets say:
John Doe - Has 10 tickets
Jane Doe - Has 9 tickets
John Smith - Has 11 Tickets
So how do I search that table and get that Jane Doe has the least amount of tickets. SO then if Jane Doe is available (from the Admin Table) assign it to her.
Otherwise if she is not available assign it to the next person with the least amount of tickets (if they are here)??
???
Ive racked my brain on this... cant figure it out...
|
|
|
|
|
hi Jacob
Well you have explain a lot of things and you are starting to confume me. ok ,let me help you interactively. i will ask you questions where am not sure what you mean.
Jabob Write:Now they were wanting to add something where it auto assigned the tickets when the user submitted them
Yes we have that in SQL but we call it Boolean datatype. but i dont recomend it i just use bit datatype, if its 0 False and if its 1 then true. but still its your choice.
Jabob Write: Type - (What it is... will either be Telecommunications or System Administrators)
Is this a question?
In this case in your Id , you need to make it an identity column. something like this
Create Table Mytable
(
U_ID int indentity(100,01) not null,
--you can continue with other fields.
)
Jabob Write:Available - Are they here? (This will be either TRUE or FALSE)
Get a count from the User_Submit Table of all names in the ASSIGNED column WHERE the Type = System Administrator.
SELECT count(*) as [count] FROM User_Submit
WHERE Type like 'System%'
Hope this Helps
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
First find the lowest ticket number and name of user in table Admin which are (username) avilable
select top 1 ticketes,name from Admin where avilable = true order by tickets asc
Second do insert in your table User_Submit, if you need to insert usrname which is avilable and with the lowest tickets numbers then use query:
declare @username as varchar(255)<br />
set @username = (select top 1 name from Admin where avilable = true order by tickets asc)<br />
<br />
Insert into User_submit values (val1,val2,val3,...@username,nextval,...)
Hope this helps, if not then post your data how is stored in table and post result which you want to get.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Well SQL didnt have a boolean value but I just chose a string and made it TRUE or FALSE (string values).
Anyways, I'm not seeing how that does what I need? I think its just wayy over my head.
So from the USer_Submit table it counts how many each ADMIN has. ADMIN are the people that work to solve the tickets.
So this way I can find out which ADMIN has the least amount of tickets (or least amount of work, however you want to put it).
BUT
In another table (Admin_Table) it list all of the ADMINs and if they are here or not (column Available, which is TRUE or FALSE, string values).
So I need which ADMIN is here and has the least amount of tickets.
|
|
|
|
|
SELECT COUNT(*),
UserName
FROM TicketTable
INNER JOIN UserTable ON UserTable.UserID = TicketTable.UserID
AND UserTable.OnLine <> 0
GROUP BY UserName
ORDER BY COUNT(*)
SQL Server has a BIT field = boolean (UserDable.OnLine <> 0)
The above code will give you the "online" user with the least number of tickets in the ticket table.
By playing with the filters on the usertable join you can manage the users.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I thought you'd eventually ask that.
The code I gave you to query with a JOIN can be converted to an UPDATE.
UPDATE TableX
SET ...
FROM TableX
INNER JOIN TableY
ON ...
A very powerful technique.
|
|
|
|
|
Thank you all for your answers. I will be sure to try it first thing in the morning. I recently just bought an iMac and having trouble installing Windows in Parallel mode. So I cant get to my project I made in Visual Studio. Not to mention Boot Camp doesnt support Windows XP (only Windows XP SP2+). So I cant install XP and update it to SP2 lol.
Just having computer problems 
Anyways will try it at work and let you know if I get it working! Thanks for all of your help!
|
|
|
|
|
Hey guys, I'm working on a website, I ve got a problem connecting to the database, I get the following message:
Unable to open the physical file "F:\Working on ASP.NET\MywebsiteProject\App_Data\EnglishWebSIteDB.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
An attempt to attach an auto-named database for file F:\Working on ASP.NET\MywebsiteProject\App_Data\EnglishWebSIteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I don't understand, I ve only got one database by that name and its used by SQL server !!!
|
|
|
|
|
Read some of this google results[^].
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Thanx for the respond man !
to tell u the truth i ve been through dozens of articles about the same problem and I ve made many changes but still no result !!!
what's puzzling me is that my connection worked before, i could connect to database and modify it through my web form ! but that error show up out of the blue !!!
i read that it might be because the user I'm currently logged in with doesn't have permission but the thing is the first time i did this (and it worked successfully) was the same as now !!! I also did many changes to my connection string but no luck !!!
Data Source=MR-K\SQLEXPRESS; Initial Catalog=EnglishWebSIteDB; User Instance=True; Integrated Security=SSPI
I'm just exhausted ! I dunno what to do !!!
Mr.K
|
|
|
|
|
I have made the database on Sql server 2005 developer edition(install at my name LINKCOMPUTER)We are working in a group.when i send this database to my friend on other pc It is asking for admin right.Can any one tell me how could I give right to other friend too so she can also work on same database.  ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
Samrah Rahman;
NED university;
Karachi.
|
|
|
|
|
if you mean that she want to use your database from your computer:
1-you must enable remote connection on your computer on this address:
start
all programs
microsoft sql server 2005
configuration tools
sql server surface area configuration
click this: surface area configuration for service and connections
click on your database engine server name and then database engine and then select remote connection:
enable local and remote connections
2- you must disable your computer firewall
3- you must start sql server browser service on this address:
start
all programs
microsoft sql server 2005
configuration tools
sql server configuration manager
click on sql server 2005 services on left panel and then start sql server browser service
now she can open management studio and then in server name type your computer name and then "\" and then your sql server instance name and then every things is right and she can see your database.
Human knowledge belongs to the world
|
|
|
|
|
Hello,
I have made pivot table with the following
TRANSFORM sum(cCapacity) AS CountOfITEM_PFX
SELECT jobworker, sum(cCapacity) AS Total
FROM cCapacity
GROUP BY JObWorker
PIVOT contract;
but how do I transfer the result set into a table.
Regards
Pritha
|
|
|
|
|
And the query works? Oh are you using Access! Sorry I assumed you were using SQL Server.
The question as such does not make sense, it defeats the purpose of a database if you are going to create a pivot query and then put it into a table, what happens when you get another jobworker, you need another column. It is for this reason that PIVOT queries were invented.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello,
Thanks for your reply.
But I wanted to have it temporarily in a table and use the table to view in my VC++ project where views are created to view the tables.
But now should assume that a pivot table cannot be transferred to a table.
Regards,
Priya
|
|
|
|
|
Ok, your in a database forum talking about putting pivot data into a table. Now it turns out you have a VC++ project you want to get that data for "viewing". I suggest you take the question to the Visual C++ forum where I would presume they will be able to help as I know nothing about VC++.
In VB/C# it is a standard thing to create a connection to get the data (in a datatable) and use the UI to display it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello,
Thanks for your replies
But in the database forum I am asking about making pivot tables from a table where I am getting the view of the pivot table and not the 'physical' table.
I don't to make a pivot table in VC++ .I will just display a table (which is pivot table in access or SQL server ) in VC++.
I will be using the connection to the database and display it in VC++.But the table(pivot) will have to be made in SQL server or access.
Thaanks & Regards,
Pritha
|
|
|
|
|
I'm having trouble understanding your requirement. These 2 statement seem to contradict each other.
OP
prithaa wrote: I have made pivot table with the following
prithaa wrote: I am asking about making pivot tables
and
prithaa wrote: (which is pivot table in access or SQL server )
this is the MOST relevant thing as pivot is totally different in the 2 databases. I beleive the transorm is an Access specific thing. Either way the pivot is created by a properly constructed select statement which you get via you DAL. Search the messages for Pivot as there have been numerious discussions on it. I beleive there is even an article on Pivot tables in SQL Server.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello,
Thanks for your reply.
(I have made pivot table with the following)
TRANSFORM sum(cCapacity) AS SumOFCapacity
SELECT jobworker, sum(cCapacity) AS Total
FROM cCapacity
GROUP BY JObWorker
PIVOT contract;
This means that I don't get any table but I get the view of the pivot table but the view of the above query is of no use to me if I cannot transfer the view to a table and use the table later.
I want a table (pivot table) not a view (pivot view)
Regards,
PRitha
|
|
|
|
|
Lets keep it simple:
What database are you using?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello,
thanks
The query that I wrote worked with MS Access but I would like to work on SQL server.I wrote it in SQL server but it did not work and I got the error (undeclared variable pivot).I think there is a version problem with SQL server.
Thanks & regards
Pritha
|
|
|
|
|
prithaa wrote: I think there is a version problem with SQL server
Nope the pivot structure is TOTALLY different in SQL Server. Do the following.
1. Get SQL Server
2. Research and create you pivot select statement in SQL Server. There are a number of posts and articles on CP to help with this
3. create a stored proc to execute the pivot select.
4. connect to sql via your DAL and execute the stored proc
5. use the result set returned to bind to a grid for display (C#/VB)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello,
Thanks for you replies
I will search for the select in SQL server
but one last question.
If the query works in Access and gives the Pivot view then can I make pivot table in a seperate table.
Pritha
|
|
|
|
|
I beleive you can use Select ..... Into NewTableName
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have try to use ado AppendChunk() fucntion in vc++6 to save to a clob data type to oracle database, but when i read back, the data was not save to the oracle database. Anyone know what is the problem?
// BEFORE THIS FUNTION, ORACLE DATABASE WAS CONNECTED
bool ADODBHandler::UpdateMap()
{
AdoNS::_RecordsetPtr rst;
try
{
..........................
rst.CreateInstance("ADODB.Recordset");
rst->CursorType = AdoNS::adOpenKeyset;
rst->LockType = AdoNS::adLockOptimistic;
HRESULT hr = rst->Open("pcmap.WAFERMAP3_PROCESSMAP", _variant_t((IDispatch*)m_cpConnection,true), AdoNS::adOpenKeyset, AdoNS::adLockOptimistic, AdoNS::adCmdTable);
rst->AddNew();
rst->Fields->GetItem("LOTNO")->PutValue(lotno);
rst->Fields->GetItem("WAFERNO")->PutValue(WaferNo);
rst->Fields->GetItem("WAFERID")->PutValue(Waferid);
rst->Fields->GetItem("PROBER_FILE")->PutValue(ProberFile);
rst->Fields->GetItem("ORIENT")->PutValue(Orient);
rst->Fields->GetItem("ROW_COUNT")->PutValue(RowCount);
rst->Fields->GetItem("COL_COUNT")->PutValue(ColCount);
rst->Fields->GetItem("PASS")->PutValue(Pass);
rst->Fields->GetItem("FAIL")->PutValue(Fail);
rst->Fields->GetItem("SOURCE")->PutValue(Source);
rst->Fields->GetItem("ET_TIME")->PutValue(date);
rst->Fields->GetItem("MAP_VER")->PutValue(Ver);
rst->Update();
_variant_t varChunk;
varChunk.bstrVal = _bstr_t(cWafer); // cWafer is a char array about 2500 bytes long
// for some strange reasons, cannot save varChunk to database and no exception error was capture
rst->Fields->GetItem("MAP")->AppendChunk(varChunk);
rst->Update();
}
.........
}
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
