SQL Query help needed please
-
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.
-
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?
The need to optimize rises from a bad design.My articles[^]