|
Does my request not make any sense???
Is it not possible to do this with SQL???
Please let me know
Thanks,
Nick
|
|
|
|
|
Nick
How about something like:
select a.ClientID, a.ClientName, b.YTDGross, b.YTDNetCom,
b.YTDNetInt, b.CSCost, b.OtherCost,
(b.YTDNetCom + b.YTDNetInt) as YTDNetRev,
(b.CSCost + b.OtherCost) as TotalAlloCost,
(b.YTDNetRev - (b.CSCost + b.OtherCost)) as TotalNetRev,
sum(c.YTDNetRev - (c.CSCost + c.OtherCost)) as RunningTotal
from tblClients as a, tblData as b, tblData as c
where a.ClientName = b.ClientName
and a.ClientName >= c.ClientName
group by a.ClientName, a.ClientID, b.YTDGross, b.YTDNetCom,
b.YTDNetInt, b.CSCost, b.OtherCost
order by a.ClientName, a.ClientID
The running total is calculated by joining to the tblData table a second time. The join from table "a" to table "c" relies upon the fact that the query is sorted by ClientName.
I haven't tested this, so you may need to build the SQL statement up, testing each step of the way.
I hope that this is helpful.
Andy Harman
|
|
|
|
|
Hi Andy
Thanks a lot for the help, but I am having trouble running the query properly.
Here is what I have:
SELECT a.CLIENTID, a.CLIENT, b.YTDGROSS, b.YTDNETCOM, b.YTDNETINT, b.CSGCOST, b.OTHERCOST, (b.YTDNETCOM+b.YTDNETINT) AS YTDNETREV, (b.CSGCOST+b.OTHERCOST) AS TOTALALLOCOST, (YTDNETREV-(b.CSGCOST+b.OTHERCOST)) AS TOTALNETREV, Sum(YTDNETREV-(c.CSGCOST+c.OTHERCOST)) AS RUNNINGTOTAL
FROM tblCLIENTS AS a, tblDATA AS b, tblDATA AS c
WHERE (([a].[CLIENT]=[b].[CLIENT] And [a].[CLIENT]>=[c].[CLIENT]))
GROUP BY a.CLIENTID, a.CLIENT, b.YTDGROSS, b.YTDNETCOM, b.YTDNETINT, b.CSGCOST, b.OTHERCOST;
I had to change:
(b.YTDNETREV - (b.CSGCOST + b.OTHERCOST)) as TOTALNETREV,
sum(c.YTDNETREV - (c.CSGCOST + c.OTHERCOST)) as RUNNINGTOTAL
TO
(YTDNETREV-(b.CSGCOST+b.OTHERCOST)) AS TOTALNETREV, Sum(YTDNETREV-(c.CSGCOST+c.OTHERCOST)) AS RUNNINGTOTAL
Also the field ClientName is actually named CLIENT
Access wouldn't recognize either b.YTDNETREV or c.YTDNETREV...so I had to change them both to YTDNETREV.
Now the query will run, but it doesn't capture any data.
Do you have anymore suggestions for me?
Again, thanks for your help!
Nick
|
|
|
|
|
Hi,
Can anybody point me towards info. regarding connection pooling specifically with the Oracle OleDb provider for .NET? I couldn't find anything about it in MSDN (although there's tons WRT SQLServer).
Cheers.
Dr Herbie.
Jugger, lion-tamer, fashion guru and man-about-town.
|
|
|
|
|
I'm not sure you can with the generic OLE provider. I do know you can with the native Oracle Provider for .NEt. Works alot like the native SQL provider.
Mark Conger
Sonork:100.28396
|
|
|
|
|
Hi All,
I have a client application which I don't like others to see the DB structure (Table, Views, StoreProcedures). Therefore I want to set an sa password when installing my MSDE instance and nobody can change it after that.
But the user can login using Windows Authentication via EnterpriseManager and change the sa password. Is there a way to prevent this action?
regards,
Sassan Komeili Zadeh
|
|
|
|
|
Hey all,
I just found out that when you delete a row from a DataTable it doesn't decrement the Count property associated with it's Rows collection. For example:
int rowcnt;<br />
DataTable t = GetTableWithManyRows();<br />
rowcnt = t.Rows.Count;<br />
t.Rows[0].Delete();
rowcnt = t.Rows.Count;
What is the idea behind this behavior? How does one get the number of rows *ignoring* the ones that have been deleted?
Thanks much -
*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";
|
|
|
|
|
DataView is handy in problem as above where we need current records count.
set dataview RowStateFilter property to as shown below to view only rows which are not deleted from table. All rows which have deleted status in underlying table will not visible.
DataView t=new DataView(someTable);<br />
t.RowStateFilter=DataViewRowState.CurrentRows;<br />
<br />
debug.WriteLine("Rows Count before deletion= "+ t.Count);<br />
t[0].Delete();<br />
debug.WriteLine("Rows Count after deletion = "+ t.Count);
this might help...........................
|
|
|
|
|
Thanks mughal, perfect solution.
Why are deleted rows still a part of the DataTable? Is this used to roll things back? I guess that is the only rational I could see for this type of behavior...
*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";
|
|
|
|
|
Hi!
I will write an application that work with an Access DB.
My database have 5 table that have a relationship to each other.
tables in DB:
Table 1: Car: CarID, Manufactore, Colour, Hp, Displacement, Price, Model
Table 2: SaleContractCar: SaleContractID, CarID
Table 3: SaleContract: SaleContractID, CustomerID, Date
Table 4: Customer: CustomerID, CustomerAddressID, Tel, Age, SeconCar, Name
Table 5: CustomerAddress: CustomerAddressID, Street, StreetNr, Zip, City
relationship between the tables:
Table 1 (one to more) Table 2
Table 2 (more to one) Table 3
Table 3 (more to one) Table 4
Table 4 (more to one) Table 5
at this moment I don't know how to start.
should I first load all tables I one DataSet?
and create for each table a DataTable?
how I should start?
how I should write an update SQL statement for this DataSet?
where I can find some sample application that work with Relational Data?
thx!!
|
|
|
|
|
Hello,
The .NET Framework SDK 1.0 includes a collection of QuickStart Tutorials. These can be installed locally or viewed online.
The ADO.NET tutorials can be found at
http://samples.gotdotnet.com/QuickStart/howto/default.aspx?url=/quickstart/howto/doc/adoplus/adoplusoverview.aspx
Notes:
- In the tutorial, you can switch between C#, VB.NET, and JScript for the example code.
- To access MS Access, you should use the System.Data.OleDb data provider.
HTH,
Bernard
|
|
|
|
|
hi all
i open an access database from asp .. for some reason i need to know the number of records in the recordset before processing it as follows :
set con=Server.CreateObject("Adodb.Connection")<br />
set rs=Server.CreateObject("Adodb.Recordset")<br />
con.ConnectionString=strCon<br />
con.CursorLocation=1 'server<br />
con.Open<br />
set rs.ActiveConnection=con<br />
rs.CursorType=adOpenKeySet<br />
rs.open "select * from items"<br />
rs.MoveLast<br />
n=rs.RecordCount<br />
Response.Write n
i get an error :
Microsoft OLE DB Provider for ODBC Drivers (0x80040E24)
Rowset does not support fetching backward.
can you help with this ?
|
|
|
|
|
You could call SELECT COUNT(*) FROM items first and then your actual query.
--
karl
|
|
|
|
|
I've already decided to use this method.. I know it but I didn't want to make 2 queries while I can use a simple property call
thank you anyway
any other ideas ?
|
|
|
|
|
You might read up on the following:
o CursorType = adStatic (3)
o CursorLocation = adUseClient (3)
Also, when I was doing ASP, I used to use the GetRows() method to store the recordset into a multi-dimensional array. That allows you to close the recordset and connection straight away - and you can wander backwards and forwards through the array data.
Andy
|
|
|
|
|
Hi..
I changed the driver in the connection string from
Microsoft access
to:
Jet 4.0
and it worked !!
|
|
|
|
|
I have been looking into randomizing a result set from a SQL Server database. I have looked into using the RAND() function but there are problems with this approach because RAND() seeds every time. So a query like this, then:
SELECT ID, rand()
FROM FOO
ORDER BY 2
This outputs something similar to:
ID
-- ----
1 .754
2 .754
3 .754
Since RAND() seeds of the time and the time did not change we get the same result through the query.
There is one solution:
SELECT * FROM TABLE ORDER BY NewId()
Another thought is to use an external dll to use c++ rand()
There are other methods that generate tables of random numbers and use cursors.
My question is what method do you use?
Thanks in Advance,
John
|
|
|
|
|
Here's a sample from SQL Books Online (why they call it online, one will never know):
DECLARE @counter smallint
SET @counter = 1
WHILE @counter < 5
BEGIN
SELECT RAND(@counter) Random_Number
SET NOCOUNT ON
SET @counter = @counter + 1
SET NOCOUNT OFF
END
GO An alternative is to just get a resultset and pulling rows from that randomly from your application.
I rated this article 2 by mistake. It deserves more. I wanted to get to the second page... - vjedlicka 3:33 25 Nov '02
|
|
|
|
|
I have the sql books online and saw that example but I am unsure how to get that to work in my application.
leppie wrote:
An alternative is to just get a resultset and pulling rows from that randomly from your application.
I know that will work but in my application most of the time I need the data read in the normal order. Only under special cases will I need the data to be randomized. I thought it will be a lot simpler and require less code modifications if I could get SQL server to randomize the list under these conditions. The data is a list of cases (mammograms) that doctors will read. I call a stored procedure to produce this result set. In the normal mode you want them to read first in first out, but when you are doing a study you want to randomize the data so each doctor sees the cases in a different order. This is because there may be a pattern in the data that infulences the doctors answers for future cases. With the data randomized we don't have to worry about things like this.
John
|
|
|
|
|
John
I had a similar problem a couple of years back. Under certain conditions the client wanted to have rows sorted in a random order.
I wrote the following JScript ASP code:
function array_randomise() {
var li_rnd ;
var lo_temp ;
for (var li_loop = 0 ; li_loop < this.length ; li_loop++) {
li_rnd = Math.round(Math.random() * li_array_len) ;
lo_temp = this[li_rnd] ;
this[li_rnd] = this[li_loop] ;
this[li_loop] = lo_temp ;
}
}
It walks through the list of records, and randomly swaps the current record with some other record (at a random position in the list).
Hope this helps.
Andy Harman
|
|
|
|
|
Now I remember what the problem is with that approach (WHILE loop) the output looks like this:
Random_Number
-------------
0.713591993212924
Random_Number
-------------
0.713610626184182
Random_Number
-------------
0.71362925915544
Random_Number
-------------
0.713647892126698
I guess some of the digits are random but it is not clear how to use the data to randomize a result set.
|
|
|
|
|
John M. Drescher wrote:
I guess some of the digits are random but it is not clear how to use the data to randomize a result set.
I dont really know SQL well enough to do this, like I said, I would just do it on application level. Hope someone can help you.
I rated this article 2 by mistake. It deserves more. I wanted to get to the second page... - vjedlicka 3:33 25 Nov '02
|
|
|
|
|
Thanks. I thought there had to be an easy way to do it in SQL Server.
John
|
|
|
|
|
I may be WAY off base here, it's been a while since I've used SQL Server, but as no one else seems to know, I'll take a guess.
Isn't the problem here that you're saying "ORDER BY 2"?
Are you sure that this sorts on Column 2 or does it just sort with each record being 2?
Wouldn't "ORDER BY rand()" work better?
Paul
We all will feed the worms and trees So don't be shy - Queens of the Stone Age, Mosquito Song
|
|
|
|
|
use this:-
select ID, NewID() as [guid]
from FOO
order by [guid]
#include <beer.h>
|
|
|
|