Hello, i need a little help with a couple of sql queriesn that refer to the pervious rows. I have a table that imports dynamic tables, and i need to query this data. The tables have been normalised and ive made 4 queries. I now need help with a couple more.
The database ive made is used for messuring rainfall. The data imported has Data, Time, and the Name of a node (node is the location of the place where the data is taken from). The data in each of the node columns is the amount of 'spills'. A spill is just what we call the amount of rainfall in each cell in a node.
I need to make the following queries on that data.
-----------------------------------
1. Number of hours spill
If the timestep is an hour then the total number hours spill is the same as the timestep, but if the short or longer it needs to be factored.
-------------------------------------
2. Number of discrete spills
A discrete spill is when the spill stops and starts again later in the column.
So i need to somehow count the number of times where it stops and starts again.
--------------------------------------
3. Peak spill rate in litres per second.
Per node
--------------------------------------
Here is a list of my current queries.
1. Total Number Of Timesteps >0.001
SELECT TestName, Count(*) AS Number_Of_Spill_Timesteps_Over_01
FROM tblImportTableTest
WHERE Spill>0.001
GROUP BY TestName;
2. Total Number Of Timesteps
SELECT TOP 1 Count(*) AS NoOfTimeSteps
FROM tblImportTableTest
GROUP BY [TestName];
3. Spill Timesteps As A % Of Total Timesteps
SELECT TestName, SUM(Iif([Spill]>0, 1, 0))/COUNT(*)*100 AS Percentage_Spills
FROM tblImportTableTest
GROUP BY TestName;
4. Total Spill Volume (only of timesteps where spill rate is >0.001)
SELECT [TestName], SUM(IIf([Spill]>0.001,[Spill],0))/1000 AS TotalSpillVolume
FROM tblImportTableTest
GROUP BY [TestName];
..........................................................................
These all appear to work. Can anyone offer any help on there other 3?
Thanks,
Marley.
The database ive made is used for messuring rainfall. The data imported has Data, Time, and the Name of a node (node is the location of the place where the data is taken from). The data in each of the node columns is the amount of 'spills'. A spill is just what we call the amount of rainfall in each cell in a node.
I need to make the following queries on that data.
-----------------------------------
1. Number of hours spill
If the timestep is an hour then the total number hours spill is the same as the timestep, but if the short or longer it needs to be factored.
-------------------------------------
2. Number of discrete spills
A discrete spill is when the spill stops and starts again later in the column.
So i need to somehow count the number of times where it stops and starts again.
--------------------------------------
3. Peak spill rate in litres per second.
Per node
--------------------------------------
Here is a list of my current queries.
1. Total Number Of Timesteps >0.001
SELECT TestName, Count(*) AS Number_Of_Spill_Timesteps_Over_01
FROM tblImportTableTest
WHERE Spill>0.001
GROUP BY TestName;
2. Total Number Of Timesteps
SELECT TOP 1 Count(*) AS NoOfTimeSteps
FROM tblImportTableTest
GROUP BY [TestName];
3. Spill Timesteps As A % Of Total Timesteps
SELECT TestName, SUM(Iif([Spill]>0, 1, 0))/COUNT(*)*100 AS Percentage_Spills
FROM tblImportTableTest
GROUP BY TestName;
4. Total Spill Volume (only of timesteps where spill rate is >0.001)
SELECT [TestName], SUM(IIf([Spill]>0.001,[Spill],0))/1000 AS TotalSpillVolume
FROM tblImportTableTest
GROUP BY [TestName];
..........................................................................
These all appear to work. Can anyone offer any help on there other 3?
Thanks,
Marley.