|
|
Hi,
The problem is as follows:
I need a stored proc that can provide results on the maximum channel usage for any given period (either 24 hours, or n days period).
This is calculated by finding rows that overlap (by Datestamp + Duration) as follows:
ROW DATESTAMP [START] DATESTAMP + DURATION [END] CHANNEL
A 2009-01-02 12:11:27 2009-01-02 12:11:27 + 75 = 2009-01-02 12:12:42 9
B 2009-01-02 12:11:52 2009-01-02 12:11:52 + 20 = 2009-01-02 12:12:12 10
C 2009-01-02 12:11:55 2009-01-02 12:11:55 + 23 = 2009-01-02 12:12:18 6
Because rows B & C are active while row A is still active and the channel ids are unique, then 3 channels are simultaneously active.
If the duration of row C was 50 secs (for example) then only 2 channels would be active together (ie. 9 & 10).
The test data should produce the following results from the query
(For 24 Hours starting 2009-01-02 00:00:00)
PERIOD MAX CHANNELS ACTIVE
00:00 - 01:00 0
...
09:00 - 10:00 2
10:00 - 11:00 2
11:00 - 12:00 2
12:00 - 13:00 3
...
(For @numdays starting 2009-01-02)
DATE MAX CHANNELS ACTIVE
2009-01-02 3
2009-01-03 3
2009-01-04 4
...
Any ideas would be really really appreciated.
Thanks.
|
|
|
|
|
One question about the data.
munklefish wrote: This is calculated by finding rows that overlap (by Datestamp + Duration) as follows:
ROW DATESTAMP [START] DATESTAMP + DURATION [END] CHANNEL
A 2009-01-02 12:11:27 2009-01-02 12:11:27 + 75 = 2009-01-02 12:12:42 9
B 2009-01-02 12:11:52 2009-01-02 12:11:52 + 20 = 2009-01-02 12:12:12 10
C 2009-01-02 12:11:55 2009-01-02 12:11:55 + 23 = 2009-01-02 12:12:18 6
Because rows B & C are active while row A is still active and the channel ids are unique, then 3 channels are simultaneously active.
If the duration of row C was 50 secs (for example) then only 2 channels would be active together (ie. 9 & 10).
If row C has duration 50 secs which is 27 seconds more than in the example data, doesn't it still overlap between rows A and B. So why is it interpreted that it's not simultaneously active?
|
|
|
|
|
hi,
how to delete rows of two database tables with single delete query?
regards,
bill
|
|
|
|
|
"Not"
Delete works on a single table. You can wrap them in a transaction if you want them to 'act' is if they were a single statement.
I are troll 
|
|
|
|
|
Not true. It is possible to delete from a join of two or more tables, or implement cascade deletes via a constraint on a foreign key relation, or use a trigger to do more complex rule based deletes from additional tables based on the scope of the first delete.
|
|
|
|
|
|
If the tables (and the desired rows) are related by some common key (a primary key/foreign key relationship would be ideal) the you could delete the rows as a join on the two tables) :
delete from TableA inner join TableB on TableA.fielda1 = TableB.fieldB3
Alternatively, if it is SQL server, you could create a trigger on TableA that deleted rows from tableB basded on what was in the Deleted psuedo table at the time of the deletion from TableA.
|
|
|
|
|
Rob Graham wrote: you could delete the rows as a join on the two tables
If you're referring to the T-SQL extension on DELETE statement, it won't delete rows on both tables. It's just another way to express where condition.
|
|
|
|
|
Hey everybody
Does anyone have any idea how can I perform OutputDebugString (C#'s Trace) in SQL?
Is it event possible?
Thanks!
|
|
|
|
|
It depends, where do you want the output to go?
If you simply want the output to the client calling SQL Server stored procedure, you could use PRINT[^]
|
|
|
|
|
Hey,
Thanks for the quick reply!
I don't want to output to the calling app.
I want to print in DebugView (printing to the "output debug").
|
|
|
|
|
Could you explain a little bit more:
- are you using Visual Studio or SQL Server Management Studio?
- then you call stored procedure?
- while the stored procedure executes you want to print something in the Output window in Visual Studio or Messages Window in Management Studio
- if using Visual Studio, are you running a program and debugging it or simply using Server Explorer while in design mode
|
|
|
|
|
I'm using SQL Server Management to write stored procedures.
Than, I'm running application that calls these stored procedures (I'm not running the application with debugger).
Just like you wrote, I want to print something to the output window - print to DebugView (what C++'s OutputDebugString() or C#'s Trace.Writeline() are doing).
|
|
|
|
|
Okay, if you use PRINT in the stored procedure and execute the procedure in Management Studio, the output will come to the message window in Management Studio.
When you're executing an application which then calls a stored procedure, AFAIK there's no way to print any messages to a Management Studio window.
However, if you're using SqlConnection in the application, you can wire InfoMessage[^] event. Whatever you print in the procedure will come to this event and you can redirect it to the place you want.
Was this what you we're after?
If you want the output to the server side where SQL Server is running, you can:
- write the message to a table
- write it to the sqlserver log file
- write it to some other file
- send it using mail
- create a notification
modified on Saturday, January 31, 2009 5:36 PM
|
|
|
|
|
Yeah,
Thanks a lot!
|
|
|
|
|
|
Mika
Does the event fire on the print command in the proc or when the procedure comepletes?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
My first guess would be that when the execution completes, but I'll run few tests this evening since I'm not sure about asynchronous execution.
Mika
|
|
|
|
|
I have a few beast size procs that I may wire up for testing!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
If i use ado and msacce mdb in c:\ all work fat but if i use the same code to read and write on md in \\myserver\mydir... all opertion are very very slow!!!!!
PLease "10 gold rules" to speed up operation on network !
Tks.
modified on Saturday, January 31, 2009 1:51 PM
|
|
|
|
|
There's not much on your post to work with but few things you should check:
Is the connect slow. If the connect is slow, you should try to find a way to speed up finding the server and the share. Since you use names for server, perhaps DNS is responding slowly etc. Talk with the people responsible for your network. Also you can try to use IP-address instead of server name.
Slow selects, how much data do you transfer. If you need for example just one row in your application, do you fetch all rows from a table in database and then filter the data in the application. Always use the filtering abilities of the database. Also do not select columns you don't need.
Slow modifications, try to make all modifications at once. For example avoid situations where you first insert a row and then later update it. If you have such operations, prepare the data as much as you can before transfering it to the database.
Modeling, perhaps some operations are slow because of the data modeling. Review your relational model critically and for example eliminate duplicate data, normalize etc.
And of course check that the network is working correctly. For example copy a big file to that directory and see if it's copied in reasonable time.
|
|
|
|
|
There is little you can do other than use a different DBMS. Microsoft Access is a "file share" based database, as opposed to a server dbms, so all the data delivery is accomplished using the underlying file sharing. As a result, MSAccess is a real dog in any shared multi-user scenarios. (or even a single user network share scenario). Try converting the database to use Sql Server Express, MySql, Firebird, or any other server based dbms.
|
|
|
|
|
Hi All...
Im a new to Report Builder - SQL reporting services 2005. I need to use a Query in Report builder to generate the reports.
Currently,im using a Single table to generate a report...
Is there any way to use more than one tables in Report Builder...
If its possible to use Query,Where do i ve to write it???
Any pls suggest some solution...
Thanks in Advance...
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
