|
This is quite complex, but I'm gonna give it a try 
You can probably do this in two ways (without using a temporary table, that is).
1. If you're fine with the result being shown vertically:
SELECT CLASS, DATENAME([month], RSRVDATE) AS Month, COUNT(*) AS Total<br />
FROM RESERVED_LEGS <br />
INNER JOIN Tickets_Purchased ON RESERVED_LEGS.PNR = Tickets_Purchased.TKTNO<br />
GROUP BY CLASS, MONTH(RSRVDATE)<br />
ORDER BY CLASS, MONTH(RSRVDATE)
This should return something like:
CLASS Month Total
First January 100
First February 150
...
2. If you want the result to be shown horizontally (this is more difficult):
SELECT CLASS, <br />
(SELECT COUNT(*) FROM RESERVED_LEGS INNER JOIN Tickets_Purchased AS TP ON RESERVED_LEGS.PNR = TP.TKTNO WHERE MONTH(RSRVDATE) = 1) AND TP.CLASS = TicketsPurchased.CLASS) AS Jan, <br />
(SELECT COUNT(*) FROM RESERVED_LEGS INNER JOIN Tickets_Purchased AS TP ON RESERVED_LEGS.PNR = TP.TKTNO WHERE MONTH(RSRVDATE) = 2) AND TP.CLASS = TicketsPurchased.CLASS) AS Feb,<br />
(SELECT COUNT(*) FROM RESERVED_LEGS INNER JOIN Tickets_Purchased AS TP ON RESERVED_LEGS.PNR = TP.TKTNO WHERE MONTH(RSRVDATE) = 3) AND TP.CLASS = TicketsPurchased.CLASS) AS Mar,<br />
...<br />
...<br />
...<br />
FROM Tickets_Purchased<br />
GROUP BY CLASS<br />
ORDER BY CLASS
You can always use the query from #1 and format it to present it the way you want it to be, as I think it will be the most efficient query from both.
I hope it helps 
PS: You might want to tweak the SQL queries a bit to make it work
PPS: Maybe there is a more efficient way to do #2. Anyone?
Edbert P.
Sydney, Australia.
|
|
|
|
|
i am very new to SQL, and my first project has been a pretty big undertaking... i am grabbing a set of textfiles from a webserver, parsing them, and doing a number of calculations, etc on them, and storing the result in SQL, this is the main load on SQL... other loads are nearly irrelevant and are always simple selects with few joins.
i am having the problem that the CPU is getting toasted, and i know there is plenty of room for optimization in my (lengthy) procedure, which is being supplied to sql pretty much with no overhead (the parsing program uses very little cpu power and during an update always has a queue ready)
so if you see anything at a glance that can speed it up please help!
(i have guessed that having no extra indexes for this database is probably for the best?)
Method Caller (C#) from web downloader
Method (C#) i have a class that manages all the sql operations for the program
SQL Stored Procedure (SQL) huge thing that is CPU bound...
any hints are more than appreciated... this is for a project i intend to be release as code for anyone to use... but first i need to get it running at a decent speed...
if i did all the processing in .net and dumped it into a txt, and BCP'd it in, would it be faster?
i would still need many of the database operations there.
-Luke
|
|
|
|
|
One thing you may want to try is using a dataset and dataadapter. Read your files and fill in the dataset, then call the DataAdapter.Update rather doing the ExecuteNonQuery for every line read.
|
|
|
|
|
any thoughts on the size of the data set i should use?
a typical update would probably be around 15-40k new/modified entries in [user_history]
perhaps i will try using a 1/4 of the system memory to start...
thanks, wether or not its will be an improvement will be interesting, as i still need a lot of if-exists... i guess it depends what factor is causing the cpu to remain at high load...
i also have the option of switching the server... from a single Athlon XP 2500+ to a dual 900Mhz Xeon... i think staying with this machine is for the best...
|
|
|
|
|
I have a table as given below
1 5 57 &Prosjekter
3 5 57 &Planning
3 5 122 &Proposal
3 5 232 &Preventive
3 5 233 &Paste Ctrl+V
I want this in row wise
select a,b,c,d,e,f where queryname
and column a=&Prosjekter
and column b=&Planning etc
I need a query how i can get this done.
Thank you
with regards
vimal
Help in need is the help indeed
|
|
|
|
|
SELECT a,b,c,d,e,f FROM tablenamw WHERE a='&Prosjekter' AND b='Planning'
Mazy
You're face to face,
With the man who sold the world - David Bowie
|
|
|
|
|
To maintain data integrity in an n-tier application I'm storing the layout of an SQL table within XML dataset schemas.
I have managed to get the two to compare with one another, to check that the specified database is valid for the application. However, I don't know my way around ADO.net enough to create a new table based on the XDS, which is what I'd like to do next.
Any help would be greatly appreciated.
|
|
|
|
|
Hi!
I m working on an IS system being developed in asp.net using C#.
Now my problem is that i have 2 tables namely
USER1 [having fields like UserID, FName, LName, .....] and EMPLOYEE[having fields like Employee_ID, Job_Title1, Industry_Sector1, Industry_Sector2.....]
Employee_ID is the Foriegn Key for UserID in User1 Table.
now i take the join as:
string query="Select FName, LaName, Job_Title1, Industry_Sector1 from User1 u, Employee e where u.UserID=e. Employee_ID and e.Industry_Sector1='"+Human Resourses+"'";
then after establishing the connection i have my code as:
SqlCommand mycommand =new SqlCommand(query, MyConnection);
datagridEmployee.DataSource=Command.ExecuteReader();
datagridEmployee.DataBind();
....
now i get the following error.
A Field or prperty with the name "Employee_ID" was not found on the selected source.
Does any on have the solution,. I mean can a datahrid support more than onetables data at a time? or whts the problem??
waiting 4 reply
|
|
|
|
|
You didn't add EMPLOYEE_ID in your query then you use it somewhere i your code, so tthis error is obvious, so add this column to your query too.
Mazy
You're face to face,
With the man who sold the world - David Bowie
|
|
|
|
|
Hi Folks !
i want to share my problem with u hope u will solve it....
i have a datacombo attached to ADO (Database Table).
1. I have coded the function for the combo on 'change' as follows to run the query on the ADO.
[code]
Private Sub cmbOdr_Change()
ADO_Ven.Recordset.Close
ADO_Ven.Recordset.Open "SELECT * FROM VENDOR WHERE VENID IN (SELECT VENID FROM PROD WHERE PRODID IN (SELECT PRODID FROM BYR_ODR_DTL WHERE ORDERNO ='" & cmbOdr.BoundText & _
"' AND [DATE] IS NULL))"
ADO_Ven.Recordset.Requery
CmbVen.Text = ""
End Sub
[/code]
i receive an error Msg...
Run-time error '91':
Object variable or With block variable not set
but when i code it in the click event of the combo "cmbOdr"
the code works fine......
2. Similar kind of Problem is with another piece of code ,
which i code on the button click event
[code]
Private Sub Command3_Click()
ADO_ByrOdrDtl.Recordset.Close
ADO_ByrOdrDtl.Recordset.Open "INSERT INTO BYR_ODR_DTL( [DATE] ) VALUES ( txtDate.Text )WHERE ORDERNO ='" & cmbOdr.BoundText & "'"
End Sub
[/code]
the abvoe mentioned Error Msg is Generated......
Plz tell me how to avoid this error...
Thanx in Advance.
|
|
|
|
|
The code looks suspiciously like VB6
I would guess it is caused by the Recordset.Close line.
The first time you run the code, it will throw an error as the Recordset object has not been created and you try to call the Close function.
That's what the error "Object variable or With block variable not set" means.
The second time you run it, the code will work as the Recordset object has been created.
Try to check whether the Recordset object exists before calling the Close function. Better yet, always call Close RIGHT AFTER you open a recordset and use the data.
Edbert P.
Sydney, Australia.
|
|
|
|
|
Any idea how you can retrieve a list of tables within a database using ADO.net?
|
|
|
|
|
If your database is SQLServer look for INFOMATION_SCHEMA in SQLServer book , and if you use Access seach this forum, this was answered so many times these days.
Mazy
You're face to face,
With the man who sold the world - David Bowie
|
|
|
|
|
I posted this about 5 posts ago.
http://www.codeproject.com/script/comments/forums.asp?msg=739476&forumid=1725#xx739476xx
Type = 'U' are tables and Type = 'V' are Views
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
 can anyone please tell me how I can have a form in MS access 2003 with Slave master relationship. I mean just a form where I can input and edit data in two different related tables at the same time.
does it make sense?
I hope it makes sense to you.
Thank brother or sister!
|
|
|
|
|
It's been a while, like 6 years if you are refering to a subform. If so, check out the help or have the wizard create one for you.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Is there a way instead of using SELECT TOP 10
to say something like:
SELECT MID 11 10 or something, so it starts at the 11th record and takes 10 records after it.
This way I can easily put my data into pages, straight from the database, with 10 records on each page. I would also need to be able to first order them and then take 10 records starting from some nth record.
Any suggestions, returning 2000 records when i only display 10 seems very wastefull.
Thanks.
http://www.codeproject.com/aspnet/custompaging.asp
I found this link here, but this seems very inefficient as well. It creates a temp table and still selects all records and inserts them into the temp table. Regarding this way... does the # in front of the table name mean it gets dropped when the stored procedure is done running? The Author never drops it, and so if multiple people called this function at the same time, what happens to the temp table? Never seen the pound sign # in front of a table before.
Thanks some more.
|
|
|
|
|
How about:
SELECT TOP 10 field1, field2 ORDER BY field1 WHERE field2 IS BETWEEN 'VALUE1' AND 'VALUE2'
...hey that could even be a stored procedure, where you pass VALUE1 and VALUE2 as params...
|
|
|
|
|
but i have no idea what value1 and value2 would be. Maybe I have an auto-increment PK but that won't work because if some rows get deleted, then you dont have a range of 10 records knowing that a multiple of 10 records was skipped.
This wont work.
I modified my question, see the bottom of the original question, i added an example i found.
Thanks anyways.
|
|
|
|
|
Doing a SELECT TOP N ... without a WHERE and ORDER BY clause is kind of trivial, and you shouldn't rely on an id or auto-increment field in an ORDER BY, as it is no guarantee of the chonological order in which records are added to a table. You should work with other fields, such as datetime fields, foreign key fields, etc.
Another question: Are you referring to paging through a DataSet using ASP.NET? If so, there are plenty of examples and articles out there on data paging and controls like the DataGrid control.
|
|
|
|
|
when i initialize a connection string in my ASP page to connect to SQL Server 2000 Database as follows:
ConnStr = "Provider=SQLOLEDB; Data Source=DB1; Initial Catalog=MYDB; User ID=sa; Password=0000;"
===========================================================
Then I use the open method to open the connection for the Database as follows:
connStr.open 'This is Line 20
===========================================================
An error is shown as follows:
Error Type:
(0x80040E4D) Line 20
===========================================================
What is the error in this case?
Coz no error is specified. I'm waiting for your help.
Sam2004
|
|
|
|
|
|
This error code is 'authentication failed'. Basically, SQL Server wouldn't let you log in.
Either your password is incorrect, or your server is configured for Windows Authentication only. If you can connect using Windows authentication, specify 'Integrated Security=SSPI' and omit the Username and Password. However, ASP will connect to the database using whichever account the script engine is running under - typically IUSR_machinename. If you don't want to deal with this, you need to configure the database server for SQL Server and Windows authentication. Right-click the server in Enterprise Manager, choose Properties and the Security tab to find this option.
It's a bad idea to log in as sa unless you're really performing system administration. Add a new login to your database (using Enterprise Manager, under Security > Logins) and give it a password. You'll then need to set the permissions for the operations that the web site needs to perform. Typically I make the new login part of the database's public role and assign that role the appropriate permissions.
|
|
|
|
|
I don't know if I should put this under php or sql, but it is a little of both. I have secure info that I do not want published to a file on the server at any time even a few seconds. I just want to take it strait out of the data base and have the person click a link, to this php file below and when they go to it it prompts them to download. I want the sql info in the file. I think I have most of the code right, but it isn't quite working. As of now, it is downloading a blank file.
<br />
<?php<br />
<br />
function show_file_information($filename)<br />
{<br />
@readfile($filename);<br />
}<br />
<br />
if(!class_exists(MySQL))<br />
{<br />
require '../home/MySQL.php';<br />
require '../home/conf_global.php';<br />
$mysql = new MySQL();<br />
$mysql->connect($INFO['sql_user'],$INFO['sql_pass'],$INFO['sql_database'],$INFO['sql_host']);<br />
}<br />
<br />
$news_result = $mysql->query("SELECT * FROM newsletters WHERE id='1'");<br />
$news = mysql_fetch_assoc($news_result);<br />
<br />
$filename = $news['add_id'];<br />
<br />
header("Pragma: public");<br />
header("Expires: 0");<br />
header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); <br />
header("Content-Type: application/force-download");<br />
header("Content-Type: application/octet-stream");<br />
header("Content-Type: application/download");<br />
header("Content-Disposition: attachment; filename=".basename(emaillist).".txt");<br />
header("Content-Transfer-Encoding: binary");<br />
header("Content-Length: ".strlen($filename));<br />
<br />
<br />
?>
|
|
|
|
|
Hi there I'm playing around on SQL server 2000 and I get a error when i try to run this query:
SELECT *, COUNT(P.PTICKETNO) AS counter from TICKETS_PURCHASED T,PASSENGER P WHERE P.PTICKETNO = T.TKTNO AND T.TKTNO = 'TKT000000000001'
The error is as such.
Column 'T.TKTNO' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
What could be the problem guys . My SQL is very weak and I' just getting my feet wet, I would appreciate all the help
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
