Returning records in Increments SQL

Returning records in Increments SQL

Post by Nathan » Sun, 15 Feb 2004 02:43:15


Hell,
I am using the following query to return results organized into 15 min increments of hours. I have it so the results are identified by HourRequested and TimeInterval (0-14, 15-29, etc). Now I need to figure out how to structure the query so each HourRequested will return an overall AVG for each 15 min increment. For example, HourRequested 5 will return 4 records (0-14, 15-29, 30-44, 45-59) each having an avgETP, avgTTP. Any ideas??

SELECT
AVG(L.ElapsedTimeToPrint) AS avgETP,
AVG(M.TimeBeforeDepletion*60) AS avgTBD,
COUNT(M.KanbanNumber) AS Count_Num,
CONVERT(varchar, L.TimeRequested, 101) AS RequestedDate,
DATEPART(hh, L.TimeRequested) AS RequestedHour,
CASE
WHEN DATEPART(mi, L.TimeRequested)/15 = 0
THEN '0-14 minutes'
WHEN DATEPART(mi, L.TimeRequested)/15 = 1
THEN '15-29 minutes'
WHEN DATEPART(mi, L.TimeRequested)/15 = 2
THEN '30-44 minutes'
WHEN DATEPART(mi, L.TimeRequested)/15 = 3
THEN '45-59 minutes'
END AS [15MinuteInterval]
FROM Log L WITH (NOLOCK)
JOIN Master M WITH (NOLOCK) ON L.KanbanNumber = M.KanbanNumber AND L.Zone = M.Zone
WHERE L.TimeRequested BETWEEN '2004-02-09 05:00' AND '2004-02-09 16:00'
AND M.Zone = 'B'
GROUP BY
L.Zone,
L.TimeRequested,
CONVERT(varchar, L.TimeRequested, 101),
DATEPART(hh, L.TimeRequested),
DATEPART(mi, L.TimeRequested)/15
ORDER BY L.TimeRequested
 
 
 

1. SQL returns only 35 records when it should return 50,000?

2. SQL question: Returning most recent order record (must include record ID)

Suppose you have a table named Orders with some fields including: PK,
CustName, OrderDate. PK is an autonumber field, so although a customer may
have multiple records, each record will have a unique value in the PK field.
How would you write the SQL statement to return the most recent order record
for all the customers?



PK CustName OrderDate

1 John Doe 5/5/05

2 Bob Smith 5/7/05

3 John Doe 5/10/05

4 Bob Smith 5/11/05



Using the sample above, records 3 and 4 would be returned:

3 John Doe 5/10/05

4 Bob Smith 5/11/05


Thanks in advance. J. M.

3. Increment a value and return the value using sql

4. SQL Challenge - how to return a record set starting at a particular record?

5. Sql query from Access returns no records, but SQL Query Analyser d

6. Increment Current Record Value on Basis of Last Records Value

7. how to increment record serial no automatically while inserting records.

8. Update record - returns to the first record in the record set

9. SQL insert record return value?

10. SQL query will not return any records

11. SQL 2005 Linked table returns #Deleted in all records/columns

12. SQL Union issue, detail records returned when condition not sa

13. OracleDataReader Returns No Records With Nested SQL

14. Return MAX record in SQL statement HELP

15. Using SQL Query Code in Form Module to Return Record Count